1
drop table if exists t0, t1, t2, t3, t4;
7
alter table t0 add key2 int not null, add index i2(key2);
8
alter table t0 add key3 int not null, add index i3(key3);
9
alter table t0 add key4 int not null, add index i4(key4);
10
alter table t0 add key5 int not null, add index i5(key5);
11
alter table t0 add key6 int not null, add index i6(key6);
12
alter table t0 add key7 int not null, add index i7(key7);
13
alter table t0 add key8 int not null, add index i8(key8);
14
update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
16
Table Op Msg_type Msg_text
17
test.t0 analyze status OK
18
explain select * from t0 where key1 < 3 or key1 > 1020;
19
id select_type table type possible_keys key key_len ref rows Extra
20
1 SIMPLE t0 range i1 i1 4 NULL 78 Using where
22
select * from t0 where key1 < 3 or key2 > 1020;
23
id select_type table type possible_keys key key_len ref rows Extra
24
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 45 Using sort_union(i1,i2); Using where
25
select * from t0 where key1 < 3 or key2 > 1020;
26
key1 key2 key3 key4 key5 key6 key7 key8
29
1021 1021 1021 1021 1021 1021 1021 3
30
1022 1022 1022 1022 1022 1022 1022 2
31
1023 1023 1023 1023 1023 1023 1023 1
32
1024 1024 1024 1024 1024 1024 1024 0
33
explain select * from t0 where key1 < 3 or key2 <4;
34
id select_type table type possible_keys key key_len ref rows Extra
35
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where
37
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
38
id select_type table type possible_keys key key_len ref rows Extra
39
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 11 Using sort_union(i1,i2); Using where
40
select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40);
41
key1 key2 key3 key4 key5 key6 key7 key8
42
31 31 31 31 31 31 31 993
43
32 32 32 32 32 32 32 992
44
33 33 33 33 33 33 33 991
45
34 34 34 34 34 34 34 990
46
35 35 35 35 35 35 35 989
47
36 36 36 36 36 36 36 988
48
37 37 37 37 37 37 37 987
49
38 38 38 38 38 38 38 986
50
39 39 39 39 39 39 39 985
51
explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
52
id select_type table type possible_keys key key_len ref rows Extra
53
1 SIMPLE t0 ALL i1 NULL NULL NULL 1024 Using where
54
explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
55
id select_type table type possible_keys key key_len ref rows Extra
56
1 SIMPLE t0 ref i1,i2,i3 i3 4 const 1 Using where
57
explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
58
id select_type table type possible_keys key key_len ref rows Extra
59
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where
60
explain select * from t0 where (key1 > 1 or key2 > 2);
61
id select_type table type possible_keys key key_len ref rows Extra
62
1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
63
explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
64
id select_type table type possible_keys key key_len ref rows Extra
65
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 1024 Using sort_union(i1,i2); Using where
67
select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or
68
(key1>10 and key1<12) or (key2>100 and key2<110);
69
id select_type table type possible_keys key key_len ref rows Extra
70
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 17 Using sort_union(i1,i2); Using where
71
explain select * from t0 where key2 = 45 or key1 <=> null;
72
id select_type table type possible_keys key key_len ref rows Extra
73
1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where
74
explain select * from t0 where key2 = 45 or key1 is not null;
75
id select_type table type possible_keys key key_len ref rows Extra
76
1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
77
explain select * from t0 where key2 = 45 or key1 is null;
78
id select_type table type possible_keys key key_len ref rows Extra
79
1 SIMPLE t0 ref i2 i2 4 const 1
80
explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
81
id select_type table type possible_keys key key_len ref rows Extra
82
1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
83
explain select * from t0 where key2=10 or key3=3 or key4 is null;
84
id select_type table type possible_keys key key_len ref rows Extra
85
1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where
86
explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
87
(key3=10) or (key4 <=> null);
88
id select_type table type possible_keys key key_len ref rows Extra
89
1 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL 6 Using sort_union(i2,i3); Using where
90
explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
91
(key3=10) or (key4 <=> null);
92
id select_type table type possible_keys key key_len ref rows Extra
93
1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 6 Using sort_union(i1,i3); Using where
94
explain select * from t0 where
95
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
96
id select_type table type possible_keys key key_len ref rows Extra
97
1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where
99
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
100
id select_type table type possible_keys key key_len ref rows Extra
101
1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where
102
select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
103
key1 key2 key3 key4 key5 key6 key7 key8
109
explain select * from t0 where
110
(key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
111
id select_type table type possible_keys key key_len ref rows Extra
112
1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where
113
explain select * from t0 where
114
(key1 < 3 or key2 < 3) and (key3 < 100);
115
id select_type table type possible_keys key key_len ref rows Extra
116
1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using where
117
explain select * from t0 where
118
(key1 < 3 or key2 < 3) and (key3 < 1000);
119
id select_type table type possible_keys key key_len ref rows Extra
120
1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where
121
explain select * from t0 where
122
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
125
id select_type table type possible_keys key key_len ref rows Extra
126
1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where
127
explain select * from t0 where
128
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
131
id select_type table type possible_keys key key_len ref rows Extra
132
1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
133
select * from t0 where
134
((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
137
key1 key2 key3 key4 key5 key6 key7 key8
144
explain select * from t0 where
145
((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
147
((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
148
id select_type table type possible_keys key key_len ref rows Extra
149
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
150
explain select * from t0 where
151
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
153
((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6));
154
id select_type table type possible_keys key key_len ref rows Extra
155
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
156
explain select * from t0 where
157
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
159
((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6));
160
id select_type table type possible_keys key key_len ref rows Extra
161
1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
162
explain select * from t0 where
163
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
165
(((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6));
166
id select_type table type possible_keys key key_len ref rows Extra
167
1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where
168
explain select * from t0 where
169
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
171
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
172
id select_type table type possible_keys key key_len ref rows Extra
173
1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where
174
explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where
175
((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4))
177
((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6));
178
id select_type table type possible_keys key key_len ref rows Extra
179
1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where
180
select * from t0 where key1 < 5 or key8 < 4 order by key1;
181
key1 key2 key3 key4 key5 key6 key7 key8
186
1021 1021 1021 1021 1021 1021 1021 3
187
1022 1022 1022 1022 1022 1022 1022 2
188
1023 1023 1023 1023 1023 1023 1023 1
189
1024 1024 1024 1024 1024 1024 1024 0
191
select * from t0 where key1 < 5 or key8 < 4 order by key1;
192
id select_type table type possible_keys key key_len ref rows Extra
193
1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 9 Using sort_union(i1,i8); Using where; Using filesort
194
create table t2 like t0;
195
insert into t2 select * from t0;
196
alter table t2 add index i1_3(key1, key3);
197
alter table t2 add index i2_3(key2, key3);
198
alter table t2 drop index i1;
199
alter table t2 drop index i2;
200
alter table t2 add index i321(key3, key2, key1);
201
explain select key3 from t2 where key1 = 100 or key2 = 100;
202
id select_type table type possible_keys key key_len ref rows Extra
203
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
204
explain select key3 from t2 where key1 <100 or key2 < 100;
205
id select_type table type possible_keys key key_len ref rows Extra
206
1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL 1024 Using where; Using index
207
explain select key7 from t2 where key1 <100 or key2 < 100;
208
id select_type table type possible_keys key key_len ref rows Extra
209
1 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL 1024 Using where
217
index i1a (key1a, key1b),
218
index i1b (key1b, key1a),
219
index i2_1(key2, key2_1),
220
index i2_2(key2, key2_1)
222
insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
223
select * from t4 where key1a = 3 or key1b = 4;
224
key1a key1b key2 key2_1 key2_2 key3
227
explain select * from t4 where key1a = 3 or key1b = 4;
228
id select_type table type possible_keys key key_len ref rows Extra
229
1 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using sort_union(i1a,i1b); Using where
230
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
231
id select_type table type possible_keys key key_len ref rows Extra
232
1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where
233
explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
234
id select_type table type possible_keys key key_len ref rows Extra
235
1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where
236
explain select * from t4 where key2_1 = 1 or key2_2 = 5;
237
id select_type table type possible_keys key key_len ref rows Extra
238
1 SIMPLE t4 ALL NULL NULL NULL NULL 1024 Using where
239
create table t1 like t0;
240
insert into t1 select * from t0;
241
explain select * from t0 left join t1 on (t0.key1=t1.key1)
242
where t0.key1=3 or t0.key2=4;
243
id select_type table type possible_keys key key_len ref rows Extra
244
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
245
1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
246
select * from t0 left join t1 on (t0.key1=t1.key1)
247
where t0.key1=3 or t0.key2=4;
248
key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8
249
3 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021
250
4 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020
252
select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
253
id select_type table type possible_keys key key_len ref rows Extra
254
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
255
1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
257
select * from t0,t1 where (t0.key1=t1.key1) and
258
(t0.key1=3 or t0.key2=4) and t1.key1<200;
259
id select_type table type possible_keys key key_len ref rows Extra
260
1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where
261
1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
263
select * from t0,t1 where (t0.key1=t1.key1) and
264
(t0.key1=3 or t0.key2<4) and t1.key1=2;
265
id select_type table type possible_keys key key_len ref rows Extra
266
1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where
267
1 SIMPLE t1 ref i1 i1 4 const 1
268
explain select * from t0,t1 where t0.key1 = 5 and
269
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
270
id select_type table type possible_keys key key_len ref rows Extra
271
1 SIMPLE t0 ref i1 i1 4 const 1
272
1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where
273
explain select * from t0,t1 where t0.key1 < 3 and
274
(t1.key1 = t0.key1 or t1.key8 = t0.key1);
275
id select_type table type possible_keys key key_len ref rows Extra
276
1 SIMPLE t0 range i1 i1 4 NULL 3 Using where
277
1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81)
278
explain select * from t1 where key1=3 or key2=4
279
union select * from t1 where key1<4 or key3=5;
280
id select_type table type possible_keys key key_len ref rows Extra
281
1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
282
2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where
283
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
284
explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
285
id select_type table type possible_keys key key_len ref rows Extra
286
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
287
2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
288
create table t3 like t0;
289
insert into t3 select * from t0;
290
alter table t3 add key9 int not null, add index i9(key9);
291
alter table t3 add keyA int not null, add index iA(keyA);
292
alter table t3 add keyB int not null, add index iB(keyB);
293
alter table t3 add keyC int not null, add index iC(keyC);
294
update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
295
explain select * from t3 where
296
key1=1 or key2=2 or key3=3 or key4=4 or
297
key5=5 or key6=6 or key7=7 or key8=8 or
298
key9=9 or keyA=10 or keyB=11 or keyC=12;
299
id select_type table type possible_keys key key_len ref rows Extra
300
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
301
select * from t3 where
302
key1=1 or key2=2 or key3=3 or key4=4 or
303
key5=5 or key6=6 or key7=7 or key8=8 or
304
key9=9 or keyA=10 or keyB=11 or keyC=12;
305
key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC
306
1 1 1 1 1 1 1 1023 1 1 1 1
307
2 2 2 2 2 2 2 1022 2 2 2 2
308
3 3 3 3 3 3 3 1021 3 3 3 3
309
4 4 4 4 4 4 4 1020 4 4 4 4
310
5 5 5 5 5 5 5 1019 5 5 5 5
311
6 6 6 6 6 6 6 1018 6 6 6 6
312
7 7 7 7 7 7 7 1017 7 7 7 7
313
9 9 9 9 9 9 9 1015 9 9 9 9
314
10 10 10 10 10 10 10 1014 10 10 10 10
315
11 11 11 11 11 11 11 1013 11 11 11 11
316
12 12 12 12 12 12 12 1012 12 12 12 12
317
1016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016
318
explain select * from t0 where key1 < 3 or key2 < 4;
319
id select_type table type possible_keys key key_len ref rows Extra
320
1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where
321
select * from t0 where key1 < 3 or key2 < 4;
322
key1 key2 key3 key4 key5 key6 key7 key8
326
update t0 set key8=123 where key1 < 3 or key2 < 4;
327
select * from t0 where key1 < 3 or key2 < 4;
328
key1 key2 key3 key4 key5 key6 key7 key8
332
delete from t0 where key1 < 3 or key2 < 4;
333
select * from t0 where key1 < 3 or key2 < 4;
334
key1 key2 key3 key4 key5 key6 key7 key8
335
select count(*) from t0;
339
create table t4 (a int);
340
insert into t4 values (1),(4),(3);
341
set @save_join_buffer_size=@@join_buffer_size;
342
set join_buffer_size= 4000;
343
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
344
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
345
where (A.key1 < 500000 or A.key2 < 3)
346
and (B.key1 < 500000 or B.key2 < 3);
347
id select_type table type possible_keys key key_len ref rows Extra
348
1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where
349
1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where
350
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
351
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
352
where (A.key1 < 500000 or A.key2 < 3)
353
and (B.key1 < 500000 or B.key2 < 3);
354
max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
356
update t0 set key1=1;
357
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
358
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
359
where (A.key1 = 1 or A.key2 = 1)
360
and (B.key1 = 1 or B.key2 = 1);
361
id select_type table type possible_keys key key_len ref rows Extra
362
1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where
363
1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where
364
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
365
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
366
where (A.key1 = 1 or A.key2 = 1)
367
and (B.key1 = 1 or B.key2 = 1);
368
max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
370
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
371
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
372
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
373
from t0 as A, t0 as B
374
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)
375
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);
376
id select_type table type possible_keys key key_len ref rows Extra
377
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
378
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
379
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
380
from t0 as A, t0 as B
381
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)
382
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);
383
max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5)
385
set join_buffer_size= @save_join_buffer_size;
386
drop table t0, t1, t2, t3, t4;
388
cola char(3) not null, colb char(3) not null, filler char(200),
391
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
393
Table Op Msg_type Msg_text
394
test.t1 optimize status OK
395
select count(*) from t1;
398
explain select * from t1 WHERE cola = 'foo' AND colb = 'bar';
399
id select_type table type possible_keys key key_len ref rows Extra
400
1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 24 Using intersect(cola,colb); Using where
401
explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar';
402
id select_type table type possible_keys key key_len ref rows Extra
403
1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 24 Using intersect(cola,colb); Using where
405
create table t0 (a int);
406
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
409
filler1 char(200), filler2 char(200),
412
insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C;
413
create table t2 like t1;
416
filler1 char(200), filler2 char(200),
418
) engine=merge union=(t1,t2);
419
explain select * from t1 where a=1 and b=1;
420
id select_type table type possible_keys key key_len ref rows Extra
421
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where
422
explain select * from t3 where a=1 and b=1;
423
id select_type table type possible_keys key key_len ref rows Extra
424
1 SIMPLE t3 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where
426
drop table t0, t1, t2;
427
CREATE TABLE t1(a INT);
428
INSERT INTO t1 VALUES(1);
429
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
430
INSERT INTO t2(a,b) VALUES
431
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
432
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
433
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
434
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
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),
450
LOCK TABLES t1 WRITE, t2 WRITE;
451
INSERT INTO t2(a,b) VALUES(1,2);
452
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
added
removed
mysql-test/r/index_merge.result
