Unlock MySQL Performance: How EXPLAIN ANALYZE Reveals Real Execution Costs

We continue the discussion on execution plans (EXPLAIN) and review what the EXPLAIN statement can do.

Analyze whether the SQL uses appropriate indexes, join order, and whether temporary tables are created.

Compare estimated costs, scanned rows, and CPU/IO estimates for each execution step.

View the actual SQL after MySQL's internal transformations.

Although EXPLAIN estimates are based on statistics and may not reflect full data, sometimes a full execution is needed to compare real scan rows and costs.

This article presents three examples to show how EXPLAIN ANALYZE parses query results.

First Example

EXPLAIN

Shows only total cost and scanned rows.

mysql:ytt>explain format=tree select * from y1\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on y1  (cost=100553 rows=1e+6)
1 row in set (0.00 sec)

EXPLAIN ANALYZE

In addition to total cost and rows, it displays actual execution data.

(mysql:ytt)>explain analyze select * from y1\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on y1  (cost=100553 rows=1e+6) (actual time=0.0339..145 rows=1e+6 loops=1)
1 row in set (0.20 sec)

The (actual time=0.0339..145 rows=1e+6 loops=1) part represents the real execution metrics: actual time in milliseconds, rows scanned, and loop count.

actual time=0.0339..145 : execution time, 0.0339 ms per row, 145 ms total.

rows=1e+6 : actual rows scanned, differing from the estimate.

loops=1 : number of loops.

Second Example

First Execution

Joining tables t1 and t2 on ID and aggregating with COUNT.

mysql:ytt>explain analyze select count(*) from t1 a join t2 b using(id)\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=6856.58 rows=10169) (actual time=28.230..28.230 rows=1 loops=1)
    -> Nested loop inner join (cost=5839.68 rows=10169) (actual time=0.057..27.721 rows=10000 loops=1)
        -> Index scan on b using idx_log_date (cost=1049.90 rows=10169) (actual time=0.044..0.579 rows=10000 loops=1)
        -> Single-row index lookup on a using PRIMARY (cost=0.37 rows=1) (actual time=0.002..0.002 rows=1 loops=10000)
1 row in set (0.03 sec)

Result Explanation

Aggregate part : estimated 10169 rows, actual 1 row, execution time 28 ms.

Nested loop inner join : estimated 10169 rows, actual 10000 rows, total time 27 ms.

Table b scanned via index idx_log_date: 2.5 ms, 10 k rows, 1 loop.

Table a scanned via primary key: 0.002 ms per row, 10 k loops.

Second Execution

Adding filter a.r1=10 introduces a filter step.

mysql:ytt>explain analyze select count(*) from t1 a join t2 b using(id) where a.r1 = 10\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (cost=5952.35 rows=508) (actual time=20.465..20.465 rows=1 loops=1)
    -> Nested loop inner join (cost=5901.50 rows=508) (actual time=0.264..0.447 rows=118 loops=1)
        -> Index scan on b using idx_log_date (cost=1049.13 rows=10169) (actual time=0.070..0.242 rows=10000 loops=1)
        -> Filter: (a.r1 = 10) (cost=0.38 rows=0) (actual time=0.002..0.002 rows=0 loops=10000)
            -> Single-row index lookup on a using PRIMARY (cost=0.38 rows=1) (actual time=0.001..0.001 rows=1 loops=10000)
1 row in set (0.02 sec)

The filter step shows that the additional condition avoids a full table scan, using primary‑key lookup followed by filtering.

Third Example

Derived‑table filtering example.

mysql:ytt>desc analyze select * from (select * from t1 where 1 order by r1 desc limit 1000) T where r2 < 1000\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (T.r2 < 1000) (cost=102.91..115.00 rows=333) (actual time=5.524..5.695 rows=1000 loops=1)
    -> Table scan on T (cost=0.01..0.15 rows=1000) (actual time=0.041..0.041 rows=1000 loops=1)
        -> Materialize (cost=102.89..117.87 rows=1000) (actual time=5.521..5.623 rows=1000 loops=1)
            -> Limit: 1000 rows (cost=2.87 rows=1000) (actual time=0.552..0.597 rows=1000 loops=1)
                -> Index scan on t1 using idx_r1 (reverse) (cost=2.87 rows=1000) (actual time=0.551..0.532 rows=1000 loops=1)
1 row in set (0.00 sec)

Explanation: the derived table is materialized, scanning 1000 rows via reverse index, then applying the filter T.r2 < 1000. Estimated rows (333) differ greatly from actual rows (1000), with execution time about 5.7 ms.

When Not to Use EXPLAIN ANALYZE

EXPLAIN ANALYZE actually runs the query, so for very expensive statements it may take a long time and is not recommended. For example, a deeply nested query like

select count(*) from t1 where r1 in (select r1 from t1 where r1 in (select r1 from t1))

would be costly.