1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
drop table if exists t0, t1;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int);
insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C;
alter table t1 add b int, add c int, add filler char(32);
update t1 set b=a, c=a, filler='fooo';
alter table t1 add key(a), add key(b);
show explain for 2*1000*1000*1000;
ERROR HY000: Unknown thread id: 2000000000
show explain for (select max(a) from t0);
ERROR 42000: This version of MariaDB doesn't yet support 'Usage of subqueries or stored function calls as part of this statement'
show explain for $thr2;
ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
show explain for $thr1;
ERROR HY000: Error when executing command SHOW EXPLAIN: Target is not running EXPLAINable command
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';
select count(*) from t1 where a < 100000;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index
count(*)
1000
select max(c) from t1 where a < 10;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 NULL 10 Using index condition
max(c)
9
# We can catch EXPLAIN, too.
set @show_expl_tmp= @@optimizer_switch;
set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on';
explain select max(c) from t1 where a < 10;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan
set optimizer_switch= @show_expl_tmp;
# UNION, first branch
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';
explain select a from t0 A union select a+1 from t0 B;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10
2 UNION B ALL NULL NULL NULL NULL 10
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10
2 UNION B ALL NULL NULL NULL NULL 10
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
# UNION, second branch
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';
explain select a from t0 A union select a+1 from t0 B;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10
2 UNION B ALL NULL NULL NULL NULL 10
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10
2 UNION B ALL NULL NULL NULL NULL 10
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
# Uncorrelated subquery, select
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';
select a, (select max(a) from t0 B) from t0 A where a<1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
2 SUBQUERY B ALL NULL NULL NULL NULL 10
a (select max(a) from t0 B)
0 9
# Uncorrelated subquery, explain
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';
explain select a, (select max(a) from t0 B) from t0 A where a<1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
2 SUBQUERY B ALL NULL NULL NULL NULL 10
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where
2 SUBQUERY B ALL NULL NULL NULL NULL 10
# correlated subquery, select
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
a (select max(a) from t0 b where b.a+a.a<10)
0 9
# correlated subquery, explain
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_start';
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
a (select max(a) from t0 b where b.a+a.a<10)
0 9
# correlated subquery, select, while inside the subquery
set @show_explain_probe_select_id=2;
set debug_dbug='d,show_explain_probe_join_exec_start';
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
a (select max(a) from t0 b where b.a+a.a<10)
0 9
# correlated subquery, explain, while inside the subquery
set @show_explain_probe_select_id=2;
set debug_dbug='d,show_explain_probe_join_exec_start';
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where
2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where
a (select max(a) from t0 b where b.a+a.a<10)
0 9
# correlated subquery, explain, while inside the subquery
set @show_explain_probe_select_id=1;
set debug_dbug='d,show_explain_probe_join_exec_end';
select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1;
show explain for $thr2;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted
a (select max(a) from t0 b where b.a+a.a<10)
0 9
drop table t0,t1;
|
