1
#---------------- Index merge test 1 -------------------------------------------
2
SET SESSION STORAGE_ENGINE = MyISAM;
3
drop table if exists t0, t1, t2, t3, t4;
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;
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 index condition; Using MRR
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
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
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
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; Using MRR
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
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
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 index condition; Using where; Using MRR
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))
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))
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))
139
key1 key2 key3 key4 key5 key6 key7 key8
146
explain select * from t0 where
147
((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
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))
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))
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))
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))
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))
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
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
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
219
index i1a (key1a, key1b),
220
index i1b (key1b, key1a),
221
index i2_1(key2, key2_1),
222
index i2_2(key2, key2_1)
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
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
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
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
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 index condition; Using MRR
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
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
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;
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;
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)
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)
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)
389
set join_buffer_size= @save_join_buffer_size;
390
drop table t0, t1, t2, t3, t4;
392
cola char(3) not null, colb char(3) not null, filler char(200),
395
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
397
Table Op Msg_type Msg_text
398
test.t1 optimize status OK
399
select count(*) from t1;
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
409
CREATE TABLE t1(a INT);
410
INSERT INTO t1 VALUES(1);
411
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
412
INSERT INTO t2(a,b) VALUES
413
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
414
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
415
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
416
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
417
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
418
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
419
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
420
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
421
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
422
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
423
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
424
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
425
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
426
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
427
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
428
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
429
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
430
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
432
LOCK TABLES t1 WRITE, t2 WRITE;
433
INSERT INTO t2(a,b) VALUES(1,2);
434
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
441
`a` int(11) DEFAULT NULL,
442
`filler` char(200) DEFAULT NULL,
443
`b` int(11) DEFAULT NULL,
446
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
447
insert into t1 values
448
(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3),
449
(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7),
450
(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1),
451
(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5),
452
(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9),
453
(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
454
(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17),
455
(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0),
456
(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4);
458
`a` int(11) DEFAULT NULL,
459
`filler` char(200) DEFAULT NULL,
460
`b` int(11) DEFAULT NULL,
461
KEY USING BTREE (`a`),
462
KEY USING BTREE (`b`)
463
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
464
insert into t2 select * from t1;
465
must use sort-union rather than union:
466
explain select * from t1 where a=4 or b=4;
467
id select_type table type possible_keys key key_len ref rows Extra
468
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where
469
select * from t1 where a=4 or b=4;
478
select * from t1 ignore index(a,b) where a=4 or b=4;
487
must use union, not sort-union:
488
explain select * from t2 where a=4 or b=4;
489
id select_type table type possible_keys key key_len ref rows Extra
490
1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where
491
select * from t2 where a=4 or b=4;
501
#---------------- ROR-index_merge tests -----------------------
502
SET SESSION STORAGE_ENGINE = MyISAM;
503
drop table if exists t0,t1,t2;
506
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
507
st_a int not null default 0,
508
swt1a int not null default 0,
509
swt2a int not null default 0,
510
st_b int not null default 0,
511
swt1b int not null default 0,
512
swt2b int not null default 0,
513
/* fields/keys for row retrieval tests */
518
/* make rows much bigger then keys */
525
/* order of keys is important */
526
key sta_swt12a(st_a,swt1a,swt2a),
527
key sta_swt1a(st_a,swt1a),
528
key sta_swt2a(st_a,swt2a),
529
key sta_swt21a(st_a,swt2a,swt1a),
531
key stb_swt1a_2b(st_b,swt1b,swt2a),
532
key stb_swt1b(st_b,swt1b),
539
create table t0 as select * from t1;
540
# Printing of many insert into t0 values (....) disabled.
541
alter table t1 disable keys;
542
# Printing of many insert into t1 select .... from t0 disabled.
543
# Printing of many insert into t1 (...) values (....) disabled.
544
alter table t1 enable keys;
545
select count(*) from t1;
548
explain select key1,key2 from t1 where key1=100 and key2=100;
549
id select_type table type possible_keys key key_len ref rows Extra
550
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index
551
select key1,key2 from t1 where key1=100 and key2=100;
554
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
555
id select_type table type possible_keys key key_len ref rows Extra
556
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
557
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
558
key1 key2 key3 key4 filler1
559
100 100 100 100 key1-key2-key3-key4
560
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
561
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
562
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
563
id select_type table type possible_keys key key_len ref rows Extra
564
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where
565
select key1,key2,filler1 from t1 where key1=100 and key2=100;
567
100 100 key1-key2-key3-key4
569
explain select key1,key2 from t1 where key1=100 and key2=100;
570
id select_type table type possible_keys key key_len ref rows Extra
571
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index
572
select key1,key2 from t1 where key1=100 and key2=100;
576
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
577
id select_type table type possible_keys key key_len ref rows Extra
578
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
579
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
584
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
585
id select_type table type possible_keys key key_len ref rows Extra
586
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
587
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
588
key1 key2 key3 key4 filler1
589
100 100 100 100 key1-key2-key3-key4
590
100 100 -1 -1 key1-key2
591
-1 -1 100 100 key4-key3
592
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
593
id select_type table type possible_keys key key_len ref rows Extra
594
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
595
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
598
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
599
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
600
id select_type table type possible_keys key key_len ref rows Extra
601
1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 83 Using union(intersect(key1,key2),key3); Using where
602
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
603
key1 key2 key3 key4 filler1
604
100 100 100 100 key1-key2-key3-key4
605
100 100 -1 -1 key1-key2
606
101 101 101 101 key1234-101
607
select key1,key2, filler1 from t1 where key1=100 and key2=100;
609
100 100 key1-key2-key3-key4
611
update t1 set filler1='to be deleted' where key1=100 and key2=100;
612
update t1 set key1=200,key2=200 where key1=100 and key2=100;
613
delete from t1 where key1=200 and key2=200;
614
select key1,key2,filler1 from t1 where key2=100 and key2=200;
616
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
617
id select_type table type possible_keys key key_len ref rows Extra
618
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
619
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
620
key1 key2 key3 key4 filler1
621
-1 -1 100 100 key4-key3
622
delete from t1 where key3=100 and key4=100;
623
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
624
id select_type table type possible_keys key key_len ref rows Extra
625
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
626
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
627
key1 key2 key3 key4 filler1
628
explain select key1,key2 from t1 where key1=100 and key2=100;
629
id select_type table type possible_keys key key_len ref rows Extra
630
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 76 Using intersect(key1,key2); Using where; Using index
631
select key1,key2 from t1 where key1=100 and key2=100;
633
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
634
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
635
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
636
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
637
id select_type table type possible_keys key key_len ref rows Extra
638
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
639
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
640
key1 key2 key3 key4 filler1
641
100 100 200 200 key1-key2-key3-key4-3
642
100 100 200 200 key1-key2-key3-key4-2
643
100 100 200 200 key1-key2-key3-key4-1
644
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
645
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
646
id select_type table type possible_keys key key_len ref rows Extra
647
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
648
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
649
key1 key2 key3 key4 filler1
650
100 100 200 200 key1-key2-key3-key4-3
651
100 100 200 200 key1-key2-key3-key4-2
652
100 100 200 200 key1-key2-key3-key4-1
654
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
655
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
656
id select_type table type possible_keys key key_len ref rows Extra
657
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
658
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
659
key1 key2 key3 key4 filler1
660
100 100 200 200 key1-key2-key3-key4-3
661
100 100 200 200 key1-key2-key3-key4-2
662
100 100 200 200 key1-key2-key3-key4-1
665
explain select * from t1 where st_a=1 and st_b=1;
666
id select_type table type possible_keys key key_len ref rows Extra
667
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
668
explain select st_a,st_b from t1 where st_a=1 and st_b=1;
669
id select_type table type possible_keys key key_len ref rows Extra
670
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
671
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
672
id select_type table type possible_keys key key_len ref rows Extra
673
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
674
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
675
id select_type table type possible_keys key key_len ref rows Extra
676
1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971
677
explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
678
id select_type table type possible_keys key key_len ref rows Extra
679
1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3879 Using where
680
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
681
id select_type table type possible_keys key key_len ref rows Extra
682
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
683
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
684
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
685
id select_type table type possible_keys key key_len ref rows Extra
686
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
687
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
688
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
689
id select_type table type possible_keys key key_len ref rows Extra
690
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
691
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
692
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
693
id select_type table type possible_keys key key_len ref rows Extra
694
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
695
explain select * from t1
696
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
697
id select_type table type possible_keys key key_len ref rows Extra
698
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
699
explain select * from t1
700
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
701
id select_type table type possible_keys key key_len ref rows Extra
702
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
703
explain select st_a from t1
704
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
705
id select_type table type possible_keys key key_len ref rows Extra
706
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
707
explain select st_a from t1
708
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
709
id select_type table type possible_keys key key_len ref rows Extra
710
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
720
select count(a) from t2 where a='BBBBBBBB';
723
select count(a) from t2 where b='BBBBBBBB';
726
expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
727
id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b
728
1 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 4 Using where
729
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
732
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
735
insert into t2 values ('ab', 'ab', 'uh', 'oh');
736
explain select a from t2 where a='ab';
737
id select_type table type possible_keys key key_len ref rows Extra
738
1 SIMPLE t2 ref a a 6 const 1 Using where
740
CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
741
KEY(c1), KEY(c2), KEY(c3));
742
INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
743
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
744
INSERT INTO t1 VALUES(0,0,0);
745
CREATE TABLE t2(c1 int);
746
INSERT INTO t2 VALUES(1);
747
DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
751
#---------------- Index merge test 2 -------------------------------------------
752
SET SESSION STORAGE_ENGINE = MyISAM;
753
drop table if exists t1,t2;
761
explain select * from t1 where key1 < 5 or key2 > 197;
762
id select_type table type possible_keys key key_len ref rows Extra
763
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
764
select * from t1 where key1 < 5 or key2 > 197;
771
explain select * from t1 where key1 < 3 or key2 > 195;
772
id select_type table type possible_keys key key_len ref rows Extra
773
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
774
select * from t1 where key1 < 3 or key2 > 195;
781
alter table t1 add str1 char (255) not null,
782
add zeroval int not null default 0,
783
add str2 char (255) not null,
784
add str3 char (255) not null;
785
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
786
alter table t1 add primary key (str1, zeroval, str2, str3);
787
explain select * from t1 where key1 < 5 or key2 > 197;
788
id select_type table type possible_keys key key_len ref rows Extra
789
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
790
select * from t1 where key1 < 5 or key2 > 197;
791
key1 key2 str1 zeroval str2 str3
792
0 200 aaa 0 bbb 200-0_a
793
1 199 aaa 0 bbb 199-0_A
794
2 198 aaa 0 bbb 198-1_a
795
3 197 aaa 0 bbb 197-1_A
796
4 196 aaa 0 bbb 196-2_a
797
explain select * from t1 where key1 < 3 or key2 > 195;
798
id select_type table type possible_keys key key_len ref rows Extra
799
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
800
select * from t1 where key1 < 3 or key2 > 195;
801
key1 key2 str1 zeroval str2 str3
802
0 200 aaa 0 bbb 200-0_a
803
1 199 aaa 0 bbb 199-0_A
804
2 198 aaa 0 bbb 198-1_a
805
3 197 aaa 0 bbb 197-1_A
806
4 196 aaa 0 bbb 196-2_a
809
pk integer not null auto_increment primary key,
811
key2 integer not null,
818
explain select pk from t1 where key1 = 1 and key2 = 1;
819
id select_type table type possible_keys key key_len ref rows Extra
820
1 SIMPLE t1 ref key1,key2 key1 5 const 4 Using where
821
select pk from t1 where key2 = 1 and key1 = 1;
825
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
831
pk int primary key auto_increment,
843
index i1(key1a, key1b),
844
index i2(key2a, key2b),
845
index i3(key3a, key3b)
847
create table t2 (a int);
848
insert into t2 values (0),(1),(2),(3),(4),(NULL);
849
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
850
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
851
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
852
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
853
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
854
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
856
Table Op Msg_type Msg_text
857
test.t1 analyze status OK
858
select count(*) from t1;
861
explain select count(*) from t1 where
862
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
863
id select_type table type possible_keys key key_len ref rows Extra
864
1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 2 Using intersect(i1,i2); Using where; Using index
865
select count(*) from t1 where
866
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
869
explain select count(*) from t1 where
870
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
871
id select_type table type possible_keys key key_len ref rows Extra
872
1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 2 Using intersect(i1,i3); Using where; Using index
873
select count(*) from t1 where
874
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
881
index idx2 (id1,id2),
884
insert into t1 values(1,'20040101'), (2,'20040102');
885
select * from t1 where id1 = 1 and id2= '20040101';
892
key2 int not null default 0,
893
key3 int not null default 0
895
insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
897
insert into t1 (key1) select key1+@d from t1;
899
insert into t1 (key1) select key1+@d from t1;
901
insert into t1 (key1) select key1+@d from t1;
903
insert into t1 (key1) select key1+@d from t1;
905
insert into t1 (key1) select key1+@d from t1;
907
insert into t1 (key1) select key1+@d from t1;
909
insert into t1 (key1) select key1+@d from t1;
911
alter table t1 add index i2(key2);
912
alter table t1 add index i3(key3);
913
update t1 set key2=key1,key3=key1;
914
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
915
id select_type table type possible_keys key key_len ref rows Extra
916
1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where
917
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
929
#---------------- 2-sweeps read Index merge test 2 -------------------------------
930
SET SESSION STORAGE_ENGINE = MyISAM;
931
drop table if exists t1;
941
select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
942
pk key1 key2 filler filler2
943
10 10 10 filler-data filler-data-2
944
9 9 9 filler-data filler-data-2
945
8 8 8 filler-data filler-data-2
946
7 7 7 filler-data filler-data-2
947
6 6 6 filler-data filler-data-2
948
5 5 5 filler-data filler-data-2
949
4 4 4 filler-data filler-data-2
950
3 3 3 filler-data filler-data-2
951
2 2 2 filler-data filler-data-2
953
select * from t1 where
954
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
955
or key1=18 or key1=60;
956
pk key1 key2 filler filler2
957
1000 1000 1000 filler-data filler-data-2
958
999 999 999 filler-data filler-data-2
959
998 998 998 filler-data filler-data-2
960
997 997 997 filler-data filler-data-2
961
996 996 996 filler-data filler-data-2
962
995 995 995 filler-data filler-data-2
963
994 994 994 filler-data filler-data-2
964
993 993 993 filler-data filler-data-2
965
992 992 992 filler-data filler-data-2
966
991 991 991 filler-data filler-data-2
967
60 60 60 filler-data filler-data-2
968
54 54 54 filler-data filler-data-2
969
53 53 53 filler-data filler-data-2
970
52 52 52 filler-data filler-data-2
971
51 51 51 filler-data filler-data-2
972
50 50 50 filler-data filler-data-2
973
18 18 18 filler-data filler-data-2
974
14 14 14 filler-data filler-data-2
975
13 13 13 filler-data filler-data-2
976
12 12 12 filler-data filler-data-2
977
11 11 11 filler-data filler-data-2
978
4 4 4 filler-data filler-data-2
979
3 3 3 filler-data filler-data-2
980
2 2 2 filler-data filler-data-2
981
1 1 1 filler-data filler-data-2
982
select * from t1 where
983
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
984
or key1 < 3 or key1 > @maxv-11;
985
pk key1 key2 filler filler2
986
1000 1000 1000 filler-data filler-data-2
987
999 999 999 filler-data filler-data-2
988
998 998 998 filler-data filler-data-2
989
997 997 997 filler-data filler-data-2
990
996 996 996 filler-data filler-data-2
991
995 995 995 filler-data filler-data-2
992
994 994 994 filler-data filler-data-2
993
993 993 993 filler-data filler-data-2
994
992 992 992 filler-data filler-data-2
995
991 991 991 filler-data filler-data-2
996
990 990 990 filler-data filler-data-2
997
54 54 54 filler-data filler-data-2
998
53 53 53 filler-data filler-data-2
999
52 52 52 filler-data filler-data-2
1000
51 51 51 filler-data filler-data-2
1001
50 50 50 filler-data filler-data-2
1002
14 14 14 filler-data filler-data-2
1003
13 13 13 filler-data filler-data-2
1004
12 12 12 filler-data filler-data-2
1005
11 11 11 filler-data filler-data-2
1006
4 4 4 filler-data filler-data-2
1007
3 3 3 filler-data filler-data-2
1008
2 2 2 filler-data filler-data-2
1009
1 1 1 filler-data filler-data-2
1010
select * from t1 where
1011
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
1013
(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
1014
pk key1 key2 filler filler2
1015
1000 1000 1000 filler-data filler-data-2
1016
999 999 999 filler-data filler-data-2
1017
998 998 998 filler-data filler-data-2
1018
997 997 997 filler-data filler-data-2
1019
996 996 996 filler-data filler-data-2
1020
995 995 995 filler-data filler-data-2
1021
994 994 994 filler-data filler-data-2
1022
993 993 993 filler-data filler-data-2
1023
992 992 992 filler-data filler-data-2
1024
991 991 991 filler-data filler-data-2
1025
54 54 54 filler-data filler-data-2
1026
53 53 53 filler-data filler-data-2
1027
52 52 52 filler-data filler-data-2
1028
51 51 51 filler-data filler-data-2
1029
50 50 50 filler-data filler-data-2
1030
14 14 14 filler-data filler-data-2
1031
13 13 13 filler-data filler-data-2
1032
12 12 12 filler-data filler-data-2
1033
11 11 11 filler-data filler-data-2
1034
4 4 4 filler-data filler-data-2
1035
3 3 3 filler-data filler-data-2
1036
2 2 2 filler-data filler-data-2
1037
1 1 1 filler-data filler-data-2
1038
select * from t1 where
1039
(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
1041
(key1 < 5) or (key1 > @maxv-10);
1042
pk key1 key2 filler filler2
1043
1000 1000 1000 filler-data filler-data-2
1044
999 999 999 filler-data filler-data-2
1045
998 998 998 filler-data filler-data-2
1046
997 997 997 filler-data filler-data-2
1047
996 996 996 filler-data filler-data-2
1048
995 995 995 filler-data filler-data-2
1049
994 994 994 filler-data filler-data-2
1050
993 993 993 filler-data filler-data-2
1051
992 992 992 filler-data filler-data-2
1052
991 991 991 filler-data filler-data-2
1053
54 54 54 filler-data filler-data-2
1054
53 53 53 filler-data filler-data-2
1055
52 52 52 filler-data filler-data-2
1056
51 51 51 filler-data filler-data-2
1057
50 50 50 filler-data filler-data-2
1058
14 14 14 filler-data filler-data-2
1059
13 13 13 filler-data filler-data-2
1060
12 12 12 filler-data filler-data-2
1061
11 11 11 filler-data filler-data-2
1062
4 4 4 filler-data filler-data-2
1063
3 3 3 filler-data filler-data-2
1064
2 2 2 filler-data filler-data-2
1065
1 1 1 filler-data filler-data-2
1067
#---------------- Clustered PK ROR-index_merge tests -----------------------------
1068
SET SESSION STORAGE_ENGINE = MyISAM;
1069
drop table if exists t1;
1076
pktail1ok int not null,
1077
pktail2ok int not null,
1078
pktail3bad int not null,
1079
pktail4bad int not null,
1080
pktail5bad int not null,
1081
pk2copy int not null,
1082
badkey int not null,
1087
/* keys with tails from CPK members */
1088
key (pktail1ok, pk1),
1089
key (pktail2ok, pk1, pk2),
1090
key (pktail3bad, pk2, pk1),
1091
key (pktail4bad, pk1, pk2copy),
1092
key (pktail5bad, pk1, pk2, pk2copy),
1093
primary key (pk1, pk2)
1095
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
1096
id select_type table type possible_keys key key_len ref rows Extra
1097
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where; Using MRR
1098
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
1099
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
1100
1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
1101
1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
1102
1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
1103
1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
1104
1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
1105
1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
1106
1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
1107
1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
1108
1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
1109
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
1110
explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
1111
id select_type table type possible_keys key key_len ref rows Extra
1112
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
1113
select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
1125
explain select * from t1 where badkey=1 and key1=10;
1126
id select_type table type possible_keys key key_len ref rows Extra
1127
1 SIMPLE t1 ref key1 key1 4 const 91 Using where
1128
explain select * from t1 where pk1 < 7500 and key1 = 10;
1129
id select_type table type possible_keys key key_len ref rows Extra
1130
1 SIMPLE t1 ref PRIMARY,key1 key1 4 const ROWS Using where
1131
explain select * from t1 where pktail1ok=1 and key1=10;
1132
id select_type table type possible_keys key key_len ref rows Extra
1133
1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where
1134
explain select * from t1 where pktail2ok=1 and key1=10;
1135
id select_type table type possible_keys key key_len ref rows Extra
1136
1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where
1137
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
1138
id select_type table type possible_keys key key_len ref rows Extra
1139
1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where
1140
explain select * from t1 where pktail3bad=1 and key1=10;
1141
id select_type table type possible_keys key key_len ref rows Extra
1142
1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where
1143
explain select * from t1 where pktail4bad=1 and key1=10;
1144
id select_type table type possible_keys key key_len ref rows Extra
1145
1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where
1146
explain select * from t1 where pktail5bad=1 and key1=10;
1147
id select_type table type possible_keys key key_len ref rows Extra
1148
1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 70 Using where
1149
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
1150
id select_type table type possible_keys key key_len ref rows Extra
1151
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
1152
select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
1168
SUBMITNR varchar(5),
1170
PROGRAMM varchar(8),
1176
SEVERITY tinyint(3),
1178
PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
1179
KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
1180
) DEFAULT CHARSET=latin1;
1181
update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
1183
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
1184
`TESTID`='' AND `UCCHECK`='';
added
removed
mysql-test/r/index_merge_myisam.result
