Reading EXPLAIN
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Before MySQL runs a query, its optimizer (the part of MySQL that decides how to run a query) picks a plan: which table to read first, which index to use, whether to sort. EXPLAIN shows you that plan, so you can see whether your index is used without guessing.
EXPLAIN shows the plan, not the result
EXPLAIN placed before a SELECT, INSERT, UPDATE or DELETE returns the optimizer's plan for that statement instead of running it. You get one row per table the query reads, saying how it will be read.
A correct query can still be slow. EXPLAIN shows the plan without running the query; the script below plans one statement and reads the columns of the result.
Example 1 · EXPLAIN plans a statement but does not run it
EXPLAIN SELECT * FROM orders WHERE status = 'PLACED';
EXPLAIN DELETE FROM orders WHERE status = 'CANCELLED';
-- Nothing was deletedSELECT status, COUNT(*) AS ordersFROM ordersGROUP BY statusORDER BY status;+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 25.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | DELETE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 15 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ +-----------+--------+ | status | orders | +-----------+--------+ | PLACED | 3 | | SHIPPED | 2 | | DELIVERED | 8 | | CANCELLED | 2 | +-----------+--------+
What this code does
- The first plan reads
orderswithtype = ALL: there is no index onstatus, so MySQL plans to read all 15 rows (rows) and keep only a part of them (filtered, a rough guess, because MySQL has no index or statistics onstatus). EXPLAIN DELETEprinted a plan and deleted nothing: the twoCANCELLEDorders are still there. Plans are estimates, so yourrowsandfilteredmay differ.
Why look at the plan? Rows examined tell you that a query read too much; the plan tells you why: no usable index, the wrong index, or an extra sort. It is the first thing to run on a slow query. Because plain EXPLAIN does not run the statement, it is safe on a DELETE or an UPDATE, and fast even on a query that would take minutes. The numbers in it are estimates from index statistics, so they can differ a little between computers and between runs.
table: which table this row of the plan is about.type: how the table is read, from best (const) to worst (ALL).possible_keysandkey: the indexes that could help, and the one chosen.rowsandfiltered: estimated rows to read, and the estimated percentage left after theWHERE.
The type column: ALL, ref, range and const
The type column of EXPLAIN says how MySQL finds the rows in a table, from best (const) to worst (ALL). It is the first column to read, before key, rows and Extra.
The type column is the first to read. The picture ranks the access types; the scripts below show const, ref, range and ALL on 50,000 payments, and eq_ref in a join.
Example 2 · const, ref, range and ALL on 50,000 payments
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_vpa ON upi_payments (payer_vpa);CREATE INDEX idx_upi_paid_at ON upi_payments (paid_at);ANALYZE TABLE upi_payments;
EXPLAIN SELECT * FROM upi_payments WHERE payment_id = 42;
EXPLAIN SELECT * FROM upi_payments WHERE payer_vpa = 'user42@okbank';
EXPLAIN SELECT * FROM upi_paymentsWHERE paid_at >= '2026-03-01' AND paid_at < '2026-03-02';
EXPLAIN SELECT * FROM upi_payments WHERE amount = 500;+-----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+---------+----------+----------+ | practice.upi_payments | analyze | status | OK | +-----------------------+---------+----------+----------+ +----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | upi_payments | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ +----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | upi_payments | NULL | ref | idx_upi_vpa | idx_upi_vpa | 162 | const | 10 | 100.00 | NULL | +----+-------------+--------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ +----+-------------+--------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | upi_payments | NULL | range | idx_upi_paid_at | idx_upi_paid_at | 5 | NULL | 144 | 100.00 | Using index condition | +----+-------------+--------------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+ +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | upi_payments | NULL | ALL | NULL | NULL | NULL | NULL | 48751 | 10.00 | Using where | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
What this code does
payment_id = 42is a primary key lookup:type = const,rows = 1.payer_vpahas a non-unique index, so it isrefwith about 10 rows: each of the 5,000 users made about 10 payments.- The date condition is a range on
idx_upi_paid_at:type = range, about 144 rows (one payment every 10 minutes for a day).amounthas no index:type = ALL,key = NULL, about 50,000 rows to read.
Example 3 · eq_ref in a join of ShopLite tables
EXPLAINSELECT o.order_id, c.name, o.statusFROM orders oJOIN customers c ON c.customer_id = o.customer_idWHERE o.status = 'PLACED';+----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+ | 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 | 15 | 25.00 | Using where | | 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | practice.o.customer_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
What this code does
- The plan has one row per table, in the order MySQL reads them.
orderscomes first withtype = ALL: no index onstatus. - For every order,
customersis read withtype = eq_refonPRIMARY: exactly one customer per order, found through the primary key.refshows where the value comes from:o.customer_idof the current order (printed with the database name in front).
const: at most one row, found by the primary key or a unique index compared with a constant (WHERE payment_id = 42).eq_refis the same idea inside a join: one row per row of the previous table.ref: an index lookup that can match several rows (WHERE payer_vpa = 'user42@okbank'on a non-unique index).range: a range scan on an index (BETWEEN,<,>,IN,LIKE 'abc%').index: reads the whole index from start to end;ALL: a full table scan, reading every row.
Predict the output
Which type will EXPLAIN show?
Use the practice database. Only primary keys and unique emails are indexed.
Round 1 of 3
Which type does EXPLAIN show for this query?
ALL is not always wrong: on a 15-row table, or when the query needs most of the rows, a scan is the cheapest plan. It is a warning sign on a big table when the query returns few rows.key, rows and Extra
A covering index is an index that holds every column a query needs, so MySQL answers from the index alone without reading the table rows. EXPLAIN shows it as Using index in the Extra column.
The key, rows and Extra columns explain the plan. The script below reads Extra for a covering read, a filesort, and a sort that the index avoids.
Example 4 · Extra: Using index, Using filesort and a sort the index avoids
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE upi_payments ( payment_id INT PRIMARY KEY, payer_vpa VARCHAR(40) NOT NULL, mobile VARCHAR(10) NOT NULL, amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) NOT NULL, paid_at DATETIME NOT NULL);
INSERT INTO upi_paymentsWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, CONCAT('user', n % 5000, '@okbank'), CONCAT('98', LPAD(n * 7 % 100000000, 8, '0')), 10 + (n * 37) % 5000, IF(n % 50 = 0, 'FAILED', 'SUCCESS'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (n * 600) SECONDFROM seq;
CREATE INDEX idx_upi_vpa ON upi_payments (payer_vpa);CREATE INDEX idx_upi_paid_at ON upi_payments (paid_at);ANALYZE TABLE upi_payments;
-- Only payer_vpa is needed: the index alone answers itEXPLAIN SELECT payer_vpa FROM upi_paymentsWHERE payer_vpa LIKE 'user42%';
-- No index on amount: MySQL must sort 50,000 rowsEXPLAIN SELECT * FROM upi_payments ORDER BY amount DESC LIMIT 5;
-- paid_at is indexed: read the last 5 index entries, no sortEXPLAIN SELECT * FROM upi_payments ORDER BY paid_at DESC LIMIT 5;+-----------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+---------+----------+----------+ | practice.upi_payments | analyze | status | OK | +-----------------------+---------+----------+----------+ +----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | upi_payments | NULL | range | idx_upi_vpa | idx_upi_vpa | 162 | NULL | 1110 | 100.00 | Using where; Using index | +----+-------------+--------------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+ +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | 1 | SIMPLE | upi_payments | NULL | ALL | NULL | NULL | NULL | NULL | 49961 | 100.00 | Using filesort | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+----------------+ +----+-------------+--------------+------------+-------+---------------+-----------------+---------+------+------+----------+---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-----------------+---------+------+------+----------+---------------------+ | 1 | SIMPLE | upi_payments | NULL | index | NULL | idx_upi_paid_at | 5 | NULL | 5 | 100.00 | Backward index scan | +----+-------------+--------------+------------+-------+---------------+-----------------+---------+------+------+----------+---------------------+
What this code does
Using indexon the first plan: the query needs onlypayer_vpa, which is inidx_upi_vpa, so MySQL never touches the table rows.Using whereis shown because theLIKEis still checked on each entry.ORDER BY amountshowstype = ALLandUsing filesort: read everything, then sort.ORDER BY paid_at DESC LIMIT 5usesidx_upi_paid_atand plans to read only 5 entries;Backward index scanmeans it walks the sorted index from the end.
key is the index the optimizer chose; NULL means no index. If possible_keys lists an index but key is NULL, the optimizer saw the index and decided a scan was cheaper, or could not use it; the lesson "When an index is not used" covers why. key_len shows how many bytes of the index are used, which tells you how many columns of a composite index take part. rows is the estimated number of rows MySQL will examine in that table. Multiply the rows of all tables in a join to get a rough feel for the work. Extra adds notes about the plan. Besides Using index (a covering index, defined above), these three matter most:
Using where: rows are read, then filtered by theWHEREcondition. Normal, but withtype = ALLit means a scan with a filter.Using index condition: part of theWHEREis checked inside the index before reading rows.Using filesort/Using temporary: MySQL must sort, or build a temporary table, because no index gives the order or grouping. Costly on many rows.
EXPLAIN FORMAT=TREE
EXPLAIN FORMAT=TREE (MySQL 8.0.16 and later) prints the plan as an indented tree of steps such as Table scan, Index lookup, Filter and Nested loop inner join, each with an estimated cost and rows.
The tree format shows the same join as a list of steps. The example below prints it, and the text then shows how to read it from the innermost line outwards.
Example 5 · The same join as a tree
EXPLAIN FORMAT=TREESELECT o.order_id, c.name, o.statusFROM orders oJOIN customers c ON c.customer_id = o.customer_idWHERE o.status = 'PLACED'\G*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=3.06 rows=3.75)
-> Filter: (o.`status` = 'PLACED') (cost=1.75 rows=3.75)
-> Table scan on o (cost=1.75 rows=15)
-> Single-row index lookup on c using PRIMARY (customer_id = o.customer_id) (cost=0.277 rows=1)What this code does
- Read from the deepest line up:
Table scan on oreads the orders,Filterkeeps thePLACEDones, and for each of themSingle-row index lookup on c using PRIMARYfetches the customer.Nested loop inner joinjoins the two streams. costis the optimizer's own unit of work androwsits estimate. Both are guesses; the next lesson'sEXPLAIN ANALYZEadds what really happened.\Gprinted the plan as a list instead of a wide table.
Read it from the most indented line outwards: the innermost steps run first and feed their rows to the step above. In a join, the first child of Nested loop inner join is the outer table, read once, and the second child is the inner table, looked up once for every outer row.
The tree form is also the base of EXPLAIN ANALYZE, which the next lesson covers. Exact wording varies a little between MySQL versions.
\G instead of ; in the mysql client: it prints each result row as a list. In MySQL Workbench, end with ; and open the cell in the result grid, or use its Visual Explain button.Next step
Check your understanding
11 quick questions