What EXPLAIN does
When you send a query, MySQL's optimiser decides how to run it: which table to read first, which index to use, in what order to join. You cannot see that decision by looking at the SQL. EXPLAIN in front of a SELECT (or UPDATE, DELETE, INSERT ... SELECT) prints the plan without running the statement. It is the main tool for finding out why a query is slow.
The optimiser makes its choices from statistics about your tables. After loading a lot of data, refresh them so the plan reflects reality:
USE shop;
ANALYZE TABLE customers, orders, order_items, products;+------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+---------+----------+----------+
| shop.customers | analyze | status | OK |
| shop.orders | analyze | status | OK |
| shop.order_items | analyze | status | OK |
| shop.products | analyze | status | OK |
+------------------+---------+----------+----------+Reading your first plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | NULL | ref | customer_id | customer_id | 4 | const | 4 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+Here is what the important columns mean.
| Column | Meaning |
|---|---|
table |
which table this row of the plan reads |
type |
how rows are found (the key column to study, see below) |
possible_keys |
indexes MySQL could use |
key |
the index it actually chose (NULL means none) |
rows |
estimated number of rows it must examine |
filtered |
estimated percentage of those rows that survive the WHERE |
Extra |
extra notes: sorting, temporary tables, index-only reads |
The access type, best to worst
type |
Meaning |
|---|---|
system, const |
at most one row, found by a primary or unique key with a constant. Fastest |
eq_ref |
one row per row of the previous table, through a primary or unique key (typical in joins) |
ref |
several rows through a non-unique index |
range |
an index range: BETWEEN, <, >, IN, LIKE 'abc%' |
index |
a full scan of an index. Better than reading the table, still reads everything |
ALL |
a full table scan. Reads every row |
On a 12-row table a full scan is perfectly fine. The same plan on 50 million rows is a disaster, so judge it against the table size.
Seeing each type
A lookup by a unique column is the best case:
EXPLAIN SELECT * FROM customers WHERE email = 'asha.verma@example.com';+----+-------------+-----------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-------+---------+-------+------+----------+-------+
| 1 | SIMPLE | customers | NULL | const | email | email | 402 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+-------+---------+-------+------+----------+-------+A lookup by a foreign key column uses the index MySQL created for it:
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | NULL | ref | customer_id | customer_id | 4 | const | 4 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+Filtering on a column with no index scans the table. order_date has none yet:
EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-07-01';+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+Add an index and run it again. With very few rows the optimiser may still prefer a scan, but for a selective range it switches:
CREATE INDEX idx_orders_date ON orders (order_date);
EXPLAIN SELECT * FROM orders WHERE order_date >= '2024-07-01';+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | orders | NULL | range | idx_orders_date | idx_orders_date | 3 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+Joins
In a join, EXPLAIN prints one line per table, in the order MySQL reads them:
EXPLAIN SELECT o.id, c.first_name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.status = 'shipped';+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| 1 | SIMPLE | o | NULL | ALL | customer_id | NULL | NULL | NULL | 20 | 25.00 | Using where |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | shop.o.customer_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+MySQL decides to read orders first (filtering on status) and then fetch each customer by primary key (eq_ref). The join column on the second table must be indexed, or the plan degrades into a scan for every row of the first table, which is the classic cause of slow joins.
Sorting and Extra
EXPLAIN SELECT * FROM products ORDER BY price;+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | products | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+Using filesort means MySQL gathers the rows and sorts them itself, because no index delivers the rows in that order. On 15 rows that costs nothing. On a big table it matters, and the next section shows how an index removes the sort.
Two other Extra values are worth recognising. Using index means all needed columns are in the index (a covering index), so the table is not touched. Using temporary means a temporary table is used, typical of some GROUP BY and DISTINCT queries on unindexed columns.
Measuring the real work
Estimates can be wrong, so confirm with exact counts, as in the indexes lesson. Build the same 39,304-row table:
CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS log_entries;
CREATE TABLE log_entries (
id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, action VARCHAR(20) NOT NULL,
created_on DATE NOT NULL, amount DECIMAL(8,2) NOT NULL, INDEX idx_created (created_on)
);
INSERT INTO log_entries (user_id, action, created_on, amount)
SELECT (a.id * 7 + b.id * 3 + c.id) % 2000 + 1,
ELT((a.id + b.id + c.id) % 4 + 1, 'login', 'view', 'purchase', 'logout'),
DATE_ADD('2024-01-01', INTERVAL (a.id * b.id + c.id) % 300 DAY),
((a.id * 31 + b.id * 17 + c.id * 13) % 50000) / 100
FROM shop.order_items a, shop.order_items b, shop.order_items c;
ANALYZE TABLE log_entries;+----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| practice.log_entries | analyze | status | OK |
+----------------------+---------+----------+----------+Count the entries from March 2024 in two ways. First with functions on the indexed column:
FLUSH STATUS;
SELECT COUNT(*) AS march_entries FROM log_entries WHERE MONTH(created_on) = 3 AND YEAR(created_on) = 2024;
SHOW SESSION STATUS LIKE 'Handler_read_next';+---------------+
| march_entries |
+---------------+
| 5326 |
+---------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 39304 |
+-------------------+-------+Then with a plain range on the raw column:
FLUSH STATUS;
SELECT COUNT(*) AS march_entries FROM log_entries WHERE created_on >= '2024-03-01' AND created_on < '2024-04-01';
SHOW SESSION STATUS LIKE 'Handler_read_next';+---------------+
| march_entries |
+---------------+
| 5326 |
+---------------+
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Handler_read_next | 5326 |
+-------------------+-------+The two queries return the same count. The first stepped through every one of the 39,304 index entries, calculating MONTH() and YEAR() for each, because the function hid the column's sort order from MySQL. The second jumped straight to the first entry on 1 March and read only the 5,326 March entries. On a table of millions of rows that is the difference between a second and a blink.
The same index also removes a sort. Ordering by the indexed column with LIMIT lets MySQL read the first few entries in order and stop:
EXPLAIN SELECT * FROM log_entries ORDER BY created_on LIMIT 5;+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| 1 | SIMPLE | log_entries | NULL | index | NULL | idx_created | 3 | NULL | 5 | 100.00 | NULL |
+----+-------------+-------------+------------+-------+---------------+-------------+---------+------+------+----------+-------+There is no Using filesort, and the estimate is just 5 rows. Ordering by amount, which has no index, forces a sort of the whole table (Using filesort appears in Extra), though the row estimate for a full scan varies a little from run to run:
EXPLAIN SELECT * FROM log_entries ORDER BY amount LIMIT 5;Common slow patterns and fixes
| Pattern | Why it is slow | Fix |
|---|---|---|
WHERE FUNC(column) = x |
index cannot be used | compare the raw column: use ranges, or index an expression |
WHERE name LIKE '%x' |
no sorted starting point | LIKE 'x%', or a FULLTEXT index |
SELECT * on wide tables |
reads and sends unneeded data, blocks covering indexes | list only the columns you need |
WHERE a = 1 OR b = 2 |
two conditions, may need two indexes | index both columns, or use UNION ALL |
ORDER BY on an unindexed column |
filesort of all rows | index the sort column, together with the filter |
big OFFSET pagination |
reads and discards all skipped rows | keyset pagination: WHERE id > last_id ORDER BY id LIMIT n |
| comparing a string column with a number | implicit type conversion disables the index | compare like with like: phone = '555' |
| correlated subquery on an unindexed column | repeated scans | add an index on the correlated column, or use a join |
EXPLAIN ANALYZE
Plain EXPLAIN only predicts. EXPLAIN ANALYZE (MySQL 8.0.18 and later) runs the query and shows actual timings and row counts beside the estimates:
EXPLAIN ANALYZE SELECT COUNT(*) FROM log_entries WHERE created_on >= '2024-03-01' AND created_on < '2024-04-01';The output is a tree with lines such as actual time=0.05..0.7 rows=1 loops=1. A big gap between the estimated rows and the actual rows suggests stale statistics, so run ANALYZE TABLE. Because it executes the statement, never run it on an UPDATE or DELETE you are not ready to apply.
Finding slow queries in the first place
You do not have to guess which queries to examine. The slow query log records statements that exceed a time limit. An administrator can turn it on:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;The performance_schema and the sys schema (for instance sys.statement_analysis) summarise the heaviest statements. Start with the queries that run most often and cost most in total, not just the single slowest one.
Common mistakes
- Optimising without looking at a plan.
- Trusting estimates on stale statistics. Run
ANALYZE TABLE. - Adding an index and not checking that the optimiser uses it.
- Judging a plan on a tiny test table. Plans change with data size.
- Running EXPLAIN ANALYZE on a modifying statement.
Exercise
On the 39,304-row log_entries table, write a query that counts entries per action for the month of May 2024 using a range condition, and use EXPLAIN to see which index it uses. Then explain in one sentence why WHERE DATE_FORMAT(created_on, '%Y-%m') = '2024-05' would be slower.
Show one possible answer
EXPLAIN SELECT action, COUNT(*) AS entries
FROM log_entries
WHERE created_on >= '2024-05-01' AND created_on < '2024-06-01'
GROUP BY action;The plan shows idx_created as the key with type range, so only the May entries are read (Using temporary appears because grouping by an unindexed column needs a temporary table). The DATE_FORMAT version has to compute a string for every row, so MySQL cannot use the index on created_on and reads the whole table.
Key points
- EXPLAIN shows the plan MySQL would use: which table it reads first, which index it picks, and how many rows it expects to examine.
- The access type matters most: const, eq_ref and ref are fast, range is good, index is a full index scan and ALL is a full table scan.
- Extra tells you about sorting and temporary tables: Using filesort and Using temporary are warning signs on big tables.
- Typical fixes: add the right index, do not wrap indexed columns in functions, avoid leading wildcards and select only the columns you need.