Query Optimization

By the end of this lesson you'll be able to open up any slow query with EXPLAIN , read the plan line by line, understand how the cost-based optimiser decided what to do, and rewrite the query so it uses your indexes instead of scanning whole tables. This is the skill that separates "it works" from "it's fast at scale".

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

The Big Picture

A query is a destination; the optimiser is a satnav. You say where you want to go (the SQL); the optimiser works out how to get there — which roads (scans), which interchanges (joins), in which order. EXPLAIN prints the chosen route before you drive it. EXPLAIN ANALYZE drives it and logs the real travel time at every junction. Your job in this lesson is to read that route sheet and spot where the satnav took the slow road because it had a bad map (stale statistics) or because you wrote the address in a way it couldn't match to a fast road (a non-sargable predicate).

1. EXPLAIN — Print the Plan Before You Run It

The query planner (also called the optimiser) is the part of the database that turns your SQL into a concrete execution plan — an ordered tree of steps. EXPLAIN prints that tree without running the query. EXPLAIN ANALYZE actually executes it and adds the real timings and real row counts next to each step, so you can compare guess against reality.

Read a plan bottom-up and inside-out : the most-indented node runs first, and its output rows flow up into the node above it. The top line is the final result.

2. Seq Scan vs Index Scan

The first thing to find in a plan is how each table is read. A Seq Scan (sequential scan) reads every row in the table and throws away the ones that don't match — fine for tiny tables or when you genuinely want most rows. An Index Scan uses an index to jump straight to the matching rows — far less work when you want only a few.

Line-by-line: Seq Scan on employees (cost=0.00..1850.00 rows=1200 width=64)

• Seq Scan on employees — the access method and the table.
• cost=0.00..1850.00 — estimated cost to get the first row (0.00) and to finish (1850.00).
• rows=1200 — the optimiser's estimate of how many rows survive the filter.
• width=64 — average bytes per returned row.
• Filter: (salary > 70000) — the condition applied to every row read. On an Index Scan this line becomes Index Cond , meaning the index itself did the filtering.

3. Cost, Rows & Width — How the Optimiser Decides

The optimiser builds several candidate plans, assigns each a numeric cost , and keeps the cheapest. Cost is an abstract unit — not milliseconds — derived from a few tunable constants for disk reads and CPU work. The key insight: every estimate ultimately traces back to the row count the optimiser expects, which it gets from table statistics.

4. Estimated Rows vs Actual Rows

This is the single most useful diagnostic in the whole lesson. EXPLAIN ANALYZE prints the estimated rows (from statistics) right beside the actual rows (from running the query). When they're close, the optimiser had a good map and its plan choice was sound. When they're off by 10x or more, the optimiser was flying blind — and almost certainly chose the wrong plan.

• actual time=0.05..6.90 — real milliseconds to first row and to last row for this node.
• rows=48000 (in the actual line) — rows this node really produced.
• loops=1 — how many times the node ran. In a nested loop the inner node runs many times; the printed time and rows are per loop , so multiply by loops for the true total.
• Rows Removed by Filter: 12000 — work the engine did and then threw away. Large numbers here mean a better index or predicate could avoid the wasted reads.

Your Turn: make it sargable

A predicate is sargable (Search-ARGument-able) when the engine can use an index for it. Wrapping the indexed column in a function like YEAR() destroys that. Rewrite the year test as a half-open range on the bare column. The expected plan is in the comments so you can check yourself.

5. Join Methods — Nested Loop, Hash, Merge

When two tables are joined, the optimiser picks one of three physical strategies. Each wins in a different situation, and the plan tells you which one it chose.

Nested Loop: for each row on the outer side, look up matches on the inner side. Best when the outer side is tiny and the inner side is indexed; awful for large-by-large because it's roughly outer × inner work. Hash Join: build a hash table from the smaller input, then stream the larger one through it — the default for big equality joins. Merge Join: sort both inputs on the join key and walk them together like a zip — best when both inputs already arrive sorted (both columns indexed).

6. Query Rewrites That Unlock Indexes

