Reference · Cheat sheet

Reading EXPLAIN

The one-page compressed essence: grade any plan by reading five columns in order.

Lesson: 0003Context: InnoDB, MySQL 8.0+

Read in this order

type → key → rows → filtered → Extra

type = how it reaches rows · key = index chosen · rows×filtered = rows carried forward · Extra = warnings. Grade type first, always.

The type ladder (best → worst)

typeMeaning
system / const≤ 1 matching row, read once. PK/unique = constant.
eq_refOne row per join combo via PK / UNIQUE-NOT-NULL. Best join type.
refRows matching an index value — non-unique key or leftmost prefix.
rangeIndex range scan (> < BETWEEN IN).
indexFull scan of the index tree (often covering; smaller than table).
ALLFull table scan — every row. Usually the thing to fix.

Key columns

possible_keysIndexes MySQL could use.
keyIndex it chose. NULL = a scan.
key_lenBytes of the key used — how many prefix columns engaged.
rowsEstimated rows examined (InnoDB: an estimate).
filtered% surviving the WHERE. rows × filtered = rows joined forward.

Extra — the warnings

Using indexCovering — index alone, no clustered lookup.
Using whereFiltered after the engine returns rows.
Using index conditionIndex Condition Pushdown.
Using filesortSeparate sort — index didn't supply order.
Using temporaryA temp table was built (often GROUP BY / DISTINCT).

Estimates lying? EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT … ← actually RUNS the query -> Index range scan … (cost=1.26 rows=5) ← estimated (actual time=0.012..0.014 rows=5 loops=1) ← measured ▲ estimated vs actual rows diverge → bad plan cause

Also: EXPLAIN FORMAT=JSON for full cost detail; EXPLAIN ANALYZE always uses FORMAT=TREE.

Quick triage

type ALL on a big table? → add/adjust an index (leftmost prefix!) key NULL but index exists? → filter isn't a prefix / column wrapped in a function Using filesort? → make ORDER BY match an index prefix + direction huge rows, tiny filtered? → index the filtering column so it happens earlier estimate ≠ actual? → ANALYZE TABLE to refresh statistics

Source: MySQL 8.0 Manual — EXPLAIN Output Format · EXPLAIN ANALYZE · All lessons