MySQL EXPLAIN ANALYZE: Real Execution Time

EXPLAIN shows what MySQL expects to do. EXPLAIN ANALYZE (MySQL 8.0.18 and later) runs the query, and shows what actually happened: the real time, the real number of rows and the number of loops for each step.

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;

CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, city VARCHAR(20) NOT NULL);
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, total DECIMAL(8,2) NOT NULL, created_at DATETIME NOT NULL);
INSERT INTO customers (name, city) WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 2000) SELECT CONCAT('Customer ', i), CONCAT('City', MOD(i, 40)) FROM n;
INSERT INTO orders (customer_id, total, created_at)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 30000)
SELECT 1 + MOD(i * 7, 2000), 10 + MOD(i * 13, 900), '2026-01-01 00:00:00' + INTERVAL i * 3 MINUTE FROM n;
ANALYZE TABLE customers, orders;

EXPLAIN ANALYZE
SELECT c.city, COUNT(*) AS orders, ROUND(SUM(o.total), 2) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-02-01'
GROUP BY c.city;

Output

OUTPUT

+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| learn.customers | analyze | status   | OK       |
| learn.orders    | analyze | status   | OK       |
+-----------------+---------+----------+----------+
2 rows in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on <temporary>  (actual time=56..56 rows=40 loops=1)
    -> Aggregate using temporary table  (actual time=56..56 rows=40 loops=1)
        -> Nested loop inner join  (cost=6459 rows=9899) (actual time=4.64..41 rows=15121 loops=1)
            -> Filter: (o.created_at >= TIMESTAMP'2026-02-01 00:00:00')  (cost=2994 rows=9899) (actual time=4.62..11.8 rows=15121 loops=1)
                -> Table scan on o  (cost=2994 rows=29699) (actual time=0.476..9.07 rows=30000 loops=1)
            -> Single-row index lookup on c using PRIMARY (id=o.customer_id)  (cost=0.25 rows=1) (actual time=0.00174..0.00177 rows=1 loops=15121)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

(The numbers differ on every run, so this output is not compared.) The output is a tree, read from the innermost (most indented) line outwards. Each line has two groups of numbers:

Text

PLAIN

1
2

-> Table scan on o  (cost=3036 rows=29893) (actual time=0.073..15.4 rows=30000 loops=1)
                     ^^^ the estimate ^^^    ^^^^^^^^^^^ the reality ^^^^^^^^^^^^^^^^^^^

Item	Meaning
`cost=...`	The optimizer’s cost estimate (arbitrary units)
`rows=...` (first)	Estimated rows produced
`actual time=a..b`	`a` = milliseconds until the first row, `b` = milliseconds until all rows (per loop)
`rows=...` (second)	Actual rows produced (per loop)
`loops=n`	How many times this step ran (once for each row of the outer step in a nested loop join)

Total time of a step

The time shown is per loop. Multiply by loops for the total:

Text

PLAIN

actual time=0.01..0.02 rows=15 loops=2000   ->   about 2000 x 0.02 ms = 40 ms in total

A step that looks fast but runs 2,000 times can be the main cost.

Compare estimate with reality

The most useful habit: compare the first rows= (estimate) with the second (actual). A big difference, such as an estimate of 10 and an actual of 100,000, means the optimizer was wrong. It may choose a bad plan because of that. The causes: stale statistics (ANALYZE TABLE), skewed data (a histogram can help), or correlated columns the optimizer does not understand.

Find the slow step

Look for the line with the highest actual total time, usually a table scan, a big sort, a temporary table or a nested loop with many loops. Everything above it in the tree includes the time of the lines below. A line’s own cost is its time minus the time of its children.

An example: with and without an index

query.sql

SQL

1
2
3

EXPLAIN ANALYZE SELECT id FROM orders WHERE customer_id = 42;
CREATE INDEX idx_customer ON orders (customer_id);
EXPLAIN ANALYZE SELECT id FROM orders WHERE customer_id = 42;

Output

OUTPUT

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (orders.customer_id = 42)  (cost=2994 rows=2970) (actual time=0.421..6.59 rows=15 loops=1)
    -> Table scan on orders  (cost=2994 rows=29699) (actual time=0.319..5.36 rows=30000 loops=1)
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on orders using idx_customer (customer_id=42)  (cost=2.48 rows=15) (actual time=0.0095..0.0122 rows=15 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Before the index: a table scan of all 30,000 rows, with a filter that keeps about 15. After: an index lookup that reads exactly those rows. The actual time drops by a large factor, and the rows before the filter drops from 30,000 to about 15.

Things to remember

It runs the query. An EXPLAIN ANALYZE on a SELECT that takes a minute takes a minute. For UPDATE/DELETE it is supported only for SELECT statements (MySQL 8.0.18) and since 8.0.19 for multi-table UPDATE/DELETE. For data-changing statements, test on a copy.
Timing has some overhead for the measurement. Use it for relative comparison, not as an exact benchmark.
The first run may be slower because data must be read from disk into the buffer pool. Run twice, and look at the second run for a “warm” result.
It always prints in the tree format.

EXPLAIN ANALYZE vs the slow query log

The slow query log tells you which statements are slow. EXPLAIN ANALYZE tells you where inside one statement the time goes. Use them together (Slow Query Log).

FORMAT=TREE is the same, without running

EXPLAIN FORMAT=TREE shows the same tree with estimates only. EXPLAIN ANALYZE adds the actual figures.

lightbulb

A quick method

Run EXPLAIN ANALYZE, find the line with the biggest actual time x loops, and look at its estimate. If the estimate is far from the actual rows, refresh statistics. If the step reads too many rows, look for a missing or unsuitable index.