MySQL / Reliable applications and analytical queries / Lesson 9

Reading EXPLAIN without guessing

Separate estimated access paths, measured execution, and logical query results.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Read access paths, estimated rows, and join order.
Use EXPLAIN ANALYZE only with execution awareness and suitable authorization.

Start from a correct question

Use the unchanged fixture, with the optional customer/time index from the previous lesson if present. First verify query semantics and result size. An efficient plan for an incorrect report is still incorrect. Record schema, parameter values, data distribution, server version, and the observed latency before changing indexes. Three training orders cannot demonstrate production speedups; they demonstrate how to read evidence.

EXPLAIN describes a candidate plan

EXPLAIN FORMAT=TREE shows the chosen operator tree without normally running the SELECT. Traditional output exposes fields such as type, possible_keys, key, rows, and filtered. possible_keys lists candidates, while key names the selected path. rows and filtered are estimates, not measured returned-row counts. A scan on a tiny table can be the rational choice even when an apparently relevant index exists.

sql Inspect a plan without inventing its output

EXPLAIN FORMAT=TREE
SELECT id,placed_at FROM orders
WHERE customer_id=1 AND placed_at>='2026-01-01'
ORDER BY placed_at,id;

Look for a scan or indexed lookup, filtering, and any explicit sort. The exact operator choice depends on statistics and indexes, so no fixed plan or timing is asserted here. The SELECT itself returns ids 101 and 102 regardless of which correct plan is chosen.

ANALYZE really executes

EXPLAIN ANALYZE runs the statement and reports iterator timings, actual rows, and loops using TREE output in MySQL 8.4. It therefore consumes resources and may wait on locks. Use a read-only SELECT in an approved disposable or diagnostic environment, with an execution budget. Multiply work per loop by loop counts when investigating repeated nested-loop work; do not compare a single child timing to total query latency blindly.

sql Measure a small read-only statement in a lab

EXPLAIN ANALYZE
SELECT c.id,COUNT(o.id) AS n
FROM customers c LEFT JOIN orders o ON o.customer_id=c.id
GROUP BY c.id ORDER BY c.id;

Actual timing values cannot be predicted from the fixture and are intentionally omitted. The underlying result has counts 2, 1, and 0 for customers 1, 2, and 3. Compare estimated and actual cardinalities at each node; a large mismatch guides investigation of statistics or skew rather than proving a missing index.

Interpret expensive operators in context

Using filesort means MySQL performs an ordering operation; the name does not prove that disk files were used. Temporary work can be entirely in memory or spill depending on limits and data. A covering index may reduce row lookups but increase maintenance costs. Inspect predicates for functions or implicit conversions on indexed columns that prevent a useful range lookup, then rewrite without changing semantics.

Change one hypothesis at a time

Compare representative parameter sets, including common and rare customers. Statistics changes, caching, concurrency, and server load can all alter measurements. Record both read improvements and write overhead after an index proposal. Avoid forcing indexes merely to make EXPLAIN look attractive: a forced plan can age badly as data changes. Keep a reversible migration and observe the workload after deployment rather than declaring success from one warm-cache run.

Common errors and fixes

Estimated rows are reported as exact results.

Why it happens: The rows field in ordinary EXPLAIN was mistaken for a measured count.
How to fix it: Compare with safe actual execution evidence and inspect statistics.
Prevent it next time: Label every plan quantity as estimated or observed.

EXPLAIN ANALYZE unexpectedly consumes resources.

Why it happens: It was treated as a purely descriptive command rather than execution.
How to fix it: Stop the diagnostic query if needed and use ordinary EXPLAIN first.
Prevent it next time: Review statement effects and execution budget before ANALYZE.

An index is declared broken because a scan is chosen.

Why it happens: Tiny tables or low-selectivity predicates make scanning cheaper.
How to fix it: Evaluate representative data and total cost before changing schema.
Prevent it next time: Judge latency and resource use, not the presence of an index name.

Check your understanding

1. Why should the three-row orders table not be used to claim a tenfold speedup?

Show worked solution

Its tiny size hides production I/O, skew, cache behavior, and concurrency. Timer noise can exceed the measured difference. Use it to verify results and plan interpretation, then benchmark representative synthetic scale with repeatable conditions before publishing a performance conclusion.

2. Rewrite a January predicate that wraps placed_at in DATE_FORMAT.