# How to read a MySQL EXPLAIN plan

> **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

```sql
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

```sql
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:

```sql
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:

```sql
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

```sql
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

```sql
DROP DATABASE explain_demo;
```

## Related

- [Why is MySQL not using my index?](https://www.dbgorilla.com/learn/mysql/why-is-mysql-not-using-my-index/)
- [How to add an index in MySQL without downtime](https://www.dbgorilla.com/learn/mysql/how-to-add-an-index-in-mysql-without-downtime/)
- [How to find slow queries in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-find-slow-queries-in-mysql/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)