Query Optimization Fundamentals
Slow database queries are often the biggest performance bottleneck in web applications. A single poorly-written query can bring your entire system to a crawl. Understanding how databases execute queries helps you write efficient ones.
How Queries Execute
When you send a query to a database, it goes through several stages:
The parser checks your SQL syntax and converts it to an internal representation. The planner considers different ways to execute your query. The optimizer chooses the most efficient execution plan based on table statistics and available indexes. Finally, the executor runs the chosen plan and returns results.
The optimizer's job is crucial — the same query can be executed many different ways, with vastly different performance characteristics.
Using EXPLAIN
The EXPLAIN command reveals how your database plans to execute a query:
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 123 AND status = 'pending';
The output shows the execution plan. Key things to look for:
Seq Scan (sequential scan) means the database reads every row in the table. For large tables, this is usually bad — it indicates a missing index.
Index Scan means the database uses an index to find matching rows quickly. This is usually what you want.
Rows shows estimated versus actual row counts. Large discrepancies suggest outdated statistics — run ANALYZE to update them.
Execution time tells you how long the query actually took. Compare this before and after optimizations.
Common Optimizations
Add missing indexes for columns in WHERE clauses, JOIN conditions, and ORDER BY. Without indexes, the database must scan entire tables.
**Avoid SELECT *** — fetch only the columns you need. Reading unnecessary data wastes I/O and memory.
-- Instead of this
SELECT * FROM users WHERE id = 123;
-- Do this
SELECT id, name, email FROM users WHERE id = 123;
Limit result sets when you don't need everything. Pagination prevents loading millions of rows when users only see twenty.
Avoid functions on indexed columns. This query can't use an index on created_at:
-- Bad: function prevents index use
WHERE YEAR(created_at) = 2024
-- Good: range query uses index
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
Watch for N+1 queries — fetching related data one row at a time instead of in batches. Use JOINs or batch loading instead.
Query optimization is iterative. Measure, identify the slowest queries, optimize them, and repeat.