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. type ALL matters when the table is large or when it appears on the inner side of a join, because then it runs once per outer row.

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.

How to read a MySQL EXPLAIN plan

Last updated Aug 21, 2026

View as Markdown

Quick answer: EXPLAIN shows the plan the optimizer intends, built from estimates. Six columns carry the signal: type (the access method), key (the index chosen), rows and filtered (how much work it expects), and Extra (where Using filesort and Using temporary hide). EXPLAIN ANALYZE runs the query and prints actual rows beside estimated rows. That gap is the diagnosis, because most bad plans come 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 trap is that “thinks” is doing real work in that sentence. The plan is built from estimates, and when a plan is bad it is almost always 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
  FLOOR(1 + RAND() * 500),
  ELT(FLOOR(1 + RAND() * 3), 'pending', 'shipped', 'cancelled'),
  FLOOR(100 + RAND() * 50000),
  NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM information_schema.columns a, information_schema.columns b
LIMIT 20000;

ANALYZE TABLE orders;

The columns that matter

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Six columns carry almost all the signal.

type: the access method, and the first thing to look at. 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. 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, or when it is on the inner side of a join and therefore runs once per outer row.

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

rows: how many rows the optimizer estimates it will read. An estimate.

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.

Extra: where the expensive work hides:

Using index: good. Covering index, no table lookup needed.
Using where: normal. Rows 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 is the classic slow GROUP BY … ORDER BY.

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 next to what was predicted:

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

You get lines shaped like:

-> Filter: (orders.status = 'pending')  (cost=1.2 rows=3) (actual time=0.03..0.12 rows=41 loops=1)

Read rows=3 against actual … rows=41. The gap between estimated and actual is the diagnosis. A plan that is wrong by 10× at one step is usually wrong by far more by the time that step feeds a join.

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

ANALYZE TABLE orders;

Reading a join plan

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

Rows come out in the order MySQL will process them: the first row is the driving table, each row after that is probed once per row surviving the row above.

That multiplication is the whole game in a join plan. A type: ALL on the second line of a two-table join is not one scan, it is one scan per row from the first table. Fixing it means giving the optimizer an index on the join column so that line becomes ref.

Clean up

DROP DATABASE explain_demo;

Previous
Find slow queries Next
Index not being used