Query Optimization

Advanced

⏱️ 22 mins read

What You'll Learn

Query optimization ensures SQL runs efficiently at scale. Use EXPLAIN/EXPLAIN ANALYZE to see the execution plan. Look for: full table scans (type=ALL), missing indexes, large estimated row counts, and nested loops on big tables. Common fixes: add indexes, rewrite functions in WHERE, avoid SELECT *, use EXISTS instead of IN.

Syntax

EXPLAIN SELECT ...
EXPLAIN ANALYZE SELECT ...
-- MySQL: look at 'type', 'key', 'rows'
-- PostgreSQL: look at Seq Scan vs Index Scan

Example

-- BAD: function on indexed column breaks index
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- GOOD: range condition uses the index
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- BAD: SELECT * is wasteful
SELECT * FROM large_table WHERE status = 'active';

-- GOOD: only needed columns
SELECT id, name, status FROM large_table
WHERE status = 'active';

-- BAD: NOT IN with possible NULLs
SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);

-- GOOD: NOT EXISTS handles NULLs correctly
SELECT * FROM a WHERE NOT EXISTS
  (SELECT 1 FROM b WHERE b.id = a.id);

Common Mistakes

Common performance killers: functions on WHERE columns, OR conditions (use UNION), SELECT *, NOT IN with NULLs, implicit type conversions (WHERE int_col = '5'), missing JOIN indexes, fetching all rows to filter in application code.

Interview Tips

In interviews, always mention EXPLAIN as your first step. Discuss the difference between Seq Scan and Index Scan. Mention query cost, cardinality estimates, and that covering indexes (all queried columns in the index) are fastest.

Practice

Given a slow query: SELECT * FROM orders WHERE YEAR(order_date) = 2024 AND status = 'completed'. Identify at least 3 problems and rewrite it optimally.

Back to Full Roadmap