MySQL – EXPLAIN and Reading a Query Plan

EXPLAIN asks the optimizer what it intends to do with a query, before it does it. It is the difference between guessing why something is slow and knowing. Every plan below was taken from pizza_lab — the pizza schema at 400,000 orders and 1,000,000 line items.

Reading a plan

EXPLAIN SELECT COUNT(*) FROM customer_order WHERE status = 'PAID';

+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys             | key                       | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | customer_order | NULL       | ref  | idx_customer_order_status | idx_customer_order_status | 122     | const | 14398 |   100.00 | Using index |
+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+-------------+

One row per table the query touches. The columns that repay attention:

The type values, best to worst

ALL is not automatically a bug: reading a small table completely is often the cheapest plan, and the optimizer knows it. ALL on a large table with a selective WHERE is the thing to chase.

The Extra column

EXPLAIN SELECT id, customer_name FROM customer_order WHERE status = 'PAID' ORDER BY customer_name LIMIT 10;

+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+----------------+
| id | select_type | table          | partitions | type | possible_keys             | key                       | key_len | ref   | rows  | filtered | Extra          |
+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+----------------+
|  1 | SIMPLE      | customer_order | NULL       | ref  | idx_customer_order_status | idx_customer_order_status | 122     | const | 14398 |   100.00 | Using filesort |
+----+-------------+----------------+------------+------+---------------------------+---------------------------+---------+-------+-------+----------+----------------+

The index found the 14,398 paid orders, then all of them were sorted to return ten. An index on (status, customer_name) would provide the order directly and remove the sort — see indexes.

⚠️ `rows` is an estimate, and it moves

This deserves its own heading, because plans are usually presented as though the numbers were measurements. They are not. The optimizer derives rows from sampled index statistics, and re-running the identical query gives a different figure:

run 1:  rows = 395261
run 2:  rows = 394155      -- same query, same data, seconds apart

Those are two runs of one EXPLAIN against an unchanged table. The estimate is also frequently wrong in a more interesting way: the plan above estimates 14,398 rows for status = 'PAID' where the true count is 8,000 — off by 80%.

None of that makes EXPLAIN less useful. It means:

• Read rows as an order of magnitude, not a number. Ten versus ten million is the signal; 14,398 versus 8,000 is noise.
• A badly wrong estimate is itself a finding — it is how the optimizer picks a bad plan, and ANALYZE TABLE is the first thing to try.
• When you need real numbers, use EXPLAIN ANALYZE.

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT COUNT(*) FROM customer_order WHERE status = 'PAID';

It runs the query — which is the point, and the caveat. The output pairs each step's estimate with what actually happened: (cost=1650 rows=14398) (actual time=0.05..8.2 rows=8000 loops=1). Estimated 14,398, actually 8,000.

Because it executes, do not point it at an UPDATE or DELETE on anything you care about, and expect it to take as long as the query does. Available from MySQL 8.0.18.

FORMAT=TREE

EXPLAIN FORMAT=TREE SELECT COUNT(*) FROM customer_order WHERE status = 'PAID'\G

*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)  (cost=3089 rows=1)
    -> Covering index lookup on customer_order using idx_customer_order_status (status='PAID')  (cost=1650 rows=14398)

End the statement with \G rather than a semicolon. Tree output is one long multi-line string, and in the client's default table mode it is drawn inside a box that wraps badly; \G prints it vertically and readably.

The same plan as a nested tree, read inside-out: the index lookup feeds the aggregate. For anything with several joins this is far easier to follow than the table, because it shows the order of operations rather than one row per table. FORMAT=JSON gives the fullest detail, including costs, and is what tooling parses.

A join plan

EXPLAIN SELECT o.id, i.product_name
FROM   customer_order o
JOIN   order_item i ON i.order_id = o.id
WHERE  o.status = 'PAID';

+----+-------------+-------+------------+------+-----------------------------------+---------------------------+---------+----------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                     | key                       | key_len | ref            | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------------------+---------------------------+---------+----------------+-------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | ref  | PRIMARY,idx_customer_order_status | idx_customer_order_status | 122     | const          | 14398 |   100.00 | Using index |
|  1 | SIMPLE      | i     | NULL       | ref  | idx_order_item_order              | idx_order_item_order      | 8       | pizza_lab.o.id |     2 |   100.00 | NULL        |
+----+-------------+-------+------------+------+-----------------------------------+---------------------------+---------+----------------+-------+----------+-------------+

Three things to read here. Rows are listed in join order — o is driven first, then i for each of its rows. The ref column names the source: pizza_lab.o.id means i is looked up by the id from o. And the row counts multiply: roughly 14,398 × 2 line-item lookups. That product, not either number alone, is the query's cost.

The most common join problem is the driving table being wrong, or the second table showing ALL — which means the join column is unindexed and MySQL is scanning the whole table once per outer row.

How to use it

1. Find the slow query — the slow query log or SHOW FULL PROCESSLIST.
2. EXPLAIN it. Look at type and rows first.
3. Find the step examining far more rows than it returns.
4. Ask why: no index, an index defeated by a function, a bad estimate, or a genuinely unselective filter.
5. Change one thing. Re-run EXPLAIN. Confirm with EXPLAIN ANALYZE.

And always EXPLAIN a new query against a large table before running it. That habit is the cheapest incident prevention available.

What to remember

• type first: ALL on a big table with a selective filter is the target.
• Using index is good; Using filesort and Using temporary together are worth a look.
• rows is a sampled estimate — it drifts between runs and was 80% out here.
• EXPLAIN ANALYZE runs the query and gives real counts.
• FORMAT=TREE for anything with joins.