How to read a MySQL EXPLAIN plan

Quick answer: EXPLAIN shows the plan the optimizer intends, based on estimates. Five columns carry most of the useful information: type (the access method), key (the index chosen), rows and filtered (how much work it expects), and Extra (where Using filesort and Using temporary appear). EXPLAIN ANALYZE runs the query and prints actual rows beside estimated rows. The difference points to the problem, because many bad plans result from a bad estimate rather than a bad decision.

EXPLAIN answers one question: what does the optimizer plan to do, and how much work does it think that will be?

The word “thinks” matters here. The plan is based on estimates, and when a plan is bad it is usually because an estimate was wrong rather than because the optimizer chose badly given what it believed.

Set up something to explain

CREATE DATABASE explain_demo;
USE explain_demo;

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  status      VARCHAR(20) NOT NULL,
  total_cents INT NOT NULL,
  created_at  DATETIME NOT NULL
);

INSERT INTO orders (customer_id, status, total_cents, created_at)
SELECT
  n MOD 500 + 1,
  ELT(n MOD 3 + 1, 'pending', 'shipped', 'cancelled'),
  100 + (n * 37) MOD 50000,
  NOW() - INTERVAL (n MOD 365) DAY
FROM (
  SELECT ROW_NUMBER() OVER () AS n
  FROM information_schema.columns a, information_schema.columns b
  LIMIT 20000
) s;

ANALYZE TABLE orders;

Every value comes from the row number rather than from RAND(), so the counts below are fixed: 20,000 rows, 500 customers with 40 rows each, and 6,667 of them shipped. Every plan on this page came from one session against exactly this table on MySQL 8.4.11, which matters because the estimates below can be compared with each other only if they came from the same statistics.

The columns that matter

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

type: ALL   possible_keys: NULL   key: NULL   rows: 19729   filtered: 10.00
Extra: Using where

That table has no index on customer_id, so this is the worst row of the ladder below and a useful place to start. Five columns carry most of the information.

type: the access method and the first thing to check. Best to worst:

type	What it means
const, eq_ref	One row, by unique key. As good as it gets.
ref	Index lookup returning several rows. Normal and healthy.
range	Index range scan. Fine for BETWEEN, >, IN.
index	Full scan of the index. Usually cheaper than ALL, still a scan.
ALL	Full table scan.

ALL is not automatically wrong. On a 200-row lookup table a scan beats an index every time and the optimizer knows it. It matters when the table is big, and it matters on the lower line of a join, though not always for the reason people expect: see the join section below, where a type: ALL there turns out to be a single scan.

key: the index actually chosen. NULL here with a large rows value is the combination worth investigating.

rows: how many rows the optimizer estimates it will read. It is an estimate, and for a full scan a loose one: InnoDB derives it by sampling index pages, so the table above reported 19,729 on one run and 20,237 on another with the same 20,000 rows in it. Do not read a full-scan rows as a count.

filtered: the percentage of those rows expected to survive conditions the index could not apply. rows × filtered / 100 is what it expects to pass on. A rows of 40,000 with filtered of 2 means it plans to read 40,000 rows and throw away 39,200 of them.

Treat filtered: 10.00 with suspicion, because it is often not an estimate at all. In the plan above it is MySQL’s fixed guess for an equality it has no statistics for. 19729 × 10 / 100 predicts 1,973 rows; the query returns 40. The number is a placeholder, not a measurement.

Add the index and the same lookup becomes:

CREATE INDEX idx_customer ON orders (customer_id);
ANALYZE TABLE orders;

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

type: ref   possible_keys: idx_customer   key: idx_customer   key_len: 4
ref: const   rows: 40   filtered: 100.00

rows: 40 is exactly right, and so is filtered: 100.00, because an index gives the optimizer something to count instead of something to assume. The rows for an index lookup is more useful than the rows for a scan.

Extra: where expensive work appears:

• Using index: good. The index covers the query, so no table lookup is needed.
• Using where: normal. Rows are filtered after retrieval.
• Using filesort: a sort that could not be satisfied by an index order.
• Using temporary: an internal temp table, usually for GROUP BY or DISTINCT.

