Skip to content

CONCEPT Cited by 2 sources

MySQL EXPLAIN ANALYZE

EXPLAIN ANALYZE is the MySQL 8.0.18+ extension that runs the query and reports the actual per-iterator runtime cost alongside the optimiser's estimates. Plain EXPLAIN tells you what MySQL plans to do; EXPLAIN ANALYZE tells you what MySQL actually did — revealing mis-estimated row counts, expensive iterators hidden inside a cheap-looking plan, and where time was actually spent.

EXPLAIN ANALYZE also prints the actual costs of individual iterators in the execution plan.

— sources/2026-04-21-planetscale-how-to-read-mysql-explains

The warning

EXPLAIN ANALYZE actually executes the query. This makes it unsafe on production for:

  • Expensive queries — you pay the full cost plus profiling overhead.
  • UPDATE / DELETE / INSERT — the side effects happen. The post warns explicitly: "EXPLAIN ANALYZE actually runs the query, so if you don't want to run the query against your live database, do not use EXPLAIN ANALYZE."

Canonical usage: against a non-production replica or a read-only analytical replica where you can afford the cost.

What it reports per iterator

For each iterator (node) in the execution tree, EXPLAIN ANALYZE prints both the planner's estimates (the yellow half of the post's worked screenshot) and the actuals (the green half):

Metric What it measures
Estimated execution cost Planner's cost estimate (some iterators aren't in the cost model → omitted)
Estimated rows returned Planner's row-count estimate for this iterator
Time to first row Actual ms until this iterator emitted its first row
Time executing this iterator Actual ms spent in this node (includes children, excludes parents). For looped iterators, average per loop
Actual rows returned Real row count emitted
Loops How many times this iterator was executed (relevant for nested-loop joins)

Estimate-vs-actual divergence is the diagnostic signal: a planner expecting 10 rows from an iterator that actually returned 10,000,000 is why the query is slow — cardinality estimates are off, so the chosen plan was wrong.

Supported statements

EXPLAIN ANALYZE works with:

  • SELECT
  • Multi-table UPDATE
  • DELETE
  • TABLE (MySQL 8.0.19+ row-producing table statement)

It does not work with stored procedures.

Output format constraints

EXPLAIN ANALYZE automatically selects FORMAT=TREE — the ASCII iterator-tree output that makes parent-child flow visible. Raw JSON format (which plain EXPLAIN supports) is not supported for EXPLAIN ANALYZE as of the time of writing. The tree format organises output as a series of nodes:

-> Higher-level iterator (e.g. GROUP BY, filter)
   -> Lower-level iterator (e.g. index lookup, table scan)

EXPLAIN output is organized into a series of nodes. At the lowest level, the nodes scan the tables or search indexes. Higher-level nodes take the operations from the lower-level nodes and operate on them.

— sources/2026-04-21-planetscale-how-to-read-mysql-explains

EXPLAIN vs EXPLAIN ANALYZE

Aspect EXPLAIN EXPLAIN ANALYZE
Runs the query? No Yes — with side effects
Cost figures Estimates only Estimates + actual timings
Row counts Estimates only Estimates + actual counts
Format Tabular / JSON / tree Tree only
Safe on production? Yes No for expensive or mutating queries
Minimum MySQL version Always 8.0.18+
Diagnosing bad row estimates Hints via rows × filtered Direct — shows actual vs estimate

Use EXPLAIN first (free, safe); escalate to EXPLAIN ANALYZE on a replica when the plain plan looks reasonable but the query is still slow — the divergence between estimate and actual reveals why.

Seen in

Last updated · 378 distilled / 1,213 read