Query Optimization
Write faster, more efficient queries
Slow queries can cripple application performance. These techniques help you write efficient SQL.
Use EXPLAIN
-- See how the database executes your query EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'test@example.com'; -- PostgreSQL EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
Optimization Tips
Select only needed columns
Avoid SELECT * in production. Specify exact columns needed.
Add indexes on WHERE columns
Index columns used in WHERE, JOIN, and ORDER BY.
Use LIMIT during development
Add LIMIT 100 while testing to avoid slow queries.
Avoid functions on indexed columns
WHERE LOWER(name) = 'john' can't use index. Store lowercase.
Anti-Patterns to Avoid
-- BAD: Function on column prevents index use SELECT * FROM users WHERE YEAR(created_at) = 2024; -- GOOD: Compare to range instead SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'; -- BAD: Leading wildcard SELECT * FROM users WHERE name LIKE '%john%'; -- GOOD: Trailing wildcard can use index SELECT * FROM users WHERE name LIKE 'john%';