Using filesort and Using temporary together on a large result set often indicate a slow GROUP BY … ORDER BY, and it is easy to produce:

EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY COUNT(*) DESC;

type: ALL   key: NULL   rows: 19729   Extra: Using temporary; Using filesort

A covering index appears in the same column:

EXPLAIN SELECT customer_id FROM orders WHERE customer_id BETWEEN 10 AND 20;

type: range   key: idx_customer   rows: 440   Extra: Using where; Using index

Using index there means the whole query was answered from the index and the table was never touched. rows: 440 is the exact answer again: eleven customers at forty rows each.

EXPLAIN ANALYZE is the one that tells you the truth

EXPLAIN shows what the optimizer believes. EXPLAIN ANALYZE runs the query and reports what actually happened beside what was predicted:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND customer_id = 42;

-> Filter: (orders.`status` = 'pending')  (cost=10.4 rows=4) (actual time=0.198..0.211 rows=13 loops=1)
    -> Index lookup on orders using idx_customer (customer_id=42)  (cost=10.4 rows=40) (actual time=0.192..0.201 rows=40 loops=1)

It is a tree. Read it from the inside out. Each node carries two row counts: rows= inside the cost= bracket is the estimate, and rows= inside the actual bracket is what happened.

Compare them node by node, because the two nodes here disagree in different ways:

• The index lookup estimated 40 and got 40. The estimate is exact because the index holds the real count.
• The filter on status estimated 4 and got 13, a factor of three out, because status has no index and MySQL guessed how many of those forty rows would be pending.

The difference between estimated and actual is the diagnosis. Fix the node where the difference first appears. A step that is wrong by 3× here can be wrong by far more by the time it feeds a join, because the error multiplies.

When they disagree badly, refresh the statistics before you change anything else:

ANALYZE TABLE orders;

Caution

EXPLAIN ANALYZE executes the statement. On an UPDATE or DELETE it performs the write. Wrap it in a transaction you roll back, or run it against a clone.

Reading a join plan

Rows appear in the order MySQL processes them: the first is the driving table, and the ones below it are what it joins to. In a join plan, check how many times the lower line runs. Since MySQL 8.0.18, that depends on the join algorithm it picked.

Take the join with no usable index on the join column:

EXPLAIN SELECT o.id, o.total_cents
FROM orders o IGNORE INDEX (idx_customer)
JOIN orders o2 IGNORE INDEX (idx_customer) ON o2.customer_id = o.customer_id
WHERE o.status = 'shipped';

| table | type | key  | rows  | filtered | Extra                                      |
| o     | ALL  | NULL | 19729 |    10.00 | Using where                                |
| o2    | ALL  | NULL | 19729 |     0.20 | Using where; Using join buffer (hash join) |

Two type: ALL lines look alarming, and the old rule of thumb says the second one is 6,667 scans. It is not. The important text in that row is Using join buffer (hash join), and EXPLAIN ANALYZE shows the cost:

-> Inner hash join (o2.customer_id = o.customer_id)  (cost=3.89e+6 rows=77847) (actual time=13.7..50.5 rows=266680 loops=1)
    -> Table scan on o2  (cost=0.0229 rows=19729) (actual time=0.344..6.08 rows=20000 loops=1)
    -> Hash
        -> Filter: (o.`status` = 'shipped')  (cost=1997 rows=1973) (actual time=0.447..11.9 rows=6667 loops=1)
            -> Table scan on o  (cost=1997 rows=19729) (actual time=0.444..8.72 rows=20000 loops=1)

loops=1 on both table scans. MySQL built a hash table from the 6,667 shipped rows and scanned o2 once against it. Each table was read exactly once, and the whole join took 50 ms.

Now allow the index and the plan changes:

EXPLAIN SELECT o.id, o.total_cents
FROM orders o
JOIN orders o2 ON o2.customer_id = o.customer_id
WHERE o.status = 'shipped';

