Reading a Query Plan

EXPLAIN shows the plan the optimizer chose; EXPLAIN ANALYZE runs the query and shows what actually happened.

SyntaxEXPLAIN [FORMAT = {TRADITIONAL | JSON | TREE}] statement | EXPLAIN ANALYZE [FORMAT = TREE] select_statement

EXPLAIN prints the execution plan for a statement without running it. EXPLAIN ANALYZE, added in MySQL 8.0.18, runs the statement and annotates the same plan with measured timings, actual row counts and loop counts, so you can see exactly where the optimizer's estimate was wrong.

The columns that matter

type is the access method and the first thing to read:

type	meaning
`const`	At most one row, found by primary or unique key.
`eq_ref`	One row per driving row, via a unique index.
`ref`	Matching rows from a non-unique index or prefix.
`range`	An index interval: `>`, `BETWEEN`, `IN()`.
`index`	Full scan of the index tree.
`ALL`	Full table scan.

key is the index actually used, possible_keys what was available, and key_len how many bytes of that key were used — which tells you how many key parts survived. rows is an estimate from index statistics and filtered the percentage expected to pass the WHERE; multiply them for the rows handed upward. In Extra, watch for Using index (covering), Using index condition (pushdown), Using filesort and Using temporary.

Formats

Output defaults to TRADITIONAL, controlled by the explain_format system variable. FORMAT=TREE shows execution order and nesting; FORMAT=JSON adds cost numbers. EXPLAIN ANALYZE supports TREE only.

Example

EXPLAIN
SELECT o.id, o.total_cents
FROM orders   AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.country = 'SE'
  AND o.placed_at >= '2026-01-01';

-- id  table  type  possible_keys        key              key_len  ref        rows  filtered  Extra
--  1  c      ref   PRIMARY,idx_country  idx_country      2        const       812    100.00  Using index
--  1  o      ref   idx_cust_placed      idx_cust_placed  8        shop.c.id     6     33.33  Using where
--
-- Read it top down: c is the driving table, scanned by country (type: ref,
-- covered by idx_country). For each of its ~812 rows, o is probed by
-- customer_id (ref). key_len 8 is one BIGINT, so only the first key part of
-- idx_cust_placed was used -- placed_at is being rechecked per row, which is
-- what filtered: 33.33 is telling you.
--
-- Estimated rows examined: 812 * 6 = ~4,900; estimated output: ~1,600.

-- Same plan as an execution tree, with measured numbers.
EXPLAIN ANALYZE
SELECT o.id, o.total_cents
FROM orders   AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.country = 'SE'
  AND o.placed_at >= '2026-01-01';

When to use it

Confirming a new index is actually used before shipping the migration.
Finding which table in a join is driving the row count up.
Explaining a statement that is currently blocking other sessions.
Proving a rewrite helped by comparing measured rows and time, not guesses.

More examples

Reading EXPLAIN ANALYZE output

Each tree line carries the optimizer's guess in (cost=... rows=...) and reality in (actual time=first..total rows=... loops=...). The gap between the two estimates is where your plan went wrong. Times are per loop in milliseconds, so a cheap-looking iterator inside loops=812 is not cheap. Reach for this whenever EXPLAIN looks reasonable but the query is slow anyway.

EXPLAIN ANALYZE
SELECT o.id, o.total_cents
FROM orders   AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE c.country = 'SE' AND o.placed_at >= '2026-01-01';

-- -> Nested loop inner join  (cost=1204 rows=1610)
--      (actual time=0.081..48.3 rows=1584 loops=1)
--     -> Index lookup on c using idx_country (country='SE')
--          (cost=91.2 rows=812) (actual time=0.042..1.19 rows=812 loops=1)
--     -> Filter: (o.placed_at >= TIMESTAMP'2026-01-01 00:00:00')
--          (cost=1.17 rows=1.98) (actual time=0.048..0.057 rows=1.95 loops=812)
--         -> Index lookup on o using idx_cust_placed (customer_id=c.id)
--              (cost=1.17 rows=5.94) (actual time=0.044..0.053 rows=5.9 loops=812)
--
-- The Filter line is the finding: 5.9 rows fetched per loop, 1.95 kept, 812
-- times over. Moving placed_at into the index prefix removes that waste.
-- "never executed" on an iterator means the plan never reached it at all.

-- The tree without running anything:
EXPLAIN FORMAT=TREE
SELECT o.id FROM orders AS o WHERE o.customer_id = 42;

Explaining a query that is already running

When a statement is stuck, you cannot re-run it to explain it — the plan may differ, and the table may be locked. EXPLAIN FOR CONNECTION explains the statement executing right now in another session. Get the connection id from the process list first. This is the standard first move during an incident, before deciding whether to kill the query.

-- Find the offender.
SELECT ID, USER, DB, TIME, STATE, LEFT(INFO, 120) AS statement
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep' AND TIME > 5
ORDER BY TIME DESC;

-- Explain what that connection is doing, without running anything yourself.
EXPLAIN FOR CONNECTION 4471;
EXPLAIN FORMAT=JSON FOR CONNECTION 4471;

-- Then decide.
KILL QUERY 4471;   -- ends the statement, keeps the connection

-- Related: performance_schema keeps normalized statement history, which is
-- where you find the pattern rather than the one instance.
SELECT DIGEST_TEXT, COUNT_STAR, ROUND(AVG_TIMER_WAIT/1e9) AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Capturing a plan into a variable (MySQL 8.4)

MySQL 8.4 added the INTO clause, which stores JSON output in a user variable instead of printing it — useful for scripting a plan regression check, because you can query the JSON rather than diff a text table. FORMAT=JSON must be stated explicitly, and INTO does not combine with FOR CONNECTION or EXPLAIN ANALYZE.

-- Requires an explicit FORMAT = JSON.
EXPLAIN FORMAT=JSON INTO @plan
SELECT id, total_cents FROM orders
WHERE customer_id = 42 AND placed_at >= '2026-01-01';

-- Now the plan is data. Assert on it in a deploy check.
SELECT JSON_EXTRACT(@plan, '$.query_block.table.access_type') AS access_type,
       JSON_EXTRACT(@plan, '$.query_block.table.key')         AS index_used,
       JSON_EXTRACT(@plan, '$.query_block.table.rows_examined_per_scan') AS est_rows;

-- Fail loudly if an index disappeared or a plan regressed to a full scan.
SELECT IF(JSON_UNQUOTE(JSON_EXTRACT(@plan, '$.query_block.table.access_type'))
          = 'ALL', 'REGRESSION: full table scan', 'ok') AS verdict;

-- MySQL 8.4 also adds FOR SCHEMA, to explain a statement in another database
-- without switching connection context.
EXPLAIN FORMAT=TREE FOR SCHEMA reporting
SELECT COUNT(*) FROM orders WHERE status = 'paid';

Discussion

Be the first to comment on this lesson.