In MySQL 8.4, how does EXPLAIN ANALYZE differ from EXPLAIN FORMAT=TREE, and how do you read its actual time, rows and loops figures?
answer
- one predicts, one executes
- iterator tree, innermost runs first
- first row versus all rows
- averaged per loop, in milliseconds
basics
~20 sEXPLAIN FORMAT=TREE prints the optimizer's iterator tree with estimated cost and rows without running anything. EXPLAIN ANALYZE executes the statement and adds actual time, rows and loops for every iterator, with times in milliseconds averaged per loop.
solid answer
~40 s`EXPLAIN FORMAT=TREE` shows the plan as a tree of iterators, each with `(cost=... rows=...)` estimates, and does not execute the query. `EXPLAIN ANALYZE` runs the statement and adds `(actual time=first..last rows=N loops=L)` to each iterator. `first` is the time to the first row and `last` is the time spent executing that iterator including its children, both in milliseconds. With several loops, the time is the average per loop, so an inner iterator with `loops=5000` costs roughly its time multiplied by 5000. `EXPLAIN ANALYZE` only supports the TREE format and fails with `FORMAT=JSON` or `FORMAT=TRADITIONAL`. It works for `SELECT`, multi-table `UPDATE` and `DELETE`, and `TABLE` statements, and because it really runs the statement it takes as long as the query does.
code
sql · 3 linesEXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2);
EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2);go deeper
Recall that EXPLAIN FORMAT=TREE predicts and EXPLAIN ANALYZE executes, and that its times are in milliseconds.
Explain each part of an iterator line, reading the tree inside out, and why the per-loop average must be multiplied by loops to find the expensive iterator.
Show that you weigh the cost of executing the statement, choose where to run it, and use the estimated versus actual rows to decide between an index, statistics or a rewrite.
Set the team's habit: plan evidence from EXPLAIN ANALYZE before and after every query change, gathered off the busiest primary and kept with the change.
## Two statements, two questions MySQL has several ways to show a plan, and two of them use the same **tree format**: - **`EXPLAIN FORMAT=TREE`** asks the optimizer what it *intends* to do. It prints the chosen plan as a tree of **iterators** (the units MySQL's executor runs: table scans, index lookups, filters, joins, sorts, aggregates). Each iterator carries the optimizer's estimates, `(cost=... rows=...)`. Nothing is executed, so it returns quickly even for a query that would take an hour to run. - **`EXPLAIN ANALYZE`** *runs* the statement, instruments every iterator and prints the same tree with measured figures added. It answers "where did the time actually go?" The classic tabular `EXPLAIN`, with its `type`, `key` and `Extra` columns, is a third view of the plan and is covered with MySQL index types. The general skill of comparing estimates with actuals is covered with execution plans. This question is about MySQL's own syntax and output. ## Reading one iterator line A line of `EXPLAIN ANALYZE` output looks like this: `-> Table scan on t2 (cost=0.07 rows=5) (actual time=0.0126..0.0221 rows=5 loops=1)` | Part | Meaning | |---|---| | `cost=0.07` | Estimated cost from the cost model; some iterators are not costed | | first `rows=5` | Estimated rows the iterator will return | | `actual time=0.0126` | Time to return the **first** row, in milliseconds | | `..0.0221` | Time spent executing this iterator, **including its children** but not its parents, in milliseconds | | second `rows=5` | Rows the iterator actually returned | | `loops=1` | How many times the iterator was started | Three habits make the output readable: 1. **Read inside out.** The most deeply indented iterators run first and feed their parents. A join's inputs are indented beneath the join line. 2. **Multiply by loops.** When `loops` is greater than 1, the time is an **average per loop**. An index lookup showing `actual time=0.05..0.08 ... loops=20000` spends about 1.6 seconds in total, which a glance at `0.08` hides. 3. **Compare the two `rows` values.** A large gap between estimated and actual rows on one iterator is where the optimizer was misled, and every iterator above it inherits the error. In MySQL 8.4 the numbers in `EXPLAIN ANALYZE` and `EXPLAIN FORMAT=TREE` are printed with three significant digits below 1000, and values outside 0.001 to 999999.5 switch to engineering notation such as `1.23e+9` or `934e-6`. A time printed as `934e-6` is under a microsecond, not 934 milliseconds. ## Rules and limits of EXPLAIN ANALYZE - **Format:** it always produces TREE output. `FORMAT=TREE` may be written explicitly. `FORMAT=JSON` or `FORMAT=TRADITIONAL` raises error 1235 ("doesn't yet support"). If the session's `explain_format` is `JSON`, a bare `EXPLAIN ANALYZE` also fails unless it says `FORMAT=TREE`. The default `explain_format` is `TRADITIONAL`, which leaves `EXPLAIN ANALYZE` on TREE. - **Statements:** `SELECT`, multi-table `UPDATE` and `DELETE`, and `TABLE` statements. It cannot be combined with `FOR CONNECTION`, so it cannot profile a statement already running in another session. - **Cost:** it executes the statement for real. A ten-minute report takes ten minutes, holds the locks and reads the pages it normally would. A multi-table `UPDATE` or `DELETE` really runs too, so try it inside a transaction you roll back, or on a replica or copy. - **Stopping it:** `KILL QUERY` or Ctrl-C ends it like any other statement. ## Choosing among MySQL's plan views | Statement | Runs the query? | Shows | |---|---|---| | `EXPLAIN` (default `TRADITIONAL`) | No | One row per table: access type, key, rows, filtered, Extra | | `EXPLAIN FORMAT=JSON` | No | The same plan with cost details as a JSON document | | `EXPLAIN FORMAT=TREE` | No | The iterator tree with estimated cost and rows | | `EXPLAIN ANALYZE` | Yes | The iterator tree with estimates plus measured time, rows and loops | | `EXPLAIN FOR CONNECTION id` | No | The plan another session is using right now | `EXPLAIN FOR CONNECTION` is the one to reach for while a slow statement is still running in another session, which `EXPLAIN ANALYZE` cannot do. Because data and statistics change, the plan it reports can differ from what `EXPLAIN` gives for the same text later, which is useful when the slowdown comes and goes. It needs the `PROCESS` privilege to look at another account's connection, and it does not work for prepared statements. ## A working routine When a statement shows up in the slow log or in Performance Schema digests: - run `EXPLAIN FORMAT=TREE` first; it is free and shows whether the plan is the one you expected; - run `EXPLAIN ANALYZE` on a representative statement with real literal values, ideally away from the busiest primary; - find the iterator whose total time (time multiplied by loops) dominates, and check its estimated and actual rows; - decide between an index, a rewrite, fresher statistics or a histogram, then run `EXPLAIN ANALYZE` again to confirm the change.
- Why is running EXPLAIN ANALYZE on a busy MySQL primary a risk?It executes the statement in full, so it takes as long, reads as many pages and holds the same locks as the real query. For a multi-table `UPDATE` or `DELETE` the change really happens, so run it inside a transaction you roll back, or on a replica or a copy of the data.
- A session has explain_format set to JSON. What happens when it runs EXPLAIN ANALYZE?It fails with error 1235, because `EXPLAIN ANALYZE` supports only the TREE format. Adding `FORMAT=TREE` to the statement makes it work whatever `explain_format` says; `FORMAT=JSON` or `FORMAT=TRADITIONAL` always fail.
- EXPLAIN ANALYZE only estimates the plan and never runs the query.
- EXPLAIN ANALYZE FORMAT=JSON returns the measured plan as JSON.
- The actual time figures in EXPLAIN ANALYZE are in seconds.
- With loops=20000, the actual time shown is already the total for all loops.
- EXPLAIN FORMAT=TREE shows the actual row counts of the last run.
Assumes MySQL 8.4, whose EXPLAIN ANALYZE and FORMAT=TREE print three significant digits and switch to engineering notation outside 0.001 to 999999.5.