Put the word EXPLAIN in front of a SELECT, UPDATE, DELETE or INSERT ... SELECT and MySQL shows the plan it would use to run the query: which tables it reads, in what order, through which index, and roughly how many rows it expects to touch. Plain EXPLAIN does not run the query, so it is safe on a slow statement. This page builds a small test database and reads real plans from MySQL 8.0.
Test data
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(40),
city VARCHAR(20),
email VARCHAR(60) UNIQUE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2),
status VARCHAR(10),
KEY idx_cust (customer_id)
);
SET SESSION cte_max_recursion_depth = 100000;
INSERT INTO customers
WITH RECURSIVE n AS (SELECT 1 i UNION ALL SELECT i+1 FROM n WHERE i < 2000)
SELECT i, CONCAT('Customer ', i),
ELT(1 + MOD(i,4), 'Noida','Bengaluru','Chennai','Hyderabad'),
CONCAT('c', i, '@example.com')
FROM n;
INSERT INTO orders
WITH RECURSIVE n AS (SELECT 1 i UNION ALL SELECT i+1 FROM n WHERE i < 20000)
SELECT i, 1 + MOD(i*13, 2000), DATE('2025-01-01') + INTERVAL MOD(i,365) DAY,
200 + MOD(i*31, 5000), ELT(1 + MOD(i,3), 'paid','pending','refunded')
FROM n;
ANALYZE TABLE customers, orders;
The columns, in the order you should read them
| Column | What it tells you |
|---|---|
table | Which table (or alias) this row of the plan reads. One row per table access, listed in join order. |
type | How the table is accessed. The most important column; see the next table. |
possible_keys | Indexes MySQL considered. NULL means no index fits the conditions. |
key | The index it actually chose. NULL with a large rows value is a warning sign. |
key_len | Bytes of the index used. On a composite index, tells you how many columns were used. |
ref | What the index is compared with: const, or a column from an earlier table. |
rows | Estimated rows examined for this step (an estimate from index statistics, not a count). |
filtered | Estimated percentage of those rows that survive the remaining conditions. |
Extra | Notes such as Using index, Using where, Using filesort, Using temporary. |
Access types from best to worst, with real output
EXPLAIN SELECT * FROM customers WHERE customer_id = 42; -- 1
EXPLAIN SELECT order_id, amount FROM orders WHERE customer_id = 42; -- 2
EXPLAIN SELECT order_id FROM orders WHERE order_id BETWEEN 100 AND 200; -- 3
EXPLAIN SELECT customer_id FROM orders; -- 4
EXPLAIN SELECT * FROM orders WHERE status = 'pending'; -- 5
| # | type | key | ref | rows | Extra |
|---|---|---|---|---|---|
| 1 | const | PRIMARY | const | 1 | NULL |
| 2 | ref | idx_cust | const | 10 | NULL |
| 3 | range | PRIMARY | NULL | 101 | Using where; Using index |
| 4 | index | idx_cust | NULL | 20178 | Using index |
| 5 | ALL | NULL | NULL | 20178 | Using where |
- const: at most one matching row, found through the primary key or a unique index (the
emaillookup also givesconst). - eq_ref: in a join, one row from this table per row of the previous table, via a primary or unique key. You will see it below.
- ref: lookup on a non-unique index; several rows can match.
- range: an index range scan for
BETWEEN,<,>,INorLIKE 'abc%'. - index: reads the whole index. Cheaper than a table scan only because the index is smaller.
- ALL: full table scan. Fine on a 50-row lookup table, a problem on a big one.
Notice query 5: filtered shows 10.00, but a third of the orders are actually pending. Without an index or histogram on status, MySQL simply guesses. After CREATE INDEX idx_status ON orders (status); the same query becomes type = ref with rows = 6667, which is close to the real 6,667. (In MySQL 8 you can also give the optimizer better estimates without an index: ANALYZE TABLE orders UPDATE HISTOGRAM ON status;.)
Reading a join plan
EXPLAIN
SELECT c.city, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.city
ORDER BY revenue DESC;
| table | type | key | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| o | ALL | NULL | NULL | 20178 | 10.00 | Using where; Using temporary; Using filesort |
| c | eq_ref | PRIMARY | expl.o.customer_id | 1 | 100.00 | NULL |
Read it top to bottom: MySQL scans every order, keeps the paid ones, and for each one fetches exactly one customer by primary key (eq_ref). The ref value expl.o.customer_id is database.alias.column; expl was the test database's name. Using temporary; Using filesort comes from the GROUP BY on another table's column followed by sorting on a computed total. With only four cities that is cheap; it becomes expensive when the grouped result is large.
A rough cost estimate for a join is the product of the rows values down the plan (here 20178 × 1).
EXPLAIN FORMAT=TREE and EXPLAIN ANALYZE (MySQL 8)
The tree format shows the same plan as nested steps, which is easier to follow for joins and subqueries:
-> Sort: revenue DESC
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Nested loop inner join (cost=2748 rows=2018)
-> Filter: (o.`status` = 'paid') (cost=2042 rows=2018)
-> Table scan on o (cost=2042 rows=20178)
-> Single-row index lookup on c using PRIMARY (customer_id=o.customer_id) (cost=0.25 rows=1)
EXPLAIN ANALYZE (8.0.18 and later) actually runs the query and adds real timings and real row counts next to the estimates:
-> Index lookup on orders using idx_cust (customer_id=42) (cost=3.5 rows=10)
(actual time=0.0587..0.0613 rows=10 loops=1)
The times will be different on your machine. What matters is comparing estimated rows with actual rows: a large gap means the optimizer is working from bad statistics. Because it executes the statement, do not run EXPLAIN ANALYZE on a heavy query on a busy production server.
Common mistakes when reading plans
- Treating
rowsas exact. It is an estimate; runANALYZE TABLEif the numbers look far off. - Panicking at
Using where. It only means a condition is checked after rows are read, and appears on many good plans. - Ignoring
key_len. If a composite index on(city, order_date)shows the key length ofcityalone, the date part is not being used. - Testing on an empty development table. With 10 rows MySQL will choose a table scan, and the plan tells you nothing about production.
For worked fixes based on these plans, read how to optimize MySQL queries and MySQL indexes explained. The clustered-index behaviour behind Using index is covered in clustered vs non-clustered indexes, and finding which queries to explain in the first place is covered in how to find and fix slow queries.