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
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
|
drop table if exists t0, t1, t2, t10, t11, t12;
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1(a int, b int);
insert into t1 values (0,0),(1,1),(2,2);
create table t2 as select * from t1;
create table t11(a int, b int);
create table t10 (pk int, a int, primary key(pk));
insert into t10 select a,a from t0;
create table t12 like t10;
insert into t12 select * from t10;
Flattened because of dependency, t10=func(t1)
explain select * from t1 where a in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
select * from t1 where a in (select pk from t10);
a b
0 0
1 1
2 2
A confluent case of dependency
explain select * from t1 where a in (select a from t10 where pk=12);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 const PRIMARY PRIMARY 4 const 1 Using where
select * from t1 where a in (select a from t10 where pk=12);
a b
An empty table inside
explain select * from t1 where a in (select a from t11);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
select * from t1 where a in (select a from t11);
a b
explain select * from t1 where a in (select pk from t10) and b in (select pk from t10);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 Using index
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using index
select * from t1 where a in (select pk from t10) and b in (select pk from t10);
a b
0 0
1 1
2 2
flattening a nested subquery
explain select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 Using index
select * from t1 where a in (select pk from t10 where t10.a in (select pk from t12));
a b
0 0
1 1
2 2
flattening subquery w/ several tables
explain extended select * from t1 where a in (select t10.pk from t10, t12 where t12.pk=t10.a);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00
1 PRIMARY t12 eq_ref PRIMARY PRIMARY 4 test.t10.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10` join `test`.`t12`) join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t12`.`pk` = `test`.`t10`.`a`))
subqueries within outer joins go into ON expr.
explAin extended
select * from t1 left join (t2 A, t2 B) on ( A.A= t1.A And B.A in (select pk from t10));
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY A ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY B ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.B.A 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`A`.`A` AS `A`,`test`.`A`.`B` AS `B`,`test`.`B`.`A` AS `A`,`test`.`B`.`B` AS `B` from `test`.`t1` left join ((`test`.`t10`) join `test`.`t2` `A` join `test`.`t2` `B`) on(((`test`.`A`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`B`.`A` = `test`.`t10`.`pk`))) where 1
t2 should be wrapped into OJ-nest, so we have "t1 LJ (t2 J t10)"
explAin extended
select * from t1 left join t2 on (t2.A= t1.A And t2.A in (select pk from t10));
id select_type tABle type possiBle_keys key key_len ref rows filtered ExtrA
1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t2 ALL NULL NULL NULL NULL 3 100.00
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t2.A 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`A` AS `A`,`test`.`t1`.`B` AS `B`,`test`.`t2`.`A` AS `A`,`test`.`t2`.`B` AS `B` from `test`.`t1` left join ((`test`.`t10`) join `test`.`t2`) on(((`test`.`t2`.`A` = `test`.`t1`.`A`) And 1 And (`test`.`t2`.`A` = `test`.`t10`.`pk`))) where 1
we shouldn't flatten if we're going to get a join of > MAX_TABLES.
explain select * from
t1 s00, t1 s01, t1 s02, t1 s03, t1 s04,t1 s05,t1 s06,t1 s07,t1 s08,t1 s09,
t1 s10, t1 s11, t1 s12, t1 s13, t1 s14,t1 s15,t1 s16,t1 s17,t1 s18,t1 s19,
t1 s20, t1 s21, t1 s22, t1 s23, t1 s24,t1 s25,t1 s26,t1 s27,t1 s28,t1 s29,
t1 s30, t1 s31, t1 s32, t1 s33, t1 s34,t1 s35,t1 s36,t1 s37,t1 s38,t1 s39,
t1 s40, t1 s41, t1 s42, t1 s43, t1 s44,t1 s45,t1 s46,t1 s47,t1 s48,t1 s49
where
s00.a in (
select m00.a from
t1 m00, t1 m01, t1 m02, t1 m03, t1 m04,t1 m05,t1 m06,t1 m07,t1 m08,t1 m09,
t1 m10, t1 m11, t1 m12, t1 m13, t1 m14,t1 m15,t1 m16,t1 m17,t1 m18,t1 m19
);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY s00 ALL NULL NULL NULL NULL 3 Using where
1 PRIMARY s01 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s02 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s03 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s04 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s05 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s06 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s07 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s08 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s09 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s10 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s11 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s12 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s13 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s14 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s15 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s16 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s17 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s18 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s19 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s20 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s21 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s22 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s23 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s24 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s25 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s26 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s27 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s28 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s29 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s30 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s31 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s32 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s33 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s34 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s35 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s36 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s37 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s38 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s39 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s40 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s41 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s42 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s43 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s44 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s45 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s46 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s47 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s48 ALL NULL NULL NULL NULL 3 Using join buffer
1 PRIMARY s49 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m00 ALL NULL NULL NULL NULL 3 Using where
2 DEPENDENT SUBQUERY m01 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m02 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m03 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m04 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m05 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m06 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m07 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m08 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m09 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m10 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m11 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m12 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m13 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m14 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m15 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m16 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m17 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m18 ALL NULL NULL NULL NULL 3 Using join buffer
2 DEPENDENT SUBQUERY m19 ALL NULL NULL NULL NULL 3 Using where; Using join buffer
select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5;
a b a b
0 0 0 0
1 1 1 1
2 2 2 2
prepare s1 from
' select * from
t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t10))
where t1.a < 5';
execute s1;
a b a b
0 0 0 0
1 1 1 1
2 2 2 2
execute s1;
a b a b
0 0 0 0
1 1 1 1
2 2 2 2
insert into t1 select (A.a + 10 * B.a),1 from t0 A, t0 B;
explain extended select * from t1 where a in (select pk from t10 where pk<3);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 103 100.00 Using where
1 PRIMARY t10 eq_ref PRIMARY PRIMARY 4 test.t1.a 1 100.00 Using index
Warnings:
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from (`test`.`t10`) join `test`.`t1` where ((`test`.`t10`.`pk` = `test`.`t1`.`a`) and (`test`.`t1`.`a` < 3))
drop table t0, t1;
drop table t10, t11, t12;
|