Decision Guides2026-09-26
SQL Query Optimization Checklist: Fix Slow Queries in the Right Order
Work through slow queries like a professional: read the plan first, fix predicates, then indexes, then rewrites — a numbered checklist that prevents the common mistake of tuning by guesswork.
Quick Answer
- Measure before touching anything: run
EXPLAIN ANALYZEand find the operation that dominates the time — usually a scan or a sort. Optimizing anything else is guesswork. - Check the predicate killers first: functions on indexed columns, implicit type casts, and leading wildcards defeat indexes instantly.
- Then rewrite shape: narrow the columns, paginate instead of OFFSET on deep pages, and replace repeated correlated subqueries with joins or window functions.
The Checklist, In Order
Step 1 — Read the plan
- Run
EXPLAIN ANALYZE(PostgreSQL/MySQL 8) or the estimated plan (older MySQL:EXPLAIN FORMAT=JSON). - Find the node with the highest actual time or row count. That node is your target; everything else is noise.
- Compare estimated rows vs actual rows. A 100x mismatch means stale statistics — run
ANALYZEbefore optimizing anything else.
Step 2 — Fix predicate anti-patterns
| Anti-pattern | Why it kills the index | Rewrite |
|---|---|---|
WHERE YEAR(created_at) = 2026 | Function wraps the column — no index match | created_at >= '2026-01-01' AND created_at < '2027-01-01' |
WHERE email = 123 (string column, numeric literal) | Implicit cast, possible full scan | Pass the literal with the column's type |
WHERE name LIKE '%smith' | Leading wildcard cannot use a B-tree | Reverse the field, or use full-text/trigram indexes |
WHERE CAST(id AS text) = '42' | Cast hides the column | Cast the constant, not the column |
WHERE status != 'archived' | Negation often scans | Partial index on the active rows |
Step 3 — Check the indexes match the query
- Do the filtered and joined columns have indexes, and does the composite order match (equality first, then range)? See the index decision guide.
- Is a covering index possible (query reads only indexed columns)? That removes table lookups entirely.
- On MySQL, watch for the same-column-different-order case:
(a, b)does not serveWHERE b = ?alone.
Step 4 — Reshape the query
- SELECT fewer columns. Wide SELECT * over a network is slow even when the scan is fast.
- Deep OFFSET → keyset pagination.
LIMIT 20 OFFSET 100000reads and discards 100k rows;WHERE id > :last_id ORDER BY id LIMIT 20jumps straight there. - Correlated subquery in SELECT → join or window function. N+1 subqueries run per row; one join runs once.
- EXISTS instead of COUNT when you only need to know a row exists — the engine can stop early.
- UNION ALL instead of UNION when duplicates are impossible — UNION pays for a dedup sort.
Step 5 — Verify and lock in the win
- Re-run
EXPLAIN ANALYZE: confirm the slow node is gone, not just moved. - Test with production-sized data. A plan that wins on 10k rows can lose on 10M (or vice versa).
- Record the before/after timing — if you cannot state the speedup in numbers, it is not done.
When the Query Is Fine and the Schema Is Not
Some slowness cannot be fixed at the query level: tables that should be partitioned by date, OLTP and analytics workloads fighting on one instance, or a join key stored as text that should be a bigint. If the plan shows the right indexes being used and the time is still high, the fix is structural — measure with the checklist above, then escalate to schema changes with evidence in hand.
Try It Yourself
Our free optimizer runs this checklist automatically on any query you paste — predicate issues, index suggestions, and rewrite hints, in plain language.