For most of MySQL's history, plan analysis meant tabular EXPLAIN: a row per table, a type column, a key column, an estimated rows count, and a cryptic Extra field. You learned to pattern-match it, and you also learned it could not tell you what actually happened, only what the optimizer intended. MySQL 8.0.18 changed that with EXPLAIN ANALYZE, which executes the query against real data and annotates every step with measured row counts and timings.
It is the single best query diagnosis tool stock MySQL has ever shipped. It is also routinely misread, because the numbers it prints are per-loop averages hanging off a nested execution model, and the intuitive reading is wrong more often than it is right. I have watched experienced engineers point at the wrong iterator in an EXPLAIN ANALYZE output and spend a day optimizing something that was already fast.
This is a field guide to reading it correctly on MySQL 8.0 and 8.4.
First warning: it actually runs the query
EXPLAIN ANALYZE is not a simulation. MySQL executes the statement to completion, discards the result set, and prints the annotated plan. That has two consequences you must respect in production. A query that takes four minutes takes four minutes under EXPLAIN ANALYZE too, holding whatever locks and consuming whatever resources it normally would. And because instrumentation itself costs time per iterator call, a plan with millions of tiny loop iterations can report noticeably more total time than the bare query takes. Treat the timings as relative weights for finding the expensive step, not as a benchmark of production latency.
Run it on a replica or a staging copy with realistic data when you can. When you must run it on a primary, know what the statement does first with plain EXPLAIN.
The anatomy of one iterator line
The output is a tree of iterators, children indented under parents, executed roughly inside-out. Every line follows the same pattern, and every number in it has a precise meaning.
EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
AND o.created_at >= '2026-07-01';A typical result looks like this (reformatted slightly for width):
-> Nested loop inner join
(cost=2847 rows=812)
(actual time=0.061..142.5 rows=9214 loops=1)
-> Index range scan on o using idx_status_created
(cost=1204 rows=812)
(actual time=0.049..38.2 rows=9214 loops=1)
-> Single-row index lookup on c using PRIMARY (id = o.customer_id)
(cost=0.9 rows=1)
(actual time=0.010..0.011 rows=1 loops=9214)Read the parenthesized groups separately. The cost and rows in the first group are the optimizer's estimates, made before execution. Everything after actual is measured. In actual time=0.061..142.5, the first number is the time to produce the first row and the second is the time to produce the last row, both in milliseconds. rows is how many rows the iterator returned, and loops is how many times the iterator was restarted.
Everything after actual is per loop
This is the misread that causes the most damage. The actual time and rows figures are averages per loop. To get real totals, multiply by loops. In the example above, the primary key lookup on customers shows rows=1 and about 0.011 ms, which looks free. But loops=9214: it ran 9,214 times, returned roughly 9,214 rows in total, and consumed around 100 ms of the 142 ms the join took. An engineer scanning for big numbers will walk straight past it.
The corollary: never compare the raw actual time of two iterators without normalizing by loops. A child inside a nested loop with loops=50000 at 0.02 ms per loop costs a full second; a sibling with loops=1 and 200 ms costs a fifth of that, yet its printed number is four orders of magnitude larger. You find the expensive step by computing time multiplied by loops down the tree, not by eyeballing.
Also remember that a parent's timing includes its children. The nested loop line reporting 142.5 ms is the total for the whole join subtree, not for join bookkeeping alone. To isolate an iterator's own cost, subtract the loop-adjusted time of its children.
Estimated versus actual rows is where the bugs live
The single most useful diff in the output is rows in the cost group (the estimate) versus rows times loops (the measurement). When they are close, the optimizer understood your data, and a slow query is slow because the work is genuinely large. When they diverge by 10x or more, the optimizer chose the plan under false assumptions, and every decision downstream of the misestimate, join order, join method, index choice, is suspect.
In the example, the range scan was estimated at 812 rows and returned 9,214, an 11x miss. That is the thread to pull: stale index statistics, a skewed status column the statistics cannot see, or a range the optimizer priced badly. Which fix applies depends on where the statistics come from, and the answer is usually ANALYZE TABLE, a histogram, or better sampling rather than an index hint. Note also where the misestimate sits: an error at a leaf multiplies as it propagates up through joins, so chase the deepest bad estimate first, not the top-line one.
The misreads I keep seeing
- Reading first-row time as startup overhead. For blocking iterators like sorts, aggregates, and materializations, the first-row time includes essentially all the work: a sort cannot emit its first row until it has consumed every input row. A large gap between the two numbers means something different on a streaming iterator than on a blocking one.
- Ignoring loops on cheap-looking lookups. Covered above; it is the classic.
- Treating the timings as reproducible latency. Buffer pool state dominates. The first run reads from disk, the second from memory, and neither instrumented run matches bare production latency. Run it twice and reason from the warm run.
- Comparing costs to times. The cost figure is in the optimizer's internal currency, not milliseconds. Cost explains why the optimizer chose the plan; actual time reports what the plan did. Different questions.
- Optimizing an iterator that is not on the critical path. Total query time concentrates somewhere. Compute loop-adjusted totals and fix the biggest term first, even if a smaller term looks uglier.
When FORMAT=TREE lies less than tabular EXPLAIN
Plain EXPLAIN FORMAT=TREE, available since 8.0.16, prints the same iterator tree without executing anything. It is worth preferring over tabular EXPLAIN even when you cannot afford to run the query, because the tabular format actively obscures modern execution. Tabular output has one row per table and was designed around the old nested-loop-only executor. Hash joins, added in 8.0.18, appear in tabular output only as a note in the Extra column, while the tree shows an explicit inner hash join iterator with its build and probe sides in the right roles. Materialized subqueries, temporary tables, and the exact point where a filter condition is applied are similarly explicit in the tree and implicit or invisible in the table.
The tabular format is not useless: it is compact, and its possible_keys column has no tree equivalent. But when the question is what will this query actually do, the tree answers it and the table hints at it. If you are coming from the PostgreSQL side, the tree format will feel familiar, though the semantics differ in ways that matter; I went through those differences in MySQL EXPLAIN vs PostgreSQL EXPLAIN ANALYZE.
Make it a workflow, not a party trick
EXPLAIN ANALYZE diagnoses one query at one moment. The queries worth diagnosing should come from somewhere systematic: a slow log digest or statement summary that tells you which family regressed after which deploy. A classic use of the pair is the ORDER BY plus LIMIT trap, where the optimizer flips to an index that avoids a sort and examines a million rows to return twenty; the digest finds the regression, and EXPLAIN ANALYZE makes the bad flip visible in one read. I covered that failure mode in ORDER BY LIMIT regressions in MySQL vs PostgreSQL.
Keep the before and after outputs. An EXPLAIN ANALYZE captured while the query was healthy is the cheapest plan-regression insurance you can buy, because the diff against the sick version usually points at the answer in seconds.
Where MonPG fits
MonPG is a PostgreSQL monitoring platform, and plan-level evidence, keeping query history and execution context so regressions are caught with before and after proof, is exactly the workflow it automates for Postgres today. We are building MySQL monitoring (coming soon) to bring the same discipline to MySQL fleets, including the digest-to-plan investigation path this post walks through manually. MonPG does not monitor MySQL yet; when it does, this guide is the reading skill it will assume. If your team also operates PostgreSQL, the platform is live there and you can start there now.