Chapter 09 · Optimizer, EXPLAIN, Statistics, and Query Plan Engineering
EXPLAIN, EXPLAIN ANALYZE, FORMAT=TREE/JSON, and Iterator Timing
Read MySQL execution-plan evidence fluently: distinguish estimates from actual execution, use traditional, TREE, and JSON representations appropriately, and interpret iterator timing without turning plan text into a brittle application API.
Learning outcomes
“Run EXPLAIN” is incomplete advice. MySQL exposes several plan representations, and each answers a different diagnostic question. More importantly, ordinary EXPLAIN describes what the optimizer expects; EXPLAIN ANALYZE executes a supported statement and reports what iterators actually did. This lesson makes that distinction routine.
Distinguish traditional/tabular, TREE, and JSON EXPLAIN representations and choose one for the diagnostic task.
Interpret access type, key choice, estimated rows, filter information, iterator nesting, actual rows, loops, and first/last-row timing.
Explain why EXPLAIN ANALYZE is operationally different from EXPLAIN and why it must be used cautiously on expensive or changing workloads.
Compare a bad estimate with actual iterator evidence and trace which parent iterator multiplies the error.
Avoid building production software that depends on human-oriented plan text remaining byte-for-byte stable across releases.
One query, three views of the estimated plan
Use the ServiceHub lab from Lesson 1. Start with a read-only query that joins work orders to customers. The result is the same regardless of EXPLAIN format; only the diagnostic representation changes.
USE servicehub_plan_lab;EXPLAINSELECT c.segment,COUNT(*) AS nFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'GROUP BY c.segment;EXPLAIN FORMAT=TREESELECT c.segment,COUNT(*) AS nFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'GROUP BY c.segment;EXPLAIN FORMAT=JSONSELECT c.segment,COUNT(*) AS nFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'GROUP BY c.segment;| Representation | Strength | Caution |
|---|---|---|
| Traditional/tabular | Compact table-level view; familiar columns such as type, key, rows, filtered, Extra | Can hide iterator structure and modern operators compared with TREE |
| TREE | Shows iterator hierarchy in execution-flow form; especially useful for hash joins and ANALYZE | Human-readable text can evolve; do not make it a brittle parser contract |
| JSON | Structured estimated-plan detail useful for deeper inspection/tooling | Verbose; still estimates unless paired with separate runtime measurement |
| EXPLAIN ANALYZE | Runs the statement and adds actual iterator timing, rows, and loops | It executes work; use only when the workload is safe to execute |
Read a TREE plan from the inside out
A TREE plan is an iterator tree. Child iterators produce rows for their parent. A lookup iterator may run once for every row produced by an outer iterator; that is why the loops number matters. A small estimate error low in the tree can become a large work multiplier higher up.
EXPLAIN ANALYZESELECT c.segment,COUNT(*) AS nFROM work_orders AS wJOIN customers AS c ON c.customer_id=w.customer_idWHERE w.tenant_id=17 AND w.status='open'GROUP BY c.segment;Look for fragments shaped like (cost=... rows=...) followed by (actual time=first..last rows=... loops=...). The values on your machine need not match a screenshot. The engineering question is whether estimated row flow resembles actual row flow and where most iterator time/loops accumulate.
For an iterator, EXPLAIN ANALYZE reports time to first row and time to completion, actual rows, and loop count. With multiple loops, reported iterator timing is averaged per loop. Parent timing includes work performed by child iterators, so do not naïvely add every displayed time as if the tree were a flat list.
Estimated rows versus actual rows
Temporarily examine a deliberately skewed predicate. channel='monitor' represents only a small fraction of the lab rows and has no index or histogram initially.
-- In the normal chapter sequence no channel histogram exists yet.SELECT SCHEMA_NAME,TABLE_NAME,COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMN_STATISTICSWHERE SCHEMA_NAME='servicehub_plan_lab' AND TABLE_NAME='work_orders' AND COLUMN_NAME='channel';EXPLAIN FORMAT=TREESELECT COUNT(*)FROM work_ordersWHERE channel='monitor';EXPLAIN ANALYZESELECT COUNT(*)FROM work_ordersWHERE channel='monitor';SELECT channel,COUNT(*) AS exact_rowsFROM work_orders GROUP BY channel ORDER BY exact_rows DESC;The access method may remain a table scan because no usable index exists, but the filter’s estimated rows can differ materially from actual rows. That distinction matters: a plan can be structurally reasonable yet based on a weak selectivity estimate. Lesson 3 will improve the optimizer’s information with a histogram and then test whether the estimate—or the whole plan—changes.
EXPLAIN ANALYZE executes the statement
Ordinary EXPLAIN can describe several statement types without running their data-changing effect. EXPLAIN ANALYZE, by contrast, executes supported statements to obtain runtime iterator evidence. In this course, the mandatory ANALYZE labs use read-only SELECT statements.
Do not paste EXPLAIN ANALYZE in front of an expensive production query merely because “it is only explain.” It can consume CPU, memory, I/O, locks, and wall time just like executing the query. MySQL lets you interrupt it with KILL QUERY or the client interrupt, but prevention is better than emergency cancellation.
Before analyzing a heavy production statement, prefer a representative staging copy, a safe read-only reproduction, a bounded predicate, or ordinary EXPLAIN. Capture the server version and dataset conditions so the evidence can be reproduced.
A tempting but ineffective change: optimize the display, not the plan
Changing from traditional to JSON output does not improve query execution. Neither does staring at key_len until it “looks right.” Plan formats are observation tools. Real tuning changes predicates, data access paths, statistics, schema, or workload design.
EXPLAIN FORMAT=TREE SELECT work_order_id FROM work_ordersWHERE tenant_id=17 AND status='open' AND opened_at >= '2026-05-01';EXPLAIN FORMAT=JSON SELECT work_order_id FROM work_ordersWHERE tenant_id=17 AND status='open' AND opened_at >= '2026-05-01';If the query is slow, compare the chosen key, estimated rows, actual rows, loops, sorting/materialization operators, and the amount of data produced—not the visual verbosity of the format.
Plan output is diagnostic, not a permanent text protocol
MySQL’s plan output is designed for diagnosis and evolves as optimizer capabilities evolve. Even when JSON is more structured, applications should not make business correctness depend on one exact plan string or undocumented field layout. If you build observability automation, version it, tolerate change, and test it against the server versions you operate.
SELECT VERSION() AS server_version;SHOW CREATE TABLE work_orders;SHOW INDEX FROM work_orders;SHOW VARIABLES LIKE 'optimizer_switch';EXPLAIN FORMAT=TREESELECT work_order_id,opened_atFROM work_ordersWHERE tenant_id=17 AND status='open' AND opened_at >= '2026-05-01'ORDER BY opened_at DESC LIMIT 40;Knowledge check
- What is the core difference between EXPLAIN and EXPLAIN ANALYZE?
- Which output format does EXPLAIN ANALYZE use in MySQL 8.4?
- Why are loops important?
- Can a table scan be reasonable even when an estimate is wrong?
- Why avoid parsing TREE text as a permanent application contract?
Reveal answers
- EXPLAIN shows optimizer estimates for the plan; EXPLAIN ANALYZE executes the supported statement and reports actual iterator timing, rows, and loops alongside estimates.
- TREE. FORMAT=TREE may be specified explicitly; TRADITIONAL and JSON are not supported for EXPLAIN ANALYZE.
- An inner iterator may execute once per outer row; multiplying modest per-loop work by many loops can dominate runtime.
- Yes. With no useful index, scanning may still be the only sensible access path; improving estimates does not automatically create a new access path.
- It is human-oriented diagnostic output and can evolve across versions as optimizer operators and formatting change.
Summary and next step
You can now separate optimizer intent from runtime evidence. Lesson 3 turns estimate gaps into a statistics investigation: persistent InnoDB statistics, histograms, skew, and ANALYZE TABLE.