What EXPLAIN shows in MySQL
The EXPLAIN command makes the MySQL optimizer show a query's execution plan instead of running it: which tables get read, in what order, through which index, and how many rows the server expects to scan. Without a plan, query tuning turns into guessing — EXPLAIN turns it into checking concrete numbers.
A plan is built for SELECT, and also for UPDATE and DELETE — handy before changing a large table, to see in advance whether the query would fall back to a full scan.
Key plan columns: type, key, rows, Extra
Out of a dozen EXPLAIN columns, four matter in practice:
| Column | What it means |
|---|---|
| type | Access method for the table: const, eq_ref, ref, range, index, ALL |
| key | The index the optimizer actually picked |
| rows | Estimated rows to scan at this step |
| Extra | Notes such as Using filesort or Using temporary |
| key_len | How many bytes of the index actually take part in the lookup |
A value of ALL in the type column means a full table scan with no index. For a table with a couple thousand rows that is harmless, but for a table with 10 million rows it is a direct path to a slow query. For a detailed look at how indexes affect type and key, see the article on MySQL index optimization.
How EXPLAIN ANALYZE differs from plain EXPLAIN
Plain EXPLAIN is only the optimizer's prediction; the query never runs. EXPLAIN ANALYZE, available since MySQL 8.0.18, actually runs the query and adds the real time of each step and the real row count to the plan. This lets you compare the forecast against reality and find the step where the optimizer's estimate was off by a wide margin.
The downside of EXPLAIN ANALYZE is that it executes the query in full, including a heavy UPDATE. In production, run such checks against a copy of the database or inside a transaction followed by ROLLBACK.
How to read a plan for a query with JOIN
Take a typical orders query joined to customers and walk through the output line by line.
EXPLAIN SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;If the row for the orders table shows type equal to ref and key points at an index on status, the optimizer found the right rows quickly. If type instead shows ALL and Extra shows Using where; Using filesort, the query scans the whole table and sorts the result afterward in memory or on disk — a clear sign a composite index on (status, created_at) is missing.
Common problems visible in the plan
A few Extra notes almost always point at a bottleneck:
Using filesort— the server sorts rows separately because no index matched ORDER BY.Using temporary— GROUP BY or DISTINCT creates a temporary table, often on disk.Using join buffer— no index was found for the JOIN, and MySQL scans rows in memory.- A large rows value at the top of the plan when the query has a small LIMIT.
How to run EXPLAIN and EXPLAIN ANALYZE
The syntax is equally simple for both commands:
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;The JSON format shows more detail, including the cost of each step in the optimizer's own units, and works well for automated checks in CI. The plain tabular format is faster to read by eye for a one-off check. Slow queries found this way are easy to collect through the slow query log and review in a batch instead of one at a time.
Checklist for tuning a query from its plan
Before closing a ticket about a slow query, run through the list:
- type for every table in the plan is no worse than range, except for deliberately small lookup tables.
- key shows a real index, not empty.
- Extra has no Using filesort and no Using temporary on the hot path.
- rows at the top level matches the expected result size, not the whole table.
- EXPLAIN ANALYZE confirmed the rows estimate is close to the actual count.