| table | type | key          | rows  | filtered | Extra       |
| o     | ALL  | NULL         | 19729 |    10.00 | Using where |
| o2    | ref  | idx_customer |    39 |   100.00 | Using index |

-> Nested loop inner join  (cost=11738 rows=77847) (actual time=0.472..123 rows=266680 loops=1)
    -> Filter: (o.`status` = 'shipped')  (cost=1997 rows=1973) (actual time=0.442..14.7 rows=6667 loops=1)
        -> Table scan on o  (cost=1997 rows=19729) (actual time=0.439..11.3 rows=20000 loops=1)
    -> Covering index lookup on o2 using idx_customer (customer_id=o.customer_id)  (cost=0.994 rows=39.5) (actual time=0.00793..0.0128 rows=40 loops=6667)

loops=6667 states the multiplication. This is the nested loop: one index lookup per shipped row. The estimated cost drops from 3.89 million to 11,738, which is why the optimizer prefers it.

The two outputs write the o2 estimate differently (39 in the table, 39.5 in the tree), and neither is wrong. It is one number: the sampled table size divided by the index’s distinct values, 19729 / 500 = 39.46. The tabular rows column is an integer and truncates; the tree keeps about three significant figures, so it prints the decimal. Both blocks above come from the same session, which is the only way that comparison means anything.

That estimate is an average, because the value being looked up is a column from the other table and is not known until the query runs. Compare it with the literal lookup earlier on this page, which estimated 40 for customer_id = 42 and was exactly right. A literal lets MySQL walk the index and count the real entries, so the estimate follows the data: on a copy of this table with 35 of customer 43’s rows deleted, WHERE customer_id = 43 estimated 5, not the 39 average.

Two things to take from the pair:

loops in EXPLAIN ANALYZE is the only place the repetition is written down. Plain EXPLAIN never says how many times a row runs. You infer it from the row above, and since 8.0.18 that inference is wrong whenever Extra says Using join buffer (hash join).

A type: ALL on the lower line of a join is no longer automatically one scan per outer row. Check Extra first. Hash join means once; absence of it means the old multiplication still applies, and then an index on the join column is what turns that line into ref.

A last trap in that second plan: the timing got worse, 123 ms against 50 ms, while the cost estimate got 300 times better. On a self-join of a table this small, entirely in the buffer pool, the hash join genuinely wins and the optimizer’s model does not know it. The cost figure models work; it does not measure elapsed time.

Clean up

DROP DATABASE explain_demo;

How DBGorilla helps

DBGorilla can help

DBGorilla runs EXPLAIN for you, so your AI coding agent can pull the plan for a query it found in the digest ranking with the schema and index definitions beside it, which is the context that turns type: ALL from a letter into a diagnosis.

Start free

FAQ

• What is the difference between EXPLAIN and EXPLAIN ANALYZE in MySQL?

  EXPLAIN shows the plan the optimizer intends to use, with estimated row counts. EXPLAIN ANALYZE actually runs the query and reports the real row counts and timings next to the estimates. Comparing the two is how you find a bad estimate, which is the root cause of most bad plans.

• Is type ALL always bad?

  No. On a small table a full scan is often the fastest plan and the optimizer is right to choose it. On the lower line of a join it used to mean one scan per outer row, but since MySQL 8.0.18 it may instead be a hash join, which scans that table once. Measured on 8.4.11, check Extra: Using join buffer (hash join) means once, and its absence means the old multiplication still applies.

• What does the filtered column mean?

  The percentage of rows the optimizer expects to survive the WHERE conditions that could not be applied by the index. rows multiplied by filtered, divided by 100, is the row count it expects to hand to the next step. A low filtered value means the index gets you into the neighbourhood and then most of what it found is thrown away.

• Does EXPLAIN ANALYZE execute the query?

  Yes, fully, including writes. Never run it on an UPDATE or DELETE you did not intend to perform. Wrap it in a transaction you roll back, or run it against a clone.

Related

• Why is MySQL not using my index?
• How to add an index in MySQL without downtime
• How to find slow queries in MySQL
• Product: DBGorilla docs