| blob | 1ebc94dff5d78da1002a9c168afe95b2d961be27 |
1 #---------------- Index merge test 1 -------------------------------------------
2 SET SESSION STORAGE_ENGINE = MyISAM;
3 drop table if exists t0, t1, t2, t3, t4;
4 create table t0
5 (
6 key1 int not null,
7 INDEX i1(key1)
8 );
9 alter table t0 add key2 int not null, add index i2(key2);
10 alter table t0 add key3 int not null, add index i3(key3);
11 alter table t0 add key4 int not null, add index i4(key4);
12 alter table t0 add key5 int not null, add index i5(key5);
13 alter table t0 add key6 int not null, add index i6(key6);
14 alter table t0 add key7 int not null, add index i7(key7);
15 alter table t0 add key8 int not null, add index i8(key8);
16 update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
17 analyze table t0;
18 Table Op Msg_type Msg_text
19 test.t0 analyze status OK
20 explain select * from t0 where key1 < 3 or key1 > 1020;
21 id select_type table type possible_keys key key_len ref rows Extra
22 1 SIMPLE t0 range i1 i1 4 NULL 78 Using where
23 explain
24 select * from t0 where key1 < 3 or key2 > 1020;
25 id select_type table type possible_keys key key_len ref rows Extra
26 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 45 Using sort_union(i1,i2); Using where
27 select * from t0 where key1 < 3 or key2 > 1020;
28 key1 key2 key3 key4 key5 key6 key7 key8
29 1 1 1 1 1 1 1 1023
30 2 2 2 2 2 2 2 1022
31 1021 1021 1021 1021 1021 1021 1021 3
32 1022 1022 1022 1022 1022 1022 1022 2
33 1023 1023 1023 1023 1023 1023 1023 1
34 1024 1024 1024 1024 1024 1024 1024 0
35 explain select * from t0 where key1 < 3 or key2 <4;
36 id select_type table type possible_keys key key_len ref rows Extra
37 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where
38 explain
39 select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
40 id select_type table type possible_keys key key_len ref rows Extra
41 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 11 Using sort_union(i1,i2); Using where
42 select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
43 key1 key2 key3 key4 key5 key6 key7 key8
44 31 31 31 31 31 31 31 993
45 32 32 32 32 32 32 32 992
46 33 33 33 33 33 33 33 991
47 34 34 34 34 34 34 34 990
48 35 35 35 35 35 35 35 989
49 36 36 36 36 36 36 36 988
50 37 37 37 37 37 37 37 987
51 38 38 38 38 38 38 38 986
52 39 39 39 39 39 39 39 985
53 explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
54 id select_type table type possible_keys key key_len ref rows Extra
55 1 SIMPLE t0 ALL i1 NULL NULL NULL 1024 Using where
56 explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
57 id select_type table type possible_keys key key_len ref rows Extra
58 1 SIMPLE t0 ref i1,i2,i3 i3 4 const 1 Using where
59 explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
60 id select_type table type possible_keys key key_len ref rows Extra
61 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where
62 explain select * from t0 where (key1 > 1 or key2 > 2);
63 id select_type table type possible_keys key key_len ref rows Extra
64 1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
65 explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
66 id select_type table type possible_keys key key_len ref rows Extra
67 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 1024 Using sort_union(i1,i2); Using where
68 explain
69 select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
70 (key1>10 and key1<12) or (key2>100 and key2<110);
71 id select_type table type possible_keys key key_len ref rows Extra
72 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 17 Using sort_union(i1,i2); Using where
73 explain select * from t0 where key2 = 45 or key1 <=> null;
74 id select_type table type possible_keys key key_len ref rows Extra
75 1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where
76 explain select * from t0 where key2 = 45 or key1 is not null;
77 id select_type table type possible_keys key key_len ref rows Extra
78 1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
79 explain select * from t0 where key2 = 45 or key1 is null;
80 id select_type table type possible_keys key key_len ref rows Extra
81 1 SIMPLE t0 ref i2 i2 4 const 1
82 explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
83 id select_type table type possible_keys key key_len ref rows Extra
84 1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
85 explain select * from t0 where key2=10 or key3=3 or key4 is null;
86 id select_type table type possible_keys key key_len ref rows Extra
87 1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
88 explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
89 (key3=10) or (key4 <=> null);
90 id select_type table type possible_keys key key_len ref rows Extra
91 1 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL 6 Using sort_union(i2,i3); Using where
92 explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
93 (key3=10) or (key4 <=> null);
94 id select_type table type possible_keys key key_len ref rows Extra
95 1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 6 Using sort_union(i1,i3); Using where
96 explain select * from t0 where
97 (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
98 id select_type table type possible_keys key key_len ref rows Extra
99 1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where
100 explain
101 select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
102 id select_type table type possible_keys key key_len ref rows Extra
103 1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where
104 select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
105 key1 key2 key3 key4 key5 key6 key7 key8
106 1 1 1 1 1 1 1 1023
107 2 2 2 2 2 2 2 1022
108 3 3 3 3 3 3 3 1021
109 4 4 4 4 4 4 4 1020
110 5 5 5 5 5 5 5 1019
111 explain select * from t0 where
112 (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
113 id select_type table type possible_keys key key_len ref rows Extra
114 1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where
115 explain select * from t0 where
116 (key1 < 3 or key2 < 3) and (key3 < 100);
117 id select_type table type possible_keys key key_len ref rows Extra
118 1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using where
119 explain select * from t0 where
120 (key1 < 3 or key2 < 3) and (key3 < 1000);
121 id select_type table type possible_keys key key_len ref rows Extra
122 1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where
123 explain select * from t0 where
124 ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
125 or
126 key2 > 5;
127 id select_type table type possible_keys key key_len ref rows Extra
128 1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where
129 explain select * from t0 where
130 ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
131 or
132 key1 < 7;
133 id select_type table type possible_keys key key_len ref rows Extra
134 1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
135 select * from t0 where
136 ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
137 or
138 key1 < 7;
139 key1 key2 key3 key4 key5 key6 key7 key8
140 1 1 1 1 1 1 1 1023
141 2 2 2 2 2 2 2 1022
142 3 3 3 3 3 3 3 1021
143 4 4 4 4 4 4 4 1020
144 5 5 5 5 5 5 5 1019
145 6 6 6 6 6 6 6 1018
146 explain select * from t0 where
147 ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
148 or
149 ((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
150 id select_type table type possible_keys key key_len ref rows Extra
151 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL 19 Using sort_union(i1,i2,i5,i6); Using where
152 explain select * from t0 where
153 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
154 or
155 ((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
156 id select_type table type possible_keys key key_len ref rows Extra
157 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 20 Using sort_union(i3,i5,i7,i8); Using where
158 explain select * from t0 where
159 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
160 or
161 ((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
162 id select_type table type possible_keys key key_len ref rows Extra
163 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
164 explain select * from t0 where
165 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
166 or
167 (((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
168 id select_type table type possible_keys key key_len ref rows Extra
169 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
170 explain select * from t0 where
171 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
172 or
173 ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
174 id select_type table type possible_keys key key_len ref rows Extra
175 1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
176 explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
177 ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
178 or
179 ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
180 id select_type table type possible_keys key key_len ref rows Extra
181 1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where
182 select * from t0 where key1 < 5 or key8 < 4 order by key1;
183 key1 key2 key3 key4 key5 key6 key7 key8
184 1 1 1 1 1 1 1 1023
185 2 2 2 2 2 2 2 1022
186 3 3 3 3 3 3 3 1021
187 4 4 4 4 4 4 4 1020
188 1021 1021 1021 1021 1021 1021 1021 3
189 1022 1022 1022 1022 1022 1022 1022 2
190 1023 1023 1023 1023 1023 1023 1023 1
191 1024 1024 1024 1024 1024 1024 1024 0
192 explain
193 select * from t0 where key1 < 5 or key8 < 4 order by key1;
194 id select_type table type possible_keys key key_len ref rows Extra
195 1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 9 Using sort_union(i1,i8); Using where; Using filesort
196 create table t2 like t0;
197 insert into t2 select * from t0;
198 alter table t2 add index i1_3(key1, key3);
199 alter table t2 add index i2_3(key2, key3);
200 alter table t2 drop index i1;
201 alter table t2 drop index i2;
202 alter table t2 add index i321(key3, key2, key1);
203 explain select key3 from t2 where key1 = 100 or key2 = 100;
204 id select_type table type possible_keys key key_len ref rows Extra
205 1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 2 Using sort_union(i1_3,i2_3); Using where
206 explain select key3 from t2 where key1 <100 or key2 < 100;
207 id select_type table type possible_keys key key_len ref rows Extra
208 1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL 1024 Using where; Using index
209 explain select key7 from t2 where key1 <100 or key2 < 100;
210 id select_type table type possible_keys key key_len ref rows Extra
211 1 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL 1024 Using where
212 create table t4 (
213 key1a int not null,
214 key1b int not null,
215 key2 int not null,
216 key2_1 int not null,
217 key2_2 int not null,
218 key3 int not null,
219 index i1a (key1a, key1b),
220 index i1b (key1b, key1a),
221 index i2_1(key2, key2_1),
222 index i2_2(key2, key2_1)
223 );
224 insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
225 select * from t4 where key1a = 3 or key1b = 4;
226 key1a key1b key2 key2_1 key2_2 key3
227 3 3 0 3 3 3
228 4 4 0 4 4 4
229 explain select * from t4 where key1a = 3 or key1b = 4;
230 id select_type table type possible_keys key key_len ref rows Extra
231 1 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using sort_union(i1a,i1b); Using where
232 explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
233 id select_type table type possible_keys key key_len ref rows Extra
234 1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where
235 explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
236 id select_type table type possible_keys key key_len ref rows Extra
237 1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where
238 explain select * from t4 where key2_1 = 1 or key2_2 = 5;
239 id select_type table type possible_keys key key_len ref rows Extra
240 1 SIMPLE t4 ALL NULL NULL NULL NULL 1024 Using where
241 create table t1 like t0;
242 insert into t1 select * from t0;
243 explain select * from t0 left join t1 on (t0.key1=t1.key1)
244 where t0.key1=3 or t0.key2=4;
245 id select_type table type possible_keys key key_len ref rows Extra
246 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
247 1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
248 select * from t0 left join t1 on (t0.key1=t1.key1)
249 where t0.key1=3 or t0.key2=4;
250 key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8
251 3 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021
252 4 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020
253 explain
254 select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
255 id select_type table type possible_keys key key_len ref rows Extra
256 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
257 1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
258 explain
259 select * from t0,t1 where (t0.key1=t1.key1) and
260 (t0.key1=3 or t0.key2=4) and t1.key1<200;
261 id select_type table type possible_keys key key_len ref rows Extra
262 1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
263 1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
264 explain
265 select * from t0,t1 where (t0.key1=t1.key1) and
266 (t0.key1=3 or t0.key2<4) and t1.key1=2;
267 id select_type table type possible_keys key key_len ref rows Extra
268 1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where
269 1 SIMPLE t1 ref i1 i1 4 const 1
270 explain select * from t0,t1 where t0.key1 = 5 and
271 (t1.key1 = t0.key1 or t1.key8 = t0.key1);
272 id select_type table type possible_keys key key_len ref rows Extra
273 1 SIMPLE t0 ref i1 i1 4 const 1
274 1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer
275 explain select * from t0,t1 where t0.key1 < 3 and
276 (t1.key1 = t0.key1 or t1.key8 = t0.key1);
277 id select_type table type possible_keys key key_len ref rows Extra
278 1 SIMPLE t0 range i1 i1 4 NULL 3 Using where
279 1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81)
280 explain select * from t1 where key1=3 or key2=4
281 union select * from t1 where key1<4 or key3=5;
282 id select_type table type possible_keys key key_len ref rows Extra
283 1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
284 2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where
285 NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
286 explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
287 id select_type table type possible_keys key key_len ref rows Extra
288 1 PRIMARY <derived2> system NULL NULL NULL NULL 1
289 2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where; Using index
290 create table t3 like t0;
291 insert into t3 select * from t0;
292 alter table t3 add key9 int not null, add index i9(key9);
293 alter table t3 add keyA int not null, add index iA(keyA);
294 alter table t3 add keyB int not null, add index iB(keyB);
295 alter table t3 add keyC int not null, add index iC(keyC);
296 update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
297 explain select * from t3 where
298 key1=1 or key2=2 or key3=3 or key4=4 or
299 key5=5 or key6=6 or key7=7 or key8=8 or
300 key9=9 or keyA=10 or keyB=11 or keyC=12;
301 id select_type table type possible_keys key key_len ref rows Extra
302 1 SIMPLE t3 index_merge i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC 4,4,4,4,4,4,4,4,4,4,4,4 NULL 12 Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where
303 select * from t3 where
304 key1=1 or key2=2 or key3=3 or key4=4 or
305 key5=5 or key6=6 or key7=7 or key8=8 or
306 key9=9 or keyA=10 or keyB=11 or keyC=12;
307 key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC
308 1 1 1 1 1 1 1 1023 1 1 1 1
309 2 2 2 2 2 2 2 1022 2 2 2 2
310 3 3 3 3 3 3 3 1021 3 3 3 3
311 4 4 4 4 4 4 4 1020 4 4 4 4
312 5 5 5 5 5 5 5 1019 5 5 5 5
313 6 6 6 6 6 6 6 1018 6 6 6 6
314 7 7 7 7 7 7 7 1017 7 7 7 7
315 9 9 9 9 9 9 9 1015 9 9 9 9
316 10 10 10 10 10 10 10 1014 10 10 10 10
317 11 11 11 11 11 11 11 1013 11 11 11 11
318 12 12 12 12 12 12 12 1012 12 12 12 12
319 1016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016
320 explain select * from t0 where key1 < 3 or key2 < 4;
321 id select_type table type possible_keys key key_len ref rows Extra
322 1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where
323 select * from t0 where key1 < 3 or key2 < 4;
324 key1 key2 key3 key4 key5 key6 key7 key8
325 1 1 1 1 1 1 1 1023
326 2 2 2 2 2 2 2 1022
327 3 3 3 3 3 3 3 1021
328 update t0 set key8=123 where key1 < 3 or key2 < 4;
329 select * from t0 where key1 < 3 or key2 < 4;
330 key1 key2 key3 key4 key5 key6 key7 key8
331 1 1 1 1 1 1 1 123
332 2 2 2 2 2 2 2 123
333 3 3 3 3 3 3 3 123
334 delete from t0 where key1 < 3 or key2 < 4;
335 select * from t0 where key1 < 3 or key2 < 4;
336 key1 key2 key3 key4 key5 key6 key7 key8
337 select count(*) from t0;
338 count(*)
339 1021
340 drop table t4;
341 create table t4 (a int);
342 insert into t4 values (1),(4),(3);
343 set @save_join_buffer_size=@@join_buffer_size;
344 set join_buffer_size= 4000;
345 Warnings:
346 Warning 1292 Truncated incorrect join_buffer_size value: '4000'
347 explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
348 from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
349 where (A.key1 < 500000 or A.key2 < 3)
350 and (B.key1 < 500000 or B.key2 < 3);
351 id select_type table type possible_keys key key_len ref rows Extra
352 1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where
353 1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where; Using join buffer
354 select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
355 from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
356 where (A.key1 < 500000 or A.key2 < 3)
357 and (B.key1 < 500000 or B.key2 < 3);
358 max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
359 10240
360 update t0 set key1=1;
361 explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
362 from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
363 where (A.key1 = 1 or A.key2 = 1)
364 and (B.key1 = 1 or B.key2 = 1);
365 id select_type table type possible_keys key key_len ref rows Extra
366 1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where
367 1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where; Using join buffer
368 select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
369 from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
370 where (A.key1 = 1 or A.key2 = 1)
371 and (B.key1 = 1 or B.key2 = 1);
372 max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
373 8194
374 alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
375 update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
376 explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
377 from t0 as A, t0 as B
378 where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
379 and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
380 id select_type table type possible_keys key key_len ref rows Extra
381 1 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where
382 1 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join buffer
383 select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
384 from t0 as A, t0 as B
385 where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1)
386 and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1);
387 max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
388 8186
389 set join_buffer_size= @save_join_buffer_size;
390 drop table t0, t1, t2, t3, t4;
391 CREATE TABLE t1 (
392 cola char(3) not null, colb char(3) not null, filler char(200),
393 key(cola), key(colb)
394 );
395 INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
396 OPTIMIZE TABLE t1;
397 Table Op Msg_type Msg_text
398 test.t1 optimize status OK
399 select count(*) from t1;
400 count(*)
401 8704
402 explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
403 id select_type table type possible_keys key key_len ref rows Extra
404 1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
405 explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
406 id select_type table type possible_keys key key_len ref rows Extra
407 1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where
408 drop table t1;
409 create table t0 (a int);
410 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
411 create table t1 (
412 a int, b int,
413 filler1 char(200), filler2 char(200),
414 key(a),key(b)
415 );
416 insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C;
417 create table t2 like t1;
418 create table t3 (
419 a int, b int,
420 filler1 char(200), filler2 char(200),
421 key(a),key(b)
422 ) engine=merge union=(t1,t2);
423 explain select * from t1 where a=1 and b=1;
424 id select_type table type possible_keys key key_len ref rows Extra
425 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where
426 explain select * from t3 where a=1 and b=1;
427 id select_type table type possible_keys key key_len ref rows Extra
428 1 SIMPLE t3 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where
429 drop table t3;
430 drop table t0, t1, t2;
431 CREATE TABLE t1(a INT);
432 INSERT INTO t1 VALUES(1);
433 CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
434 INSERT INTO t2(a,b) VALUES
435 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
436 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
437 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
438 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
439 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
440 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
441 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
442 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
443 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
444 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
445 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
446 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
447 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
448 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
449 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
450 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
451 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
452 (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
453 (1,2);
454 LOCK TABLES t1 WRITE, t2 WRITE;
455 INSERT INTO t2(a,b) VALUES(1,2);
456 SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
457 a
458 1
459 1
460 UNLOCK TABLES;
461 DROP TABLE t1, t2;
462 CREATE TABLE `t1` (
463 `a` int(11) DEFAULT NULL,
464 `filler` char(200) DEFAULT NULL,
465 `b` int(11) DEFAULT NULL,
466 KEY `a` (`a`),
467 KEY `b` (`b`)
468 ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
469 insert into t1 values
470 (0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3),
471 (4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7),
472 (8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1),
473 (2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5),
474 (6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9),
475 (10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
476 (14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17),
477 (18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0),
478 (4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4);
479 create table t2(
480 `a` int(11) DEFAULT NULL,
481 `filler` char(200) DEFAULT NULL,
482 `b` int(11) DEFAULT NULL,
483 KEY USING BTREE (`a`),
484 KEY USING BTREE (`b`)
485 ) ENGINE=MEMORY DEFAULT CHARSET=latin1;
486 insert into t2 select * from t1;
487 must use sort-union rather than union:
488 explain select * from t1 where a=4 or b=4;
489 id select_type table type possible_keys key key_len ref rows Extra
490 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where
491 select * from t1 where a=4 or b=4;
492 a filler b
493 4 4 0
494 4 5 0
495 4 filler 4
496 4 filler 4
497 4 qq 5
498 4 zz 4
499 5 qq 4
500 select * from t1 ignore index(a,b) where a=4 or b=4;
501 a filler b
502 4 4 0
503 4 5 0
504 4 filler 4
505 4 filler 4
506 4 qq 5
507 4 zz 4
508 5 qq 4
509 must use union, not sort-union:
510 explain select * from t2 where a=4 or b=4;
511 id select_type table type possible_keys key key_len ref rows Extra
512 1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where
513 select * from t2 where a=4 or b=4;
514 a filler b
515 4 4 0
516 4 5 0
517 4 filler 4
518 4 filler 4
519 4 qq 5
520 4 zz 4
521 5 qq 4
522 drop table t1, t2;
523 CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'),
524 KEY b(b), KEY a(a));
525 INSERT INTO t1 VALUES ('y',''), ('z','');
526 SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR
527 (a='pure-S') OR (a='DE80337a') OR (a='DE80799');
528 b a
529 y
530 z
531 DROP TABLE t1;
532 #
533 # BUG#40974: Incorrect query results when using clause evaluated using range check
534 #
535 create table t0 (a int);
536 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
537 create table t1 (a int);
538 insert into t1 values (1),(2);
539 create table t2(a int, b int);
540 insert into t2 values (1,1), (2, 1000);
541 create table t3 (a int, b int, filler char(100), key(a), key(b));
542 insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C;
543 insert into t3 values (1,1,'data');
544 insert into t3 values (1,1,'data');
545 The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
546 explain select * from t1
547 where exists (select 1 from t2, t3
548 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
549 id select_type table type possible_keys key key_len ref rows Extra
550 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
551 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
552 2 DEPENDENT SUBQUERY t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3)
553 select * from t1
554 where exists (select 1 from t2, t3
555 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1));
556 a
557 1
558 2
559 drop table t0, t1, t2, t3;
560 #
561 # BUG#44810: index merge and order by with low sort_buffer_size
562 # crashes server!
563 #
564 CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B));
565 INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128));
566 INSERT INTO t1 SELECT * FROM t1;
567 INSERT INTO t1 SELECT * FROM t1;
568 INSERT INTO t1 SELECT * FROM t1;
569 INSERT INTO t1 SELECT * FROM t1;
570 INSERT INTO t1 SELECT * FROM t1;
571 INSERT INTO t1 SELECT * FROM t1;
572 SET SESSION sort_buffer_size=1;
573 Warnings:
574 Warning 1292 Truncated incorrect sort_buffer_size value: '1'
575 EXPLAIN
576 SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
577 ORDER BY a,b;
578 id select_type table type possible_keys key key_len ref rows Extra
579 1 SIMPLE t1 index_merge a,b a,b 131,131 NULL 64 Using sort_union(a,b); Using where; Using filesort
580 SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%'
581 ORDER BY a,b;
582 SET SESSION sort_buffer_size=DEFAULT;
583 DROP TABLE t1;
584 End of 5.0 tests
585 #---------------- ROR-index_merge tests -----------------------
586 SET SESSION STORAGE_ENGINE = MyISAM;
587 drop table if exists t0,t1,t2;
588 create table t1
589 (
590 /* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
591 st_a int not null default 0,
592 swt1a int not null default 0,
593 swt2a int not null default 0,
594 st_b int not null default 0,
595 swt1b int not null default 0,
596 swt2b int not null default 0,
597 /* fields/keys for row retrieval tests */
598 key1 int,
599 key2 int,
600 key3 int,
601 key4 int,
602 /* make rows much bigger then keys */
603 filler1 char (200),
604 filler2 char (200),
605 filler3 char (200),
606 filler4 char (200),
607 filler5 char (200),
608 filler6 char (200),
609 /* order of keys is important */
610 key sta_swt12a(st_a,swt1a,swt2a),
611 key sta_swt1a(st_a,swt1a),
612 key sta_swt2a(st_a,swt2a),
613 key sta_swt21a(st_a,swt2a,swt1a),
614 key st_a(st_a),
615 key stb_swt1a_2b(st_b,swt1b,swt2a),
616 key stb_swt1b(st_b,swt1b),
617 key st_b(st_b),
618 key(key1),
619 key(key2),
620 key(key3),
621 key(key4)
622 ) ;
623 create table t0 as select * from t1;
624 # Printing of many insert into t0 values (....) disabled.
625 alter table t1 disable keys;
626 # Printing of many insert into t1 select .... from t0 disabled.
627 # Printing of many insert into t1 (...) values (....) disabled.
628 alter table t1 enable keys;
629 select count(*) from t1;
630 count(*)
631 64801
632 explain select key1,key2 from t1 where key1=100 and key2=100;
633 id select_type table type possible_keys key key_len ref rows Extra
634 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index
635 select key1,key2 from t1 where key1=100 and key2=100;
636 key1 key2
637 100 100
638 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
639 id select_type table type possible_keys key key_len ref rows Extra
640 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
641 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
642 key1 key2 key3 key4 filler1
643 100 100 100 100 key1-key2-key3-key4
644 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
645 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
646 explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
647 id select_type table type possible_keys key key_len ref rows Extra
648 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where
649 select key1,key2,filler1 from t1 where key1=100 and key2=100;
650 key1 key2 filler1
651 100 100 key1-key2-key3-key4
652 100 100 key1-key2
653 explain select key1,key2 from t1 where key1=100 and key2=100;
654 id select_type table type possible_keys key key_len ref rows Extra
655 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index
656 select key1,key2 from t1 where key1=100 and key2=100;
657 key1 key2
658 100 100
659 100 100
660 explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
661 id select_type table type possible_keys key key_len ref rows Extra
662 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
663 select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
664 key1 key2 key3 key4
665 100 100 100 100
666 100 100 -1 -1
667 -1 -1 100 100
668 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
669 id select_type table type possible_keys key key_len ref rows Extra
670 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
671 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
672 key1 key2 key3 key4 filler1
673 100 100 100 100 key1-key2-key3-key4
674 100 100 -1 -1 key1-key2
675 -1 -1 100 100 key4-key3
676 explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
677 id select_type table type possible_keys key key_len ref rows Extra
678 1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index
679 select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
680 key1 key2 key3
681 100 100 100
682 insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
683 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
684 id select_type table type possible_keys key key_len ref rows Extra
685 1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 83 Using union(intersect(key1,key2),key3); Using where
686 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
687 key1 key2 key3 key4 filler1
688 100 100 100 100 key1-key2-key3-key4
689 100 100 -1 -1 key1-key2
690 101 101 101 101 key1234-101
691 select key1,key2, filler1 from t1 where key1=100 and key2=100;
692 key1 key2 filler1
693 100 100 key1-key2-key3-key4
694 100 100 key1-key2
695 update t1 set filler1='to be deleted' where key1=100 and key2=100;
696 update t1 set key1=200,key2=200 where key1=100 and key2=100;
697 delete from t1 where key1=200 and key2=200;
698 select key1,key2,filler1 from t1 where key2=100 and key2=200;
699 key1 key2 filler1
700 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
701 id select_type table type possible_keys key key_len ref rows Extra
702 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 152 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
703 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
704 key1 key2 key3 key4 filler1
705 -1 -1 100 100 key4-key3
706 delete from t1 where key3=100 and key4=100;
707 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
708 id select_type table type possible_keys key key_len ref rows Extra
709 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 152 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
710 select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
711 key1 key2 key3 key4 filler1
712 explain select key1,key2 from t1 where key1=100 and key2=100;
713 id select_type table type possible_keys key key_len ref rows Extra
714 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 76 Using intersect(key1,key2); Using where; Using index
715 select key1,key2 from t1 where key1=100 and key2=100;
716 key1 key2
717 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
718 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
719 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
720 explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
721 id select_type table type possible_keys key key_len ref rows Extra
722 1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 136 Using union(key3,intersect(key1,key2),key4); Using where
723 select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
724 key1 key2 key3 key4 filler1
725 100 100 200 200 key1-key2-key3-key4-3
726 100 100 200 200 key1-key2-key3-key4-2
727 100 100 200 200 key1-key2-key3-key4-1
728 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
729 explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
730 id select_type table type possible_keys key key_len ref rows Extra
731 1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 146 Using union(key3,intersect(key1,key2),key4); Using where
732 select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
733 key1 key2 key3 key4 filler1
734 100 100 200 200 key1-key2-key3-key4-3
735 100 100 200 200 key1-key2-key3-key4-2
736 100 100 200 200 key1-key2-key3-key4-1
737 -1 -1 -1 200 key4
738 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
739 explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
740 id select_type table type possible_keys key key_len ref rows Extra
741 1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 156 Using union(key3,intersect(key1,key2),key4); Using where
742 select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
743 key1 key2 key3 key4 filler1
744 100 100 200 200 key1-key2-key3-key4-3
745 100 100 200 200 key1-key2-key3-key4-2
746 100 100 200 200 key1-key2-key3-key4-1
747 -1 -1 -1 200 key4
748 -1 -1 200 -1 key3
749 explain select * from t1 where st_a=1 and st_b=1;
750 id select_type table type possible_keys key key_len ref rows Extra
751 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 3515 Using intersect(st_a,st_b); Using where
752 explain select st_a,st_b from t1 where st_a=1 and st_b=1;
753 id select_type table type possible_keys key key_len ref rows Extra
754 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 3515 Using intersect(st_a,st_b); Using where; Using index
755 explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
756 id select_type table type possible_keys key key_len ref rows Extra
757 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 15093 Using where
758 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
759 id select_type table type possible_keys key key_len ref rows Extra
760 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971
761 explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
762 id select_type table type possible_keys key key_len ref rows Extra
763 1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3879 Using where
764 explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
765 id select_type table type possible_keys key key_len ref rows Extra
766 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
767 explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
768 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
769 id select_type table type possible_keys key key_len ref rows Extra
770 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL 58 Using intersect(sta_swt12a,stb_swt1b); Using where
771 explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
772 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
773 id select_type table type possible_keys key key_len ref rows Extra
774 1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,sta_swt2a,stb_swt1b 8,8,8 NULL 57 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where
775 explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
776 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
777 id select_type table type possible_keys key key_len ref rows Extra
778 1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 223 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
779 explain select * from t1
780 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
781 id select_type table type possible_keys key key_len ref rows Extra
782 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
783 explain select * from t1
784 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
785 id select_type table type possible_keys key key_len ref rows Extra
786 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 232 Using intersect(sta_swt1a,stb_swt1b); Using where
787 explain select st_a from t1
788 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
789 id select_type table type possible_keys key key_len ref rows Extra
790 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
791 explain select st_a from t1
792 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
793 id select_type table type possible_keys key key_len ref rows Extra
794 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt1a,stb_swt1b 8,8 NULL 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
795 drop table t0,t1;
796 create table t2 (
797 a char(10),
798 b char(10),
799 filler1 char(255),
800 filler2 char(255),
801 key(a(5)),
802 key(b(5))
803 );
804 select count(a) from t2 where a='BBBBBBBB';
805 count(a)
806 4
807 select count(a) from t2 where b='BBBBBBBB';
808 count(a)
809 4
810 expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
811 id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b
812 1 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 4 Using where
813 select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
814 count(a)
815 4
816 select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
817 count(a)
818 4
819 insert into t2 values ('ab', 'ab', 'uh', 'oh');
820 explain select a from t2 where a='ab';
821 id select_type table type possible_keys key key_len ref rows Extra
822 1 SIMPLE t2 ref a a 6 const 1 Using where
823 drop table t2;
824 CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
825 KEY(c1), KEY(c2), KEY(c3));
826 INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
827 (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
828 INSERT INTO t1 VALUES(0,0,0);
829 CREATE TABLE t2(c1 int);
830 INSERT INTO t2 VALUES(1);
831 DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
832 SELECT * FROM t1;
833 c1 c2 c3
834 DROP TABLE t1,t2;
835 #---------------- Index merge test 2 -------------------------------------------
836 SET SESSION STORAGE_ENGINE = MyISAM;
837 drop table if exists t1,t2;
838 create table t1
839 (
840 key1 int not null,
841 key2 int not null,
842 INDEX i1(key1),
843 INDEX i2(key2)
844 );
845 explain select * from t1 where key1 < 5 or key2 > 197;
846 id select_type table type possible_keys key key_len ref rows Extra
847 1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
848 select * from t1 where key1 < 5 or key2 > 197;
849 key1 key2
850 0 200
851 1 199
852 2 198
853 3 197
854 4 196
855 explain select * from t1 where key1 < 3 or key2 > 195;
856 id select_type table type possible_keys key key_len ref rows Extra
857 1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
858 select * from t1 where key1 < 3 or key2 > 195;
859 key1 key2
860 0 200
861 1 199
862 2 198
863 3 197
864 4 196
865 alter table t1 add str1 char (255) not null,
866 add zeroval int not null default 0,
867 add str2 char (255) not null,
868 add str3 char (255) not null;
869 update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
870 alter table t1 add primary key (str1, zeroval, str2, str3);
871 explain select * from t1 where key1 < 5 or key2 > 197;
872 id select_type table type possible_keys key key_len ref rows Extra
873 1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
874 select * from t1 where key1 < 5 or key2 > 197;
875 key1 key2 str1 zeroval str2 str3
876 0 200 aaa 0 bbb 200-0_a
877 1 199 aaa 0 bbb 199-0_A
878 2 198 aaa 0 bbb 198-1_a
879 3 197 aaa 0 bbb 197-1_A
880 4 196 aaa 0 bbb 196-2_a
881 explain select * from t1 where key1 < 3 or key2 > 195;
882 id select_type table type possible_keys key key_len ref rows Extra
883 1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
884 select * from t1 where key1 < 3 or key2 > 195;
885 key1 key2 str1 zeroval str2 str3
886 0 200 aaa 0 bbb 200-0_a
887 1 199 aaa 0 bbb 199-0_A
888 2 198 aaa 0 bbb 198-1_a
889 3 197 aaa 0 bbb 197-1_A
890 4 196 aaa 0 bbb 196-2_a
891 drop table t1;
892 create table t1 (
893 pk integer not null auto_increment primary key,
894 key1 integer,
895 key2 integer not null,
896 filler char (200),
897 index (key1),
898 index (key2)
899 );
900 show warnings;
901 Level Code Message
902 explain select pk from t1 where key1 = 1 and key2 = 1;
903 id select_type table type possible_keys key key_len ref rows Extra
904 1 SIMPLE t1 ref key1,key2 key1 5 const 4 Using where
905 select pk from t1 where key2 = 1 and key1 = 1;
906 pk
907 26
908 27
909 select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
910 pk
911 26
912 27
913 drop table t1;
914 create table t1 (
915 pk int primary key auto_increment,
916 key1a int,
917 key2a int,
918 key1b int,
919 key2b int,
920 dummy1 int,
921 dummy2 int,
922 dummy3 int,
923 dummy4 int,
924 key3a int,
925 key3b int,
926 filler1 char (200),
927 index i1(key1a, key1b),
928 index i2(key2a, key2b),
929 index i3(key3a, key3b)
930 );
931 create table t2 (a int);
932 insert into t2 values (0),(1),(2),(3),(4),(NULL);
933 insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
934 select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
935 insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
936 select key1a, key1b, key2a, key2b, key3a, key3b from t1;
937 insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
938 select key1a, key1b, key2a, key2b, key3a, key3b from t1;
939 analyze table t1;
940 Table Op Msg_type Msg_text
941 test.t1 analyze status OK
942 select count(*) from t1;
943 count(*)
944 5184
945 explain select count(*) from t1 where
946 key1a = 2 and key1b is null and key2a = 2 and key2b is null;
947 id select_type table type possible_keys key key_len ref rows Extra
948 1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 2 Using intersect(i1,i2); Using where; Using index
949 select count(*) from t1 where
950 key1a = 2 and key1b is null and key2a = 2 and key2b is null;
951 count(*)
952 4
953 explain select count(*) from t1 where
954 key1a = 2 and key1b is null and key3a = 2 and key3b is null;
955 id select_type table type possible_keys key key_len ref rows Extra
956 1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 2 Using intersect(i1,i3); Using where; Using index
957 select count(*) from t1 where
958 key1a = 2 and key1b is null and key3a = 2 and key3b is null;
959 count(*)
960 4
961 drop table t1,t2;
962 create table t1 (
963 id1 int,
964 id2 date ,
965 index idx2 (id1,id2),
966 index idx1 (id2)
967 );
968 insert into t1 values(1,'20040101'), (2,'20040102');
969 select * from t1 where id1 = 1 and id2= '20040101';
970 id1 id2
971 1 2004-01-01
972 drop table t1;
973 drop view if exists v1;
974 CREATE TABLE t1 (
975 `oid` int(11) unsigned NOT NULL auto_increment,
976 `fk_bbk_niederlassung` int(11) unsigned NOT NULL,
977 `fk_wochentag` int(11) unsigned NOT NULL,
978 `uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
979 `uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
980 `geloescht` tinyint(4) NOT NULL,
981 `version` int(5) NOT NULL,
982 PRIMARY KEY (`oid`),
983 KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
984 KEY `fk_wochentag` (`fk_wochentag`),
985 KEY `ix_version` (`version`)
986 ) DEFAULT CHARSET=latin1;
987 insert into t1 values
988 (1, 38, 1, '08:00:00', '13:00:00', 0, 1),
989 (2, 38, 2, '08:00:00', '13:00:00', 0, 1),
990 (3, 38, 3, '08:00:00', '13:00:00', 0, 1),
991 (4, 38, 4, '08:00:00', '13:00:00', 0, 1),
992 (5, 38, 5, '08:00:00', '13:00:00', 0, 1),
993 (6, 38, 5, '08:00:00', '13:00:00', 1, 2),
994 (7, 38, 3, '08:00:00', '13:00:00', 1, 2),
995 (8, 38, 1, '08:00:00', '13:00:00', 1, 2),
996 (9, 38, 2, '08:00:00', '13:00:00', 1, 2),
997 (10, 38, 4, '08:00:00', '13:00:00', 1, 2),
998 (11, 38, 1, '08:00:00', '13:00:00', 0, 3),
999 (12, 38, 2, '08:00:00', '13:00:00', 0, 3),
1000 (13, 38, 3, '08:00:00', '13:00:00', 0, 3),
1001 (14, 38, 4, '08:00:00', '13:00:00', 0, 3),
1002 (15, 38, 5, '08:00:00', '13:00:00', 0, 3),
1003 (16, 38, 4, '08:00:00', '13:00:00', 0, 4),
1004 (17, 38, 5, '08:00:00', '13:00:00', 0, 4),
1005 (18, 38, 1, '08:00:00', '13:00:00', 0, 4),
1006 (19, 38, 2, '08:00:00', '13:00:00', 0, 4),
1007 (20, 38, 3, '08:00:00', '13:00:00', 0, 4),
1008 (21, 7, 1, '08:00:00', '13:00:00', 0, 1),
1009 (22, 7, 2, '08:00:00', '13:00:00', 0, 1),
1010 (23, 7, 3, '08:00:00', '13:00:00', 0, 1),
1011 (24, 7, 4, '08:00:00', '13:00:00', 0, 1),
1012 (25, 7, 5, '08:00:00', '13:00:00', 0, 1);
1013 create view v1 as
1014 select
1015 zeit1.oid AS oid,
1016 zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
1017 zeit1.fk_wochentag AS fk_wochentag,
1018 zeit1.uhrzeit_von AS uhrzeit_von,
1019 zeit1.uhrzeit_bis AS uhrzeit_bis,
1020 zeit1.geloescht AS geloescht,
1021 zeit1.version AS version
1022 from
1023 t1 zeit1
1024 where
1025 (zeit1.version =
1026 (select max(zeit2.version) AS `max(version)`
1027 from t1 zeit2
1028 where
1029 ((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
1030 (zeit1.fk_wochentag = zeit2.fk_wochentag) and
1031 (zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
1032 (zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
1033 )
1034 )
1035 )
1036 and (zeit1.geloescht = 0);
1037 select * from v1 where oid = 21;
1038 oid fk_bbk_niederlassung fk_wochentag uhrzeit_von uhrzeit_bis geloescht version
1039 21 7 1 08:00:00 13:00:00 0 1
1040 drop view v1;
1041 drop table t1;
1042 CREATE TABLE t1(
1043 t_cpac varchar(2) NOT NULL,
1044 t_vers varchar(4) NOT NULL,
1045 t_rele varchar(2) NOT NULL,
1046 t_cust varchar(4) NOT NULL,
1047 filler1 char(250) default NULL,
1048 filler2 char(250) default NULL,
1049 PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
1050 UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
1051 KEY IX_5 (t_vers,t_rele,t_cust)
1052 );
1053 insert into t1 values
1054 ('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''),
1055 ('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''),
1056 ('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''),
1057 ('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''),
1058 ('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''),
1059 ('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''),
1060 ('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''),
1061 ('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''),
1062 ('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''),
1063 ('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''),
1064 ('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''),
1065 ('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''),
1066 ('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''),
1067 ('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
1068 ('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
1069 ('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
1070 ('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
1071 ('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
1072 ('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
1073 ('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
1074 ('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
1075 ('wh','B61U','a ','stnd','','');
1076 show create table t1;
1077 Table Create Table
1078 t1 CREATE TABLE `t1` (
1079 `t_cpac` varchar(2) NOT NULL,
1080 `t_vers` varchar(4) NOT NULL,
1081 `t_rele` varchar(2) NOT NULL,
1082 `t_cust` varchar(4) NOT NULL,
1083 `filler1` char(250) DEFAULT NULL,
1084 `filler2` char(250) DEFAULT NULL,
1085 PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`),
1086 UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),
1087 KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)
1088 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
1089 select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
1090 t_vers t_rele t_cust filler1
1091 7.6 a
1092 7.6 a
1093 select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
1094 and t_rele='a' and t_cust = ' ';
1095 t_vers t_rele t_cust filler1
1096 7.6 a
1097 7.6 a
1098 drop table t1;
1099 create table t1 (
1100 pk int(11) not null auto_increment,
1101 a int(11) not null default '0',
1102 b int(11) not null default '0',
1103 c int(11) not null default '0',
1104 filler1 datetime, filler2 varchar(15),
1105 filler3 longtext,
1106 kp1 varchar(4), kp2 varchar(7),
1107 kp3 varchar(2), kp4 varchar(4),
1108 kp5 varchar(7),
1109 filler4 char(1),
1110 primary key (pk),
1111 key idx1(a,b,c),
1112 key idx2(c),
1113 key idx3(kp1,kp2,kp3,kp4,kp5)
1114 ) default charset=latin1;
1115 set @fill=NULL;
1116 SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
1117 kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R ';
1118 COUNT(*)
1119 1
1120 drop table t1;
1121 create table t1
1122 (
1123 key1 int not null,
1124 key2 int not null default 0,
1125 key3 int not null default 0
1126 );
1127 insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
1128 set @d=8;
1129 insert into t1 (key1) select key1+@d from t1;
1130 set @d=@d*2;
1131 insert into t1 (key1) select key1+@d from t1;
1132 set @d=@d*2;
1133 insert into t1 (key1) select key1+@d from t1;
1134 set @d=@d*2;
1135 insert into t1 (key1) select key1+@d from t1;
1136 set @d=@d*2;
1137 insert into t1 (key1) select key1+@d from t1;
1138 set @d=@d*2;
1139 insert into t1 (key1) select key1+@d from t1;
1140 set @d=@d*2;
1141 insert into t1 (key1) select key1+@d from t1;
1142 set @d=@d*2;
1143 alter table t1 add index i2(key2);
1144 alter table t1 add index i3(key3);
1145 update t1 set key2=key1,key3=key1;
1146 explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
1147 id select_type table type possible_keys key key_len ref rows Extra
1148 1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where
1149 select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
1150 key1 key2 key3
1151 31 31 31
1152 32 32 32
1153 33 33 33
1154 34 34 34
1155 35 35 35
1156 36 36 36
1157 37 37 37
1158 38 38 38
1159 39 39 39
1160 drop table t1;
1161 #
1162 # Bug#56423: Different count with SELECT and CREATE SELECT queries
1163 #
1164 CREATE TABLE t1 (
1165 a INT,
1166 b INT,
1167 c INT,
1168 d INT,
1169 PRIMARY KEY (a),
1170 KEY (c),
1171 KEY bd (b,d)
1172 );
1173 INSERT INTO t1 VALUES
1174 (1, 0, 1, 0),
1175 (2, 1, 1, 1),
1176 (3, 1, 1, 1),
1177 (4, 0, 1, 1);
1178 EXPLAIN
1179 SELECT a
1180 FROM t1
1181 WHERE c = 1 AND b = 1 AND d = 1;
1182 id select_type table type possible_keys key key_len ref rows Extra
1183 1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where
1184 CREATE TABLE t2 ( a INT )
1185 SELECT a
1186 FROM t1
1187 WHERE c = 1 AND b = 1 AND d = 1;
1188 SELECT * FROM t2;
1189 a
1190 2
1191 3
1192 DROP TABLE t1, t2;
1193 CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
1194 INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
1195 SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
1196 a b
1197 1 2
1198 1 2
1199 1 2
1200 1 2
1201 DROP TABLE t1;
1202 # Code coverage of fix.
1203 CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
1204 INSERT INTO t1 (b) VALUES (1);
1205 UPDATE t1 SET b = 2 WHERE a = 1;
1206 SELECT * FROM t1;
1207 a b
1208 1 2
1209 CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
1210 INSERT INTO t2 (b) VALUES ('a');
1211 UPDATE t2 SET b = 'b' WHERE a = 1;
1212 SELECT * FROM t2;
1213 a b
1214 1 b
1215 DROP TABLE t1, t2;
1216 #---------------- 2-sweeps read Index merge test 2 -------------------------------
1217 SET SESSION STORAGE_ENGINE = MyISAM;
1218 drop table if exists t1;
1219 create table t1 (
1220 pk int primary key,
1221 key1 int,
1222 key2 int,
1223 filler char(200),
1224 filler2 char(200),
1225 index(key1),
1226 index(key2)
1227 );
1228 select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
1229 pk key1 key2 filler filler2
1230 10 10 10 filler-data filler-data-2
1231 9 9 9 filler-data filler-data-2
1232 8 8 8 filler-data filler-data-2
1233 7 7 7 filler-data filler-data-2
1234 6 6 6 filler-data filler-data-2
1235 5 5 5 filler-data filler-data-2
1236 4 4 4 filler-data filler-data-2
1237 3 3 3 filler-data filler-data-2
1238 2 2 2 filler-data filler-data-2
1239 set @maxv=1000;
1240 select * from t1 where
1241 (pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
1242 or key1=18 or key1=60;
1243 pk key1 key2 filler filler2
1244 1000 1000 1000 filler-data filler-data-2
1245 999 999 999 filler-data filler-data-2
1246 998 998 998 filler-data filler-data-2
1247 997 997 997 filler-data filler-data-2
1248 996 996 996 filler-data filler-data-2
1249 995 995 995 filler-data filler-data-2
1250 994 994 994 filler-data filler-data-2
1251 993 993 993 filler-data filler-data-2
1252 992 992 992 filler-data filler-data-2
1253 991 991 991 filler-data filler-data-2
1254 60 60 60 filler-data filler-data-2
1255 54 54 54 filler-data filler-data-2
1256 53 53 53 filler-data filler-data-2
1257 52 52 52 filler-data filler-data-2
1258 51 51 51 filler-data filler-data-2
1259 50 50 50 filler-data filler-data-2
1260 18 18 18 filler-data filler-data-2
1261 14 14 14 filler-data filler-data-2
1262 13 13 13 filler-data filler-data-2
1263 12 12 12 filler-data filler-data-2
1264 11 11 11 filler-data filler-data-2
1265 4 4 4 filler-data filler-data-2
1266 3 3 3 filler-data filler-data-2
1267 2 2 2 filler-data filler-data-2
1268 1 1 1 filler-data filler-data-2
1269 select * from t1 where
1270 (pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
1271 or key1 < 3 or key1 > @maxv-11;
1272 pk key1 key2 filler filler2
1273 1000 1000 1000 filler-data filler-data-2
1274 999 999 999 filler-data filler-data-2
1275 998 998 998 filler-data filler-data-2
1276 997 997 997 filler-data filler-data-2
1277 996 996 996 filler-data filler-data-2
1278 995 995 995 filler-data filler-data-2
1279 994 994 994 filler-data filler-data-2
1280 993 993 993 filler-data filler-data-2
1281 992 992 992 filler-data filler-data-2
1282 991 991 991 filler-data filler-data-2
1283 990 990 990 filler-data filler-data-2
1284 54 54 54 filler-data filler-data-2
1285 53 53 53 filler-data filler-data-2
1286 52 52 52 filler-data filler-data-2
1287 51 51 51 filler-data filler-data-2
1288 50 50 50 filler-data filler-data-2
1289 14 14 14 filler-data filler-data-2
1290 13 13 13 filler-data filler-data-2
1291 12 12 12 filler-data filler-data-2
1292 11 11 11 filler-data filler-data-2
1293 4 4 4 filler-data filler-data-2
1294 3 3 3 filler-data filler-data-2
1295 2 2 2 filler-data filler-data-2
1296 1 1 1 filler-data filler-data-2
1297 select * from t1 where
1298 (pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
1299 or
1300 (key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
1301 pk key1 key2 filler filler2
1302 1000 1000 1000 filler-data filler-data-2
1303 999 999 999 filler-data filler-data-2
1304 998 998 998 filler-data filler-data-2
1305 997 997 997 filler-data filler-data-2
1306 996 996 996 filler-data filler-data-2
1307 995 995 995 filler-data filler-data-2
1308 994 994 994 filler-data filler-data-2
1309 993 993 993 filler-data filler-data-2
1310 992 992 992 filler-data filler-data-2
1311 991 991 991 filler-data filler-data-2
1312 54 54 54 filler-data filler-data-2
1313 53 53 53 filler-data filler-data-2
1314 52 52 52 filler-data filler-data-2
1315 51 51 51 filler-data filler-data-2
1316 50 50 50 filler-data filler-data-2
1317 14 14 14 filler-data filler-data-2
1318 13 13 13 filler-data filler-data-2
1319 12 12 12 filler-data filler-data-2
1320 11 11 11 filler-data filler-data-2
1321 4 4 4 filler-data filler-data-2
1322 3 3 3 filler-data filler-data-2
1323 2 2 2 filler-data filler-data-2
1324 1 1 1 filler-data filler-data-2
1325 select * from t1 where
1326 (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
1327 or
1328 (key1 < 5) or (key1 > @maxv-10);
1329 pk key1 key2 filler filler2
1330 1000 1000 1000 filler-data filler-data-2
1331 999 999 999 filler-data filler-data-2
1332 998 998 998 filler-data filler-data-2
1333 997 997 997 filler-data filler-data-2
1334 996 996 996 filler-data filler-data-2
1335 995 995 995 filler-data filler-data-2
1336 994 994 994 filler-data filler-data-2
1337 993 993 993 filler-data filler-data-2
1338 992 992 992 filler-data filler-data-2
1339 991 991 991 filler-data filler-data-2
1340 54 54 54 filler-data filler-data-2
1341 53 53 53 filler-data filler-data-2
1342 52 52 52 filler-data filler-data-2
1343 51 51 51 filler-data filler-data-2
1344 50 50 50 filler-data filler-data-2
1345 14 14 14 filler-data filler-data-2
1346 13 13 13 filler-data filler-data-2
1347 12 12 12 filler-data filler-data-2
1348 11 11 11 filler-data filler-data-2
1349 4 4 4 filler-data filler-data-2
1350 3 3 3 filler-data filler-data-2
1351 2 2 2 filler-data filler-data-2
1352 1 1 1 filler-data filler-data-2
1353 drop table t1;
1354 #---------------- Clustered PK ROR-index_merge tests -----------------------------
1355 SET SESSION STORAGE_ENGINE = MyISAM;
1356 drop table if exists t1;
1357 create table t1
1358 (
1359 pk1 int not null,
1360 pk2 int not null,
1361 key1 int not null,
1362 key2 int not null,
1363 pktail1ok int not null,
1364 pktail2ok int not null,
1365 pktail3bad int not null,
1366 pktail4bad int not null,
1367 pktail5bad int not null,
1368 pk2copy int not null,
1369 badkey int not null,
1370 filler1 char (200),
1371 filler2 char (200),
1372 key (key1),
1373 key (key2),
1374 /* keys with tails from CPK members */
1375 key (pktail1ok, pk1),
1376 key (pktail2ok, pk1, pk2),
1377 key (pktail3bad, pk2, pk1),
1378 key (pktail4bad, pk1, pk2copy),
1379 key (pktail5bad, pk1, pk2, pk2copy),
1380 primary key (pk1, pk2)
1381 );
1382 explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
1383 id select_type table type possible_keys key key_len ref rows Extra
1384 1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using where
1385 select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
1386 pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
1387 1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
1388 1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
1389 1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
1390 1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
1391 1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
1392 1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
1393 1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
1394 1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
1395 1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
1396 1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
1397 explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
1398 id select_type table type possible_keys key key_len ref rows Extra
1399 1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
1400 select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
1401 pk1 pk2
1402 95 59
1403 95 58
1404 95 57
1405 95 56
1406 95 55
1407 95 54
1408 95 53
1409 95 52
1410 95 51
1411 95 50
1412 explain select * from t1 where badkey=1 and key1=10;
1413 id select_type table type possible_keys key key_len ref rows Extra
1414 1 SIMPLE t1 ref key1 key1 4 const 91 Using where
1415 explain select * from t1 where pk1 < 7500 and key1 = 10;
1416 id select_type table type possible_keys key key_len ref rows Extra
1417 1 SIMPLE t1 ref PRIMARY,key1 key1 4 const ROWS Using where
1418 explain select * from t1 where pktail1ok=1 and key1=10;
1419 id select_type table type possible_keys key key_len ref rows Extra
1420 1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where
1421 explain select * from t1 where pktail2ok=1 and key1=10;
1422 id select_type table type possible_keys key key_len ref rows Extra
1423 1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where
1424 explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
1425 id select_type table type possible_keys key key_len ref rows Extra
1426 1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where
1427 explain select * from t1 where pktail3bad=1 and key1=10;
1428 id select_type table type possible_keys key key_len ref rows Extra
1429 1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where
1430 explain select * from t1 where pktail4bad=1 and key1=10;
1431 id select_type table type possible_keys key key_len ref rows Extra
1432 1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where
1433 explain select * from t1 where pktail5bad=1 and key1=10;
1434 id select_type table type possible_keys key key_len ref rows Extra
1435 1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 70 Using where
1436 explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
1437 id select_type table type possible_keys key key_len ref rows Extra
1438 1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
1439 select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
1440 pk1 pk2 key1 key2
1441 104 49 10 10
1442 104 48 10 10
1443 104 47 10 10
1444 104 46 10 10
1445 104 45 10 10
1446 104 44 10 10
1447 104 43 10 10
1448 104 42 10 10
1449 104 41 10 10
1450 104 40 10 10
1451 drop table t1;
1452 create table t1
1453 (
1454 RUNID varchar(22),
1455 SUBMITNR varchar(5),
1456 ORDERNR char(1),
1457 PROGRAMM varchar(8),
1458 TESTID varchar(4),
1459 UCCHECK char(1),
1460 ETEXT varchar(80),
1461 ETEXT_TYPE char(1),
1462 INFO char(1),
1463 SEVERITY tinyint(3),
1464 TADIRFLAG char(1),
1465 PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
1466 KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
1467 ) DEFAULT CHARSET=latin1;
1468 update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
1469 WHERE
1470 `RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
1471 `TESTID`='' AND `UCCHECK`='';
1472 drop table t1;
1473 #
1474 # Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
1475 #
1476 CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
1477 INSERT INTO t1 VALUES (2);
1478 CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
1479 PRIMARY KEY (f1), KEY (f2), KEY (f3) );
1480 INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
1481 SELECT t1.f1 FROM t1
1482 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
1483 f1
1484 2
1485 EXPLAIN SELECT t1.f1 FROM t1
1486 WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
1487 id select_type table type possible_keys key key_len ref rows Extra
1488 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
1489 2 DEPENDENT SUBQUERY t2 ref f2,f3 f2 5 1 Using where
1490 DROP TABLE t1,t2;
1491 #
1492 # Generic @@optimizer_switch tests (move those into a separate file if
1493 # we get another @@optimizer_switch user)
1494 #
1495 select @@optimizer_switch;
1496 @@optimizer_switch
1497 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1498 set optimizer_switch='index_merge=off,index_merge_union=off';
1499 select @@optimizer_switch;
1500 @@optimizer_switch
1501 index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on
1502 set optimizer_switch='index_merge_union=on';
1503 select @@optimizer_switch;
1504 @@optimizer_switch
1505 index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1506 set optimizer_switch='default,index_merge_sort_union=off';
1507 select @@optimizer_switch;
1508 @@optimizer_switch
1509 index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on
1510 set optimizer_switch=4;
1511 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of '4'
1512 set optimizer_switch=NULL;
1513 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL'
1514 set optimizer_switch='default,index_merge';
1515 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge'
1516 set optimizer_switch='index_merge=index_merge';
1517 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=index_merge'
1518 set optimizer_switch='index_merge=on,but...';
1519 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'but...'
1520 set optimizer_switch='index_merge=';
1521 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge='
1522 set optimizer_switch='index_merge';
1523 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge'
1524 set optimizer_switch='on';
1525 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'on'
1526 set optimizer_switch='index_merge=on,index_merge=off';
1527 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off'
1528 set optimizer_switch='index_merge_union=on,index_merge_union=default';
1529 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge_union=default'
1530 set optimizer_switch='default,index_merge=on,index_merge=off,default';
1531 ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off,default'
1532 set optimizer_switch=default;
1533 set optimizer_switch='index_merge=off,index_merge_union=off,default';
1534 select @@optimizer_switch;
1535 @@optimizer_switch
1536 index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on
1537 set optimizer_switch=default;
1538 select @@global.optimizer_switch;
1539 @@global.optimizer_switch
1540 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1541 set @@global.optimizer_switch=default;
1542 select @@global.optimizer_switch;
1543 @@global.optimizer_switch
1544 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1545 #
1546 # Check index_merge's @@optimizer_switch flags
1547 #
1548 select @@optimizer_switch;
1549 @@optimizer_switch
1550 index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1551 create table t0 (a int);
1552 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1553 create table t1 (a int, b int, c int, filler char(100),
1554 key(a), key(b), key(c));
1555 insert into t1 select
1556 A.a * B.a*10 + C.a*100,
1557 A.a * B.a*10 + C.a*100,
1558 A.a,
1559 'filler'
1560 from t0 A, t0 B, t0 C;
1561 This should use union:
1562 explain select * from t1 where a=1 or b=1;
1563 id select_type table type possible_keys key key_len ref rows Extra
1564 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where
1565 This should use ALL:
1566 set optimizer_switch='default,index_merge=off';
1567 explain select * from t1 where a=1 or b=1;
1568 id select_type table type possible_keys key key_len ref rows Extra
1569 1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where
1570 This should use sort-union:
1571 set optimizer_switch='default,index_merge_union=off';
1572 explain select * from t1 where a=1 or b=1;
1573 id select_type table type possible_keys key key_len ref rows Extra
1574 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using sort_union(a,b); Using where
1575 This will use sort-union:
1576 set optimizer_switch=default;
1577 explain select * from t1 where a<1 or b <1;
1578 id select_type table type possible_keys key key_len ref rows Extra
1579 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where
1580 This should use ALL:
1581 set optimizer_switch='default,index_merge_sort_union=off';
1582 explain select * from t1 where a<1 or b <1;
1583 id select_type table type possible_keys key key_len ref rows Extra
1584 1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where
1585 This should use ALL:
1586 set optimizer_switch='default,index_merge=off';
1587 explain select * from t1 where a<1 or b <1;
1588 id select_type table type possible_keys key key_len ref rows Extra
1589 1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where
1590 This will use sort-union:
1591 set optimizer_switch='default,index_merge_union=off';
1592 explain select * from t1 where a<1 or b <1;
1593 id select_type table type possible_keys key key_len ref rows Extra
1594 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where
1595 alter table t1 add d int, add key(d);
1596 update t1 set d=a;
1597 This will use sort_union:
1598 set optimizer_switch=default;
1599 explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
1600 id select_type table type possible_keys key key_len ref rows Extra
1601 1 SIMPLE t1 index_merge a,b,c,d a,b 5,5 NULL 3 Using sort_union(a,b); Using where
1602 And if we disable sort_union, union:
1603 set optimizer_switch='default,index_merge_sort_union=off';
1604 explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
1605 id select_type table type possible_keys key key_len ref rows Extra
1606 1 SIMPLE t1 index_merge a,b,c,d c,d 5,5 NULL 100 Using union(c,d); Using where
1607 drop table t1;
1608 create table t1 (
1609 a int, b int, c int,
1610 filler1 char(200), filler2 char(200),
1611 key(a),key(b),key(c)
1612 );
1613 insert into t1
1614 select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar'
1615 from t0 A, t0 B, t0 C, t0 D where D.a<5;
1616 This should be intersect:
1617 set optimizer_switch=default;
1618 explain select * from t1 where a=10 and b=10;
1619 id select_type table type possible_keys key key_len ref rows Extra
1620 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
1621 No intersect when index_merge is disabled:
1622 set optimizer_switch='default,index_merge=off';
1623 explain select * from t1 where a=10 and b=10;
1624 id select_type table type possible_keys key key_len ref rows Extra
1625 1 SIMPLE t1 ref a,b a 5 const 49 Using where
1626 No intersect if it is disabled:
1627 set optimizer_switch='default,index_merge_intersection=off';
1628 explain select * from t1 where a=10 and b=10;
1629 id select_type table type possible_keys key key_len ref rows Extra
1630 1 SIMPLE t1 ref a,b a 5 const 49 Using where
1631 Do intersect when union was disabled
1632 set optimizer_switch='default,index_merge_union=off';
1633 explain select * from t1 where a=10 and b=10;
1634 id select_type table type possible_keys key key_len ref rows Extra
1635 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
1636 Do intersect when sort_union was disabled
1637 set optimizer_switch='default,index_merge_sort_union=off';
1638 explain select * from t1 where a=10 and b=10;
1639 id select_type table type possible_keys key key_len ref rows Extra
1640 1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
1641 This will use intersection inside a union:
1642 set optimizer_switch=default;
1643 explain select * from t1 where a=10 and b=10 or c=10;
1644 id select_type table type possible_keys key key_len ref rows Extra
1645 1 SIMPLE t1 index_merge a,b,c a,b,c 5,5,5 NULL 6 Using union(intersect(a,b),c); Using where
1646 Should be only union left:
1647 set optimizer_switch='default,index_merge_intersection=off';
1648 explain select * from t1 where a=10 and b=10 or c=10;
1649 id select_type table type possible_keys key key_len ref rows Extra
1650 1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using union(a,c); Using where
1651 This will switch to sort-union (intersection will be gone, too,
1652 thats a known limitation:
1653 set optimizer_switch='default,index_merge_union=off';
1654 explain select * from t1 where a=10 and b=10 or c=10;
1655 id select_type table type possible_keys key key_len ref rows Extra
1656 1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using sort_union(a,c); Using where
1657 set optimizer_switch=default;
1658 show variables like 'optimizer_switch';
1659 Variable_name Value
1660 optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
1661 drop table t0, t1;