Lesson 0003 · Internals & Performance

Reading EXPLAIN

The optimizer already wrote down its plan for every query — this lesson teaches you to read it in one fixed pass and know instantly whether it's good.

~10 minRetrieval quiz + hands-on EXPLAINCheat sheet: here

In 0001 and 0002 you predicted how MySQL would use an index. EXPLAIN is how you check — for any query, on any table. It prints the optimizer's chosen plan without running the statement. The trick is to stop reading it left-to-right and instead read five columns in a fixed order of importance.

The one idea: read it in this order

1 · type — the access-method ladder

This is the single most important value. It tells you how MySQL reaches rows, and it has a strict best-to-worst order. Learn the ladder and you can grade a plan at a glance. — MySQL Manual — EXPLAIN Join Types

Rule of thumb: const/eq_ref/ref are usually fine; range depends on how wide; index and especially ALL on a big table are red flags unless the row count is tiny.

2 · key — which index actually won

possible_keys lists indexes MySQL could use; key is the one it chose (NULL = none, i.e. a scan). A common bug signal: possible_keys names your index but key is NULL — the optimizer decided a scan was cheaper, often because the filter isn't a leftmost prefix or the column is wrapped in a function. — MySQL Manual: "the key column indicates the key (index) that MySQL actually decided to use."

3 · rows × filtered — the work estimate

rows is how many rows MySQL estimates it must examine (for InnoDB, an estimate, not exact). filtered is the percentage expected to survive the WHERE. Their product is the rows carried into the next join step. — MySQL Manual: "rows × filtered shows the number of rows that are joined with the following table."

4 · Extra — where the warnings live

Small text, big meaning. The four you'll see constantly:

Estimates vs reality: EXPLAIN ANALYZE

Plain EXPLAIN shows estimates. When the estimate lies (stale statistics, skewed data), run EXPLAIN ANALYZE — it actually executes the query and prints actual time and rows next to the estimates, in a tree of iterators. When estimated rows and actual rows diverge wildly, you've found why the optimizer chose badly. — MySQL Manual: "EXPLAIN ANALYZE … runs a statement and produces EXPLAIN output along with timing and … information about how the optimizer's expectations matched the actual execution."

Check yourself

Answer from memory — effortful recall is what builds retention. Feedback is immediate.

Hands-on: grade some plans

Reuse the people table from Lesson 0002 (index name (last_name, first_name), PK id). Read the five columns each time.

-- A) PK equality → expect type: const
EXPLAIN SELECT * FROM people WHERE id = 1;

-- B) prefix equality → expect type: ref, key: name
EXPLAIN SELECT * FROM people WHERE last_name = 'Jones';

-- C) covering → expect Extra: Using index
EXPLAIN SELECT last_name, first_name FROM people WHERE last_name = 'Jones';

-- D) non-prefix filter → expect type: ALL, key: NULL
EXPLAIN SELECT * FROM people WHERE first_name = 'John';

-- E) estimates vs reality — actual rows beside estimated
EXPLAIN ANALYZE SELECT * FROM people WHERE last_name = 'Jones';