“SELECT *” is Killing You: Write Optimized SQL Queries

When working with SQL, it’s tempting to take shortcuts—perhaps the most common being SELECT *. It’s quick, convenient, and works during development. But in production? It can be a silent killer of query performance, especially on large datasets.

This article will explore why SELECT * is bad for performance, and how to optimize SQL queries for maintainability and speed by selecting only what’s necessary—even highlighting profiling examples and real-world use cases.

1. Why `SELECT *` is Problematic

SELECT * fetches all columns from a table, which often means retrieving far more data than you actually need. This leads to:

Increased I/O costs: Accessing unnecessary columns increases disk reads and memory utilization.
Network latency: More data sent over the wire means slower responses.
Column ambiguity: Schema changes (e.g., adding columns) may break dependent applications when assumptions are made about column order.

Imagine this query on a large customer table:

SELECT * FROM customers WHERE country = 'USA';

Even if you only need first_name and email, you’re retrieving every column—possibly dozens, including large text or JSON fields. Multiply that by thousands of rows, and it’s easy to see how things spin out of control performance-wise.

2. Writing Targeted SELECT Queries

Always specify only the columns you need. Here’s the optimal way:

SELECT first_name, email FROM customers WHERE country = 'USA';

This approach has immediate benefits:

Queries become faster—especially when scanning large tables.
You use less memory and network bandwidth.
It improves code readability and makes explicit what data is being used.

Best practice: Always select the columns you actually use in your application logic. Tools like ORMs may encourage SELECT * — override this behavior where possible.

3. Using EXPLAIN to Profile Your Queries

SQL databases like PostgreSQL and MySQL provide an EXPLAIN command that shows how queries are executed.

Consider the following PostgreSQL example:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

This might yield a result with a sequential scan or a less efficient plan. But if you write:

EXPLAIN ANALYZE SELECT id, order_date FROM orders WHERE customer_id = 42;

You’ll often see a reduction in total cost and execution time, especially if you’re using indexes efficiently.

Tip: Pair EXPLAIN ANALYZE with query plans in SQL client tools (like pgAdmin or MySQL Workbench) to visualize bottlenecks.

4. Real-World Optimization Case: Analytics Dashboard

In one production scenario, a team built a reporting dashboard pulling data from a table with 100M+ rows:

SELECT * FROM user_events WHERE event_time > NOW() - INTERVAL '1 day';

Performance suffered. The fix was a combination of selecting only needed fields and proper indexing:

SELECT user_id, event_type, event_time FROM user_events
WHERE event_time > NOW() - INTERVAL '1 day';

Result: Execution time dropped from 8 seconds to under 1 second. The reduced column count allowed better index usage and reduced transfer size over the network.

Bonus tip: Create a covering index on the selected columns if queries like this are frequent.

5. Automation and Tools to Help You Audit Query Quality

In CI/CD pipelines or local development, you can enforce or audit query practices using tools like:

SQLFluff: A linter for SQL syntax and standards, allows detecting the use of SELECT *.
pg_stat_statements: Postgres extension to profile all queries and spot expensive ones.
Query plans in ORMs: Most ORMs let you output raw SQL—make a habit of inspecting them.

Moreover, write shared utility functions or ORM-level mappers that enforce explicit field selection—encouraging team-wide good habits.

Conclusion

SELECT * may seem harmless but it’s almost always a red flag in production. With just a little more effort, you can write cleaner, faster queries that scale. By profiling your performance, narrowing down your selections, and integrating query linting into your workflow—you’ll notice measurable gains in SQL efficiency.

Start small: find your top 5 slow SQL statements today, and make them leaner one column at a time.

Useful links:

1. Why SELECT * is Problematic