Description:
EXPLAIN ANALYZE does not compute expressions in the select list. If these expressions are expensive (e.g. subqueries), then EXPLAIN ANALYZE output will not be an accurate description of what happens when one runs the corresponding select.
How to repeat:
create table ten(a int primary key);
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table one_k(a int primary key);
insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C;
create table one_m (a int);
insert into one_m select A.a+1000*B.a from one_k A, one_k B;
# Run a query with an expensive correlated subquery in the select list:
mysql> select a, (select count(*) from one_m where one_m.a< ten.a) from ten;
+------+---------------------------------------------------+
| a | (select count(*) from one_m where one_m.a< ten.a) |
+------+---------------------------------------------------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+------+---------------------------------------------------+
10 rows in set (1 min 54.57 sec)
mysql> explain analyze select a, (select count(*) from one_m where one_m.a< ten.a) from ten\G
*************************** 1. row ***************************
EXPLAIN: -> Index scan on ten using PRIMARY (cost=1.25 rows=10) (actual time=0.274..0.424 rows=10 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: count(0) (never executed)
-> Filter: (one_m.a < ten.a) (cost=33787.30 rows=332906) (never executed)
-> Table scan on one_m (cost=33787.30 rows=998819) (never executed)
1 row in set, 1 warning (0.01 sec)
EXPLAIN ANALYZE runs instantly and shows that the subquery is never executed.
Description: EXPLAIN ANALYZE does not compute expressions in the select list. If these expressions are expensive (e.g. subqueries), then EXPLAIN ANALYZE output will not be an accurate description of what happens when one runs the corresponding select. How to repeat: create table ten(a int primary key); insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table one_k(a int primary key); insert into one_k select A.a + B.a* 10 + C.a * 100 from ten A, ten B, ten C; create table one_m (a int); insert into one_m select A.a+1000*B.a from one_k A, one_k B; # Run a query with an expensive correlated subquery in the select list: mysql> select a, (select count(*) from one_m where one_m.a< ten.a) from ten; +------+---------------------------------------------------+ | a | (select count(*) from one_m where one_m.a< ten.a) | +------+---------------------------------------------------+ | 0 | 0 | | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | | 8 | 8 | | 9 | 9 | +------+---------------------------------------------------+ 10 rows in set (1 min 54.57 sec) mysql> explain analyze select a, (select count(*) from one_m where one_m.a< ten.a) from ten\G *************************** 1. row *************************** EXPLAIN: -> Index scan on ten using PRIMARY (cost=1.25 rows=10) (actual time=0.274..0.424 rows=10 loops=1) -> Select #2 (subquery in projection; dependent) -> Aggregate: count(0) (never executed) -> Filter: (one_m.a < ten.a) (cost=33787.30 rows=332906) (never executed) -> Table scan on one_m (cost=33787.30 rows=998819) (never executed) 1 row in set, 1 warning (0.01 sec) EXPLAIN ANALYZE runs instantly and shows that the subquery is never executed.