EXPLAIN ANALYZE: real timings
45 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)EXPLAIN tells you what the optimizer plans to do. EXPLAIN ANALYZE runs the query and tells you what really happened in every step: how long it took, how many rows came out and how many times it ran. It turns "this query feels slow" into "this step takes 3.6 seconds".
EXPLAIN ANALYZE runs the query (MySQL 8.0.18 and later)
EXPLAIN ANALYZE runs the query, measures every step of the plan while it runs, and prints the plan as a tree with real time, rows and loops beside the optimizer's estimates. It needs MySQL 8.0.18 or later.
EXPLAIN only plans; EXPLAIN ANALYZE also runs the query. The script below measures a PNR lookup before and after an index, so every step has real numbers.
Example 1 · A PNR lookup measured before and after an index
SET SESSION cte_max_recursion_depth = 100000;
CREATE TABLE train_bookings ( booking_id INT PRIMARY KEY, pnr CHAR(10) NOT NULL, train_no INT NOT NULL, travel_date DATE NOT NULL, fare DECIMAL(8,2) NOT NULL);
INSERT INTO train_bookings (booking_id, pnr, train_no, travel_date, fare)WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100000)SELECT n, CAST(4210000000 + n * 7919 AS CHAR), 12000 + n % 500, DATE '2026-01-01' + INTERVAL (n % 365) DAY, 200 + (n * 37) % 2800FROM seq;
EXPLAIN ANALYZESELECT * FROM train_bookings WHERE pnr = '4211583800'\G
CREATE INDEX idx_bookings_pnr ON train_bookings (pnr);
EXPLAIN ANALYZESELECT * FROM train_bookings WHERE pnr = '4211583800'\G*************************** 1. row ***************************
EXPLAIN: -> Filter: (train_bookings.pnr = '4211583800') (cost=10000 rows=10000) (actual time=2.7..82.8 rows=1 loops=1)
-> Table scan on train_bookings (cost=10000 rows=100000) (actual time=2.44..66.8 rows=100000 loops=1)
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on train_bookings using idx_bookings_pnr (pnr = '4211583800'), with index condition: (train_bookings.pnr = '4211583800') (cost=0.35 rows=1) (actual time=0.551..0.56 rows=1 loops=1)What this code does
- Before the index:
Table scanproduced all 100,000 rows (rows=100000) and theFilterkept 1; the last row came after tens of milliseconds. Your times will differ. - After: a single
Index lookup ... using idx_bookings_pnr, a fraction of a millisecond. MySQL 9.x prints extra words such aswith index condition; MySQL 8.4 prints the same step without them.
Why is it needed when EXPLAIN exists? Estimates can be wrong, and a plan that looks fine can still be slow. Only a real run shows where the time went. A query that takes 2 minutes also takes 2 minutes under EXPLAIN ANALYZE. Each step now has two brackets: the estimate (cost=... rows=...) and the measurement (actual time=A..B rows=R loops=L).
actual time=A..B: milliseconds to the first row (A) and to the last row (B), per loop.rows=R: rows this step produced, per loop (an average).loops=L: how many times the step ran. Total work is about rows × loops, and total time about B × loops.
SELECT queries. It runs the statement for real, so never point it at a statement whose effects you do not want.Estimated versus actual rows
An estimate is the optimizer's guess of how many rows a step returns. The script below compares estimated rows with actual rows for a filter on a column that has no index.
Example 2 · A bad estimate: 10% guessed, 2% real
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;
EXPLAIN ANALYZESELECT COUNT(*) FROM upi_payments WHERE status = 'FAILED'\G*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=6152 rows=1) (actual time=24..24 rows=1 loops=1)
-> Filter: (upi_payments.`status` = 'FAILED') (cost=5000 rows=5000) (actual time=1.22..23.7 rows=1000 loops=1)
-> Table scan on upi_payments (cost=5000 rows=50000) (actual time=0.96..18.1 rows=50000 loops=1)What this code does
- The
Filterstep estimated 5,000 rows: 10% of the table, MySQL's guess for an=condition on a column it knows nothing about. The actual bracket shows 1,000 rows: only every 50th payment failed. - Here the plan is a scan either way, so the bad estimate costs nothing. In a join, the same mistake could make MySQL read the tables in the wrong order.
Compare rows= in the estimate bracket with rows= in the actual bracket, step by step. A difference of 2 or 3 times is normal. A difference of 10 or 100 times on a step that matters is worth fixing.
A common cause is a condition on a column with no index and no statistics. MySQL then guesses a fixed share of the rows, often 10%, whatever the real data looks like.
ANALYZE TABLE and statistics
Statistics are InnoDB's summary of the data in each index, such as roughly how many distinct values it holds. They are sampled, saved and recalculated automatically; ANALYZE TABLE rebuilds them at once.
Better statistics give better estimates. The script below builds a histogram on an unindexed column and shows the estimate move from a guessed 10% to the real 2%.
Example 3 · A histogram fixes the estimate
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;
ANALYZE TABLE upi_payments;ANALYZE TABLE upi_payments UPDATE HISTOGRAM ON status;
EXPLAIN ANALYZESELECT COUNT(*) FROM upi_payments WHERE status = 'FAILED'\G
SELECT COLUMN_NAME, JSON_UNQUOTE(JSON_EXTRACT(HISTOGRAM, '$."histogram-type"')) AS histogram_typeFROM information_schema.COLUMN_STATISTICSWHERE TABLE_NAME = 'upi_payments';+-----------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+---------+----------+----------+
| practice.upi_payments | analyze | status | OK |
+-----------------------+---------+----------+----------+
+-----------------------+-----------+----------+---------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+-----------+----------+---------------------------------------------------+
| practice.upi_payments | histogram | status | Histogram statistics created for column 'status'. |
+-----------------------+-----------+----------+---------------------------------------------------+
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=5269 rows=1) (actual time=24.3..24.3 rows=1 loops=1)
-> Filter: (upi_payments.`status` = 'FAILED') (cost=5039 rows=997) (actual time=0.695..23.7 rows=1000 loops=1)
-> Table scan on upi_payments (cost=5039 rows=49827) (actual time=0.403..18.1 rows=50000 loops=1)
+-------------+----------------+
| COLUMN_NAME | histogram_type |
+-------------+----------------+
| status | singleton |
+-------------+----------------+What this code does
- The plain
ANALYZE TABLEfirst refreshes the table's statistics after the big load. With the histogram, the estimate drops from 5,000 to under 1,000, close to the real 1,000. The numbers come from sampling, so yours will differ a little. information_schema.COLUMN_STATISTICSstores histograms as JSON.singletonmeans one bucket per distinct value, used when a column has few values, likeSUCCESSandFAILED.
For a column without an index, MySQL 8.0 can keep a histogram: a count of how the values are spread. ANALYZE TABLE t UPDATE HISTOGRAM ON col builds one; the optimizer then knows that only 2% of payments are FAILED, not a guessed 10%. By default histograms are not updated automatically (MySQL 8.4 and later can add AUTO UPDATE to the statement), so rebuild them after big changes, and remove one with ANALYZE TABLE t DROP HISTOGRAM ON col.
Better statistics do not make a scan faster; they help the optimizer choose the right plan. For a filter that should be fast, the real fix is still an index.
Finding the slow step
A dependent subquery is a subquery that uses a column of the outer row, so it runs once for every outer row. If each run scans a big table, the total work multiplies.
The slowest step is often a scan inside a loop. The first script finds one in a plan; the second shows a sort step and the index that removes it.
Example 4 · The slow step: a scan inside a loop
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE shop_orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, store_id INT NOT NULL, city VARCHAR(20) NOT NULL, order_date DATE NOT NULL, amount DECIMAL(8,2) NOT NULL);
INSERT INTO shop_ordersWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, 1 + (n * 7) % 5000, 1 + n % 200, ELT(1 + n % 5, 'Bengaluru', 'Chennai', 'Delhi', 'Hyderabad', 'Pune'), DATE '2026-01-01' + INTERVAL (n DIV 280) DAY, 99 + (n * 13) % 900FROM seq;
CREATE TABLE vip_customers ( customer_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL);
INSERT INTO vip_customers (customer_id, name)WITH RECURSIVE seq AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100)SELECT n * 37, CONCAT('VIP ', n * 37) FROM seq;
-- Last order date of each of 100 VIP customersEXPLAIN ANALYZESELECT v.customer_id, (SELECT MAX(f.order_date) FROM shop_orders f WHERE f.customer_id = v.customer_id) AS last_orderFROM vip_customers v\G
CREATE INDEX idx_shop_orders_cust_date ON shop_orders (customer_id, order_date);
EXPLAIN ANALYZESELECT v.customer_id, (SELECT MAX(f.order_date) FROM shop_orders f WHERE f.customer_id = v.customer_id) AS last_orderFROM vip_customers v\G*************************** 1. row ***************************
EXPLAIN: -> Covering index scan on v using PRIMARY (cost=10.2 rows=100) (actual time=0.0848..27.7 rows=100 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: max(f.order_date) (cost=1652 rows=1) (actual time=30.8..30.8 rows=1 loops=100)
-> Filter: (f.customer_id = v.customer_id) (cost=500 rows=5000) (actual time=1.74..30.7 rows=10 loops=100)
-> Table scan on f (cost=500 rows=50000) (actual time=0.36..25.7 rows=50000 loops=100)
*************************** 1. row ***************************
EXPLAIN: -> Covering index scan on v using PRIMARY (cost=10.2 rows=100) (actual time=0.167..0.196 rows=100 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: max(f.order_date) (cost=4.3 rows=1) (actual time=0.0217..0.0218 rows=1 loops=100)
-> Covering index lookup on f using idx_shop_orders_cust_date (customer_id = v.customer_id) (cost=2 rows=10) (actual time=0.0126..0.0153 rows=10 loops=100)What this code does
- The subquery is dependent:
Select #2ran withloops=100, once per VIP customer, and every loop did aTable scan on fof 50,000 rows. Each loop took a few tens of milliseconds; multiplied by 100 loops that is seconds, almost all of the query's time: that is the slow step. - With
(customer_id, order_date)each loop becomes aCovering index lookupof 10 entries taking a tiny fraction of a millisecond. The query result is the same; only the step inside the loop changed.
Example 5 · A sort step and the index that removes it
SET SESSION cte_max_recursion_depth = 50000;
CREATE TABLE shop_orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, store_id INT NOT NULL, city VARCHAR(20) NOT NULL, order_date DATE NOT NULL, amount DECIMAL(8,2) NOT NULL);
INSERT INTO shop_ordersWITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 50000)SELECT n, 1 + (n * 7) % 5000, 1 + n % 200, ELT(1 + n % 5, 'Bengaluru', 'Chennai', 'Delhi', 'Hyderabad', 'Pune'), DATE '2026-01-01' + INTERVAL (n DIV 280) DAY, 99 + (n * 13) % 900FROM seq;
-- The 5 biggest ordersEXPLAIN ANALYZESELECT order_id, amount FROM shop_ordersORDER BY amount DESC LIMIT 5\G
CREATE INDEX idx_shop_orders_amount ON shop_orders (amount);
EXPLAIN ANALYZESELECT order_id, amount FROM shop_ordersORDER BY amount DESC LIMIT 5\G*************************** 1. row ***************************
EXPLAIN: -> Limit: 5 row(s) (cost=5000 rows=5) (actual time=29.9..29.9 rows=5 loops=1)
-> Sort: shop_orders.amount DESC, limit input to 5 row(s) per chunk (cost=5000 rows=50000) (actual time=29.9..29.9 rows=5 loops=1)
-> Table scan on shop_orders (cost=5000 rows=50000) (actual time=2.25..18.9 rows=50000 loops=1)
*************************** 1. row ***************************
EXPLAIN: -> Limit: 5 row(s) (cost=75e-6 rows=5) (actual time=0.247..0.25 rows=5 loops=1)
-> Covering index scan on shop_orders using idx_shop_orders_amount (reverse) (cost=75e-6 rows=5) (actual time=0.241..0.244 rows=5 loops=1)What this code does
- Without an index, MySQL scanned all 50,000 orders and ran a
Sortstep to find the top 5; nearly all the time is in the scan and the sort. - With
idx_shop_orders_amount, it reads the index backwards and stops after 5 entries. TheLimitstep tells MySQL to stop early, which only works when the rows arrive already sorted.
To find the slow step, look for the line with the biggest total time, which is the second actual time number times loops. Then read what that step is: Table scan, Sort, a lookup that runs thousands of times. That is where an index or a rewrite pays off.
The classic slow step is a scan inside a loop. A dependent subquery is the usual case: if it scans 50,000 rows each time, 100 outer rows mean 50 lakh rows read. An index on the inner table turns each loop into a lookup. After a fix, run EXPLAIN ANALYZE again and report both numbers: "from 2.6 seconds to about 1 millisecond, because the scan in the subquery became an index lookup". That is the evidence a reviewer or an interviewer wants.
Predict the output
How many loops?
Read the query and count how often the inner SELECT runs.
Round 1 of 2
customers has 10 rows. How many times does the inner SELECT run (loops=?)
Next step
Check your understanding
11 quick questions