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".

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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).

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.

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)

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.

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.

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.

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).

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.

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.

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.

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.

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.

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.

Practice quiz

What does plain EXPLAIN do?

  • Runs the query and shows real timings
  • Rebuilds the table's statistics
  • Prints the chosen execution plan without running the query
  • Creates an index automatically

Answer: Prints the chosen execution plan without running the query. EXPLAIN prints the plan the optimiser chose without running the query. EXPLAIN ANALYZE actually runs it and adds real timings.

How should you read a query plan?

  • Bottom-up and inside-out — the most-indented node runs first
  • Top-down, left to right
  • Alphabetically by node name
  • In random order

Answer: Bottom-up and inside-out — the most-indented node runs first. Read a plan bottom-up and inside-out: the most-indented node runs first and its rows flow up into the node above it.

When is a Seq Scan a red flag?

  • When it returns 90% of the table
  • On a tiny table
  • Whenever it appears, always
  • When a large table is scanned to return only a few rows

Answer: When a large table is scanned to return only a few rows. A Seq Scan isn't always bad — scanning is cheaper when you want most of the table. The warning sign is a Seq Scan on a large table returning only a handful of rows.

What makes a predicate 'sargable'?

  • It wraps the indexed column in a function
  • The engine can use an index for it — no function on the bare indexed column
  • It always uses a Seq Scan
  • It returns exactly one row

Answer: The engine can use an index for it — no function on the bare indexed column. A sargable predicate lets the engine use an index. Wrapping the column in a function like YEAR(hire_date) destroys that; use a range on the bare column instead.

Why does WHERE YEAR(hire_date) = 2024 prevent index use?

  • The index stores raw dates, not years, so the function hides the indexed column
  • YEAR is not a valid function
  • Indexes can never be used with dates
  • It returns too many rows

Answer: The index stores raw dates, not years, so the function hides the indexed column. The index stores raw hire_date values, not their year. Rewrite as a half-open range (hire_date >= '2024-01-01' AND hire_date < '2025-01-01') so the index can be used.

Which join method builds a hash table from the smaller input and streams the larger through it?

  • Nested Loop
  • Merge Join
  • Hash Join
  • Seq Scan

Answer: Hash Join. A Hash Join builds an in-memory hash table from the smaller input, then streams the larger input through it — the default for big equality joins.

When is a Nested Loop join the right choice?

  • Large table joined to large table
  • When the outer side is tiny and the inner side is indexed
  • When both inputs are already sorted
  • When neither side has an index

Answer: When the outer side is tiny and the inner side is indexed. A Nested Loop looks up matches in the inner table for each outer row — great when the outer side is tiny and the inner side is indexed, but terrible for large-by-large.

Are the cost numbers in an EXPLAIN plan measured in milliseconds?

  • Yes, they are exact milliseconds
  • Yes, but only for Seq Scans
  • No, they are byte counts
  • No — they are abstract cost units used to compare plans, not a stopwatch

Answer: No — they are abstract cost units used to compare plans, not a stopwatch. Cost is an abstract unit derived from page-read and CPU constants, used to compare plans. For real time, read the actual time values from EXPLAIN ANALYZE.

What should you do when EXPLAIN ANALYZE shows estimated rows far from actual rows?

  • Add more columns to the SELECT
  • Run ANALYZE on the table to refresh statistics
  • Drop all indexes
  • Increase the cost constants

Answer: Run ANALYZE on the table to refresh statistics. A big estimate-vs-actual mismatch is the classic symptom of stale statistics. Run ANALYZE your_table; to refresh them before blaming the query.

Why does a leading-wildcard LIKE such as WHERE name LIKE '%son' scan the whole table?

  • Because LIKE never uses indexes
  • Because '%son' is invalid syntax
  • Because a B-tree index is sorted left-to-right, so a leading wildcard can't use it
  • Because it returns no rows

Answer: Because a B-tree index is sorted left-to-right, so a leading wildcard can't use it. A B-tree index is sorted left to right, so a leading wildcard defeats it. An anchored pattern like LIKE 'son%' can use the index.