1
#---------------- Index merge test 2 -------------------------------------------
2
SET SESSION STORAGE_ENGINE = InnoDB;
3
drop table if exists t1,t2;
11
explain select * from t1 where key1 < 5 or key2 > 197;
12
id select_type table type possible_keys key key_len ref rows Extra
13
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
14
select * from t1 where key1 < 5 or key2 > 197;
21
explain select * from t1 where key1 < 3 or key2 > 195;
22
id select_type table type possible_keys key key_len ref rows Extra
23
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
24
select * from t1 where key1 < 3 or key2 > 195;
31
alter table t1 add str1 char (100) not null,
32
add zeroval int not null default 0,
33
add str2 char (100) not null,
34
add str3 char (100) not null;
35
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
36
alter table t1 add primary key (str1, zeroval, str2, str3);
37
explain select * from t1 where key1 < 5 or key2 > 197;
38
id select_type table type possible_keys key key_len ref rows Extra
39
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
40
select * from t1 where key1 < 5 or key2 > 197;
41
key1 key2 str1 zeroval str2 str3
42
4 196 aaa 0 bbb 196-2_a
43
3 197 aaa 0 bbb 197-1_A
44
2 198 aaa 0 bbb 198-1_a
45
1 199 aaa 0 bbb 199-0_A
46
0 200 aaa 0 bbb 200-0_a
47
explain select * from t1 where key1 < 3 or key2 > 195;
48
id select_type table type possible_keys key key_len ref rows Extra
49
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where
50
select * from t1 where key1 < 3 or key2 > 195;
51
key1 key2 str1 zeroval str2 str3
52
4 196 aaa 0 bbb 196-2_a
53
3 197 aaa 0 bbb 197-1_A
54
2 198 aaa 0 bbb 198-1_a
55
1 199 aaa 0 bbb 199-0_A
56
0 200 aaa 0 bbb 200-0_a
59
pk integer not null auto_increment primary key,
61
key2 integer not null,
68
explain select pk from t1 where key1 = 1 and key2 = 1;
69
id select_type table type possible_keys key key_len ref rows Extra
70
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,4 NULL 1 Using intersect(key1,key2); Using where; Using index
71
select pk from t1 where key2 = 1 and key1 = 1;
75
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
81
pk int primary key auto_increment,
93
index i1(key1a, key1b),
94
index i2(key2a, key2b),
95
index i3(key3a, key3b)
97
create table t2 (a int);
98
insert into t2 values (0),(1),(2),(3),(4),(NULL);
99
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
100
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
101
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
102
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
103
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
104
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
106
Table Op Msg_type Msg_text
107
test.t1 analyze status OK
108
select count(*) from t1;
111
explain select count(*) from t1 where
112
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
113
id select_type table type possible_keys key key_len ref rows Extra
114
1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL # Using intersect(i1,i2); Using where; Using index
115
select count(*) from t1 where
116
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
119
explain select count(*) from t1 where
120
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
121
id select_type table type possible_keys key key_len ref rows Extra
122
1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL # Using intersect(i1,i3); Using where; Using index
123
select count(*) from t1 where
124
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
131
index idx2 (id1,id2),
134
insert into t1 values(1,'20040101'), (2,'20040102');
135
select * from t1 where id1 = 1 and id2= '20040101';
142
key2 int not null default 0,
143
key3 int not null default 0
145
insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
148
insert into t1 (key1) select key1+@d from t1;
150
insert into t1 (key1) select key1+@d from t1;
152
insert into t1 (key1) select key1+@d from t1;
154
insert into t1 (key1) select key1+@d from t1;
156
insert into t1 (key1) select key1+@d from t1;
158
insert into t1 (key1) select key1+@d from t1;
160
insert into t1 (key1) select key1+@d from t1;
163
alter table t1 add index i2(key2);
164
alter table t1 add index i3(key3);
165
update t1 set key2=key1,key3=key1;
166
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
167
id select_type table type possible_keys key key_len ref rows Extra
168
1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 9 Using sort_union(i3,i2); Using where
169
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
181
#---------------- 2-sweeps read Index merge test 2 -------------------------------
182
SET SESSION STORAGE_ENGINE = InnoDB;
183
drop table if exists t1;
193
select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
194
pk key1 key2 filler filler2
195
2 2 2 filler-data filler-data-2
196
3 3 3 filler-data filler-data-2
197
9 9 9 filler-data filler-data-2
198
10 10 10 filler-data filler-data-2
199
4 4 4 filler-data filler-data-2
200
5 5 5 filler-data filler-data-2
201
6 6 6 filler-data filler-data-2
202
7 7 7 filler-data filler-data-2
203
8 8 8 filler-data filler-data-2
205
select * from t1 where
206
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
207
or key1=18 or key1=60;
208
pk key1 key2 filler filler2
209
18 18 18 filler-data filler-data-2
210
60 60 60 filler-data filler-data-2
211
1 1 1 filler-data filler-data-2
212
2 2 2 filler-data filler-data-2
213
3 3 3 filler-data filler-data-2
214
4 4 4 filler-data filler-data-2
215
11 11 11 filler-data filler-data-2
216
12 12 12 filler-data filler-data-2
217
13 13 13 filler-data filler-data-2
218
14 14 14 filler-data filler-data-2
219
50 50 50 filler-data filler-data-2
220
51 51 51 filler-data filler-data-2
221
52 52 52 filler-data filler-data-2
222
53 53 53 filler-data filler-data-2
223
54 54 54 filler-data filler-data-2
224
991 991 991 filler-data filler-data-2
225
992 992 992 filler-data filler-data-2
226
993 993 993 filler-data filler-data-2
227
994 994 994 filler-data filler-data-2
228
995 995 995 filler-data filler-data-2
229
996 996 996 filler-data filler-data-2
230
997 997 997 filler-data filler-data-2
231
998 998 998 filler-data filler-data-2
232
999 999 999 filler-data filler-data-2
233
1000 1000 1000 filler-data filler-data-2
234
select * from t1 where
235
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
236
or key1 < 3 or key1 > @maxv-11;
237
pk key1 key2 filler filler2
238
990 990 990 filler-data filler-data-2
239
1 1 1 filler-data filler-data-2
240
2 2 2 filler-data filler-data-2
241
3 3 3 filler-data filler-data-2
242
4 4 4 filler-data filler-data-2
243
11 11 11 filler-data filler-data-2
244
12 12 12 filler-data filler-data-2
245
13 13 13 filler-data filler-data-2
246
14 14 14 filler-data filler-data-2
247
50 50 50 filler-data filler-data-2
248
51 51 51 filler-data filler-data-2
249
52 52 52 filler-data filler-data-2
250
53 53 53 filler-data filler-data-2
251
54 54 54 filler-data filler-data-2
252
991 991 991 filler-data filler-data-2
253
992 992 992 filler-data filler-data-2
254
993 993 993 filler-data filler-data-2
255
994 994 994 filler-data filler-data-2
256
995 995 995 filler-data filler-data-2
257
996 996 996 filler-data filler-data-2
258
997 997 997 filler-data filler-data-2
259
998 998 998 filler-data filler-data-2
260
999 999 999 filler-data filler-data-2
261
1000 1000 1000 filler-data filler-data-2
262
select * from t1 where
263
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
265
(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
266
pk key1 key2 filler filler2
267
1 1 1 filler-data filler-data-2
268
2 2 2 filler-data filler-data-2
269
3 3 3 filler-data filler-data-2
270
4 4 4 filler-data filler-data-2
271
11 11 11 filler-data filler-data-2
272
12 12 12 filler-data filler-data-2
273
13 13 13 filler-data filler-data-2
274
14 14 14 filler-data filler-data-2
275
50 50 50 filler-data filler-data-2
276
51 51 51 filler-data filler-data-2
277
52 52 52 filler-data filler-data-2
278
53 53 53 filler-data filler-data-2
279
54 54 54 filler-data filler-data-2
280
991 991 991 filler-data filler-data-2
281
992 992 992 filler-data filler-data-2
282
993 993 993 filler-data filler-data-2
283
994 994 994 filler-data filler-data-2
284
995 995 995 filler-data filler-data-2
285
996 996 996 filler-data filler-data-2
286
997 997 997 filler-data filler-data-2
287
998 998 998 filler-data filler-data-2
288
999 999 999 filler-data filler-data-2
289
1000 1000 1000 filler-data filler-data-2
290
select * from t1 where
291
(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
293
(key1 < 5) or (key1 > @maxv-10);
294
pk key1 key2 filler filler2
295
1 1 1 filler-data filler-data-2
296
2 2 2 filler-data filler-data-2
297
3 3 3 filler-data filler-data-2
298
4 4 4 filler-data filler-data-2
299
991 991 991 filler-data filler-data-2
300
992 992 992 filler-data filler-data-2
301
993 993 993 filler-data filler-data-2
302
994 994 994 filler-data filler-data-2
303
995 995 995 filler-data filler-data-2
304
996 996 996 filler-data filler-data-2
305
997 997 997 filler-data filler-data-2
306
998 998 998 filler-data filler-data-2
307
999 999 999 filler-data filler-data-2
308
1000 1000 1000 filler-data filler-data-2
309
11 11 11 filler-data filler-data-2
310
12 12 12 filler-data filler-data-2
311
13 13 13 filler-data filler-data-2
312
14 14 14 filler-data filler-data-2
313
50 50 50 filler-data filler-data-2
314
51 51 51 filler-data filler-data-2
315
52 52 52 filler-data filler-data-2
316
53 53 53 filler-data filler-data-2
317
54 54 54 filler-data filler-data-2
319
#---------------- Clustered PK ROR-index_merge tests -----------------------------
320
SET SESSION STORAGE_ENGINE = InnoDB;
321
drop table if exists t1;
328
pktail1ok int not null,
329
pktail2ok int not null,
330
pktail3bad int not null,
331
pktail4bad int not null,
332
pktail5bad int not null,
333
pk2copy int not null,
339
/* keys with tails from CPK members */
340
key (pktail1ok, pk1),
341
key (pktail2ok, pk1, pk2),
342
key (pktail3bad, pk2, pk1),
343
key (pktail4bad, pk1, pk2copy),
344
key (pktail5bad, pk1, pk2, pk2copy),
345
primary key (pk1, pk2)
347
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
348
id select_type table type possible_keys key key_len ref rows Extra
349
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where
350
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
351
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
352
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
353
1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
354
1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
355
1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
356
1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
357
1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
358
1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
359
1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
360
1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
361
1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
362
explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
363
id select_type table type possible_keys key key_len ref rows Extra
364
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index
365
select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
377
explain select * from t1 where badkey=1 and key1=10;
378
id select_type table type possible_keys key key_len ref rows Extra
379
1 SIMPLE t1 ref key1 key1 4 const 100 Using where
380
explain select * from t1 where pk1 < 7500 and key1 = 10;
381
id select_type table type possible_keys key key_len ref rows Extra
382
1 SIMPLE t1 index_merge PRIMARY,key1 key1,PRIMARY 4,4 NULL ROWS Using intersect(key1,PRIMARY); Using where
383
explain select * from t1 where pktail1ok=1 and key1=10;
384
id select_type table type possible_keys key key_len ref rows Extra
385
1 SIMPLE t1 index_merge key1,pktail1ok key1,pktail1ok 4,4 NULL 1 Using intersect(key1,pktail1ok); Using where
386
explain select * from t1 where pktail2ok=1 and key1=10;
387
id select_type table type possible_keys key key_len ref rows Extra
388
1 SIMPLE t1 index_merge key1,pktail2ok key1,pktail2ok 4,4 NULL 1 Using intersect(key1,pktail2ok); Using where
389
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
390
id select_type table type possible_keys key key_len ref rows Extra
391
1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 199 Using sort_union(pktail2ok,key1); Using where
392
explain select * from t1 where pktail3bad=1 and key1=10;
393
id select_type table type possible_keys key key_len ref rows Extra
394
1 SIMPLE t1 ref key1,pktail3bad key1 4 const 100 Using where
395
explain select * from t1 where pktail4bad=1 and key1=10;
396
id select_type table type possible_keys key key_len ref rows Extra
397
1 SIMPLE t1 ref key1,pktail4bad key1 4 const 100 Using where
398
explain select * from t1 where pktail5bad=1 and key1=10;
399
id select_type table type possible_keys key key_len ref rows Extra
400
1 SIMPLE t1 ref key1,pktail5bad key1 4 const 100 Using where
401
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
402
id select_type table type possible_keys key key_len ref rows Extra
403
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where; Using index
404
select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
430
PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
431
KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
433
update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
435
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
436
`TESTID`='' AND `UCCHECK`='';