You influence the plan far more by how you write the query than by tweaking the optimiser. These five rewrites remove the most common reasons an index goes unused.

Your Turn: fix the seq scan

The plan below reads all 2,000,000 rows to return just 12. The predicate is already sargable (a bare customer_id ), so the issue isn't the query — there's simply no index to use. Write the one statement that fixes it.

7. Statistics — The Optimiser's Map of Your Data

Every cost estimate ultimately rests on statistics : how many rows a table has, how many distinct values a column holds, which values are most common, and a histogram of the distribution. The database samples these periodically. After a big bulk load, delete, or import, the sample is out of date — stale — and the estimates (remember the 200-vs-48000 miss) go badly wrong, taking the plan with them.

8. Optimiser Hints (Use Sparingly)

Sometimes the optimiser still gets it wrong and you need to override it with a hint . There's no SQL standard for hints, so the syntax differs by engine — and a hint that helps today's data can hurt tomorrow's. Fix indexes, predicates and statistics first; treat hints as a last-resort splint.

Common Errors (and the fix)

• Function on an indexed column kills the index: WHERE YEAR(hire_date) = 2024 or WHERE UPPER(name) = 'ANA' forces a Seq Scan. Rewrite as a range on the bare column, or build a matching expression index on YEAR(hire_date) .
• Leading-wildcard LIKE can't use a B-tree: WHERE name LIKE '%son' scans the whole table because the index is sorted left-to-right. LIKE 'son%' (anchored at the start) can use the index.
• Stale statistics produce wild estimates: if EXPLAIN ANALYZE shows estimated rows far from actual rows, run ANALYZE your_table; before blaming the query.
• SELECT * defeats a covering index: if an index covers (customer_id, total) but you ask for every column, the engine must visit the table anyway. Select only the columns you need so the index alone can answer.
• Reading the plan top-down: plans execute the most-indented node first. Read bottom-up, or you'll misjudge which step is the real bottleneck.

📘 Quick Reference

Term

What it means

Frequently Asked Questions

No. If your query returns most of the table, one sequential pass is cheaper than millions of random index look-ups, so the optimiser correctly picks a Seq Scan. It's only a smell when a large table is scanned to return a few rows.

No — they're abstract units derived from page-read and CPU constants. Use them to compare two plans, not as a stopwatch. For real time, use EXPLAIN ANALYZE and read the actual time values.

For "does any related row exist?", EXISTS can stop at the first match, while IN may materialise the whole subquery. On modern optimisers they're often planned identically, but EXISTS is the safer default for correlated existence checks.

Q: My estimate and actual rows are wildly different. What now?

Run ANALYZE your_table; to refresh statistics, then re-check. If a column is very skewed, raise its statistics target ( SET STATISTICS 1000 ) and analyse again so the histogram captures the distribution.

Q: Do these plans look the same in MySQL or SQL Server?

The concepts (scans, joins, cost, statistics) are universal, but the wording and exact numbers differ. MySQL's EXPLAIN formats results differently and uses comment-style hints; SQL Server shows graphical plans and uses OPTION . Learn the ideas here and the engine-specific syntax maps over easily.

Mini-Challenge: Optimise the Slow Report

Now do it with the support removed — a brief, a blank canvas, and the expected shape in the comments. Make the date test sargable, add one supporting index, and explain how the plan changes. Then paste it into a playground to confirm.

🎉 Lesson Complete

• ✅ EXPLAIN shows the plan; EXPLAIN ANALYZE runs it and adds real times
• ✅ A Seq Scan reads everything; an Index Scan jumps to matching rows
• ✅ Cost, rows and width drive every decision — and all rest on statistics
• ✅ Estimated-vs-actual rows is your fastest stale-stats diagnostic
• ✅ Nested loop, hash and merge joins each win in different situations
• ✅ Sargable predicates and column-only SELECTs unlock your indexes
• ✅ Next: Advanced JOIN Patterns — semi-joins, anti-joins, and lateral joins

Practice quiz

Continue this course

• Previous: Indexing Internals
• Next: Advanced JOIN Patterns