« back to all changes in this revision
Viewing changes to tests/r/index_merge_myisam.result
- browse files at revision 1063.9.3
- compare with another revision
- compare with revision 971.1.39
- download diff
- download tarball
- view history from revision 1063.9.3
- Committer: Brian Aker
- Date: 2009-06-16 00:53:22 UTC
- mto: This revision was merged to the branch mainline in revision 1094.
- Revision ID: brian@gaz-20090616005322-w0ode4jul9z8s2y9
Partial fix for tests for tmp
- files removed:
- tests/r/drizzlecheck.result
- files modified:
- tests/include/mix1.inc
added
removed
tests/r/index_merge_myisam.result
1
#---------------- ROR-index_merge tests -----------------------
2
SET SESSION STORAGE_ENGINE = MyISAM;
3
drop table if exists t0,t1,t2;
4
create table t1
5
(
6
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
7
st_a int not null default 0,
8
swt1a int not null default 0,
9
swt2a int not null default 0,
10
st_b int not null default 0,
11
swt1b int not null default 0,
12
swt2b int not null default 0,
13
/* fields/keys for row retrieval tests */
14
key1 int,
15
key2 int,
16
key3 int,
17
key4 int,
18
/* make rows much bigger then keys */
19
filler1 char (200),
20
filler2 char (200),
21
filler3 char (200),
22
filler4 char (200),
23
filler5 char (200),
24
filler6 char (200),
25
/* order of keys is important */
26
key sta_swt12a(st_a,swt1a,swt2a),
27
key sta_swt1a(st_a,swt1a),
28
key sta_swt2a(st_a,swt2a),
29
key sta_swt21a(st_a,swt2a,swt1a),
30
key st_a(st_a),
31
key stb_swt1a_2b(st_b,swt1b,swt2a),
32
key stb_swt1b(st_b,swt1b),
33
key st_b(st_b),
34
key(key1),
35
key(key2),
36
key(key3),
37
key(key4)
38
) ;
39
create table t0 as select * from t1;
40
# Printing of many insert into t0 values (....) disabled.
41
alter table t1 disable keys;
42
# Printing of many insert into t1 select .... from t0 disabled.
43
# Printing of many insert into t1 (...) values (....) disabled.
44
alter table t1 enable keys;
45
select count(*) from t1;
46
count(*)
47
64801
48
explain select key1,key2 from t1 where key1=100 and key2=100;
49
id select_type table type possible_keys key key_len ref rows Extra
50
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
51
select key1,key2 from t1 where key1=100 and key2=100;
52
key1 key2
53
100 100
54
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
55
id select_type table type possible_keys key key_len ref rows Extra
56
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 6 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
57
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
58
key1 key2 key3 key4 filler1
59
100 100 100 100 key1-key2-key3-key4
60
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
61
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
62
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
63
id select_type table type possible_keys key key_len ref rows Extra
64
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where
65
select key1,key2,filler1 from t1 where key1=100 and key2=100;
66
key1 key2 filler1
67
100 100 key1-key2-key3-key4
68
100 100 key1-key2
69
explain select key1,key2 from t1 where key1=100 and key2=100;
70
id select_type table type possible_keys key key_len ref rows Extra
71
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
72
select key1,key2 from t1 where key1=100 and key2=100;
73
key1 key2
74
100 100
75
100 100
76
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
77
id select_type table type possible_keys key key_len ref rows Extra
78
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 6 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
79
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
80
key1 key2 key3 key4
81
100 100 100 100
82
100 100 -1 -1
83
-1 -1 100 100
84
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
85
id select_type table type possible_keys key key_len ref rows Extra
86
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 6 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
87
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
88
key1 key2 key3 key4 filler1
89
100 100 100 100 key1-key2-key3-key4
90
100 100 -1 -1 key1-key2
91
-1 -1 100 100 key4-key3
92
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
93
id select_type table type possible_keys key key_len ref rows Extra
94
1 SIMPLE t1 index_merge key1,key2,key3 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where
95
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
96
key1 key2 key3
97
100 100 100
98
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
99
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
100
id select_type table type possible_keys key key_len ref rows Extra
101
1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 5 Using union(intersect(key1,key2),key3); Using where
102
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
103
key1 key2 key3 key4 filler1
104
100 100 100 100 key1-key2-key3-key4
105
100 100 -1 -1 key1-key2
106
101 101 101 101 key1234-101
107
select key1,key2, filler1 from t1 where key1=100 and key2=100;
108
key1 key2 filler1
109
100 100 key1-key2-key3-key4
110
100 100 key1-key2
111
update t1 set filler1='to be deleted' where key1=100 and key2=100;
112
update t1 set key1=200,key2=200 where key1=100 and key2=100;
113
delete from t1 where key1=200 and key2=200;
114
select key1,key2,filler1 from t1 where key2=100 and key2=200;
115
key1 key2 filler1
116
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
117
id select_type table type possible_keys key key_len ref rows Extra
118
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 6 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
119
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
120
key1 key2 key3 key4 filler1
121
-1 -1 100 100 key4-key3
122
delete from t1 where key3=100 and key4=100;
123
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
124
id select_type table type possible_keys key key_len ref rows Extra
125
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 6 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
126
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
127
key1 key2 key3 key4 filler1
128
explain select key1,key2 from t1 where key1=100 and key2=100;
129
id select_type table type possible_keys key key_len ref rows Extra
130
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 3 Using intersect(key1,key2); Using where; Using index
131
select key1,key2 from t1 where key1=100 and key2=100;
132
key1 key2
133
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
134
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
135
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
136
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
137
id select_type table type possible_keys key key_len ref rows Extra
138
1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 17 Using union(key3,intersect(key1,key2),key4); Using where
139
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
140
key1 key2 key3 key4 filler1
141
100 100 200 200 key1-key2-key3-key4-2
142
100 100 200 200 key1-key2-key3-key4-1
143
100 100 200 200 key1-key2-key3-key4-3
144
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
145
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
146
id select_type table type possible_keys key key_len ref rows Extra
147
1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 19 Using union(key3,intersect(key1,key2),key4); Using where
148
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
149
key1 key2 key3 key4 filler1
150
100 100 200 200 key1-key2-key3-key4-2
151
100 100 200 200 key1-key2-key3-key4-1
152
100 100 200 200 key1-key2-key3-key4-3
153
-1 -1 -1 200 key4
154
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
155
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
156
id select_type table type possible_keys key key_len ref rows Extra
157
1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 21 Using union(key3,intersect(key1,key2),key4); Using where
158
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
159
key1 key2 key3 key4 filler1
160
100 100 200 200 key1-key2-key3-key4-2
161
100 100 200 200 key1-key2-key3-key4-1
162
100 100 200 200 key1-key2-key3-key4-3
163
-1 -1 -1 200 key4
164
-1 -1 200 -1 key3
165
explain select * from t1 where st_a=1 and st_b=1;
166
id select_type table type possible_keys key key_len ref rows Extra
167
1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 14593 Using where
168
explain select st_a,st_b from t1 where st_a=1 and st_b=1;
169
id select_type table type possible_keys key key_len ref rows Extra
170
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 3286 Using intersect(st_a,st_b); Using where; Using index
171
explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1;
172
id select_type table type possible_keys key key_len ref rows Extra
173
1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 14593 Using where
174
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
175
id select_type table type possible_keys key key_len ref rows Extra
176
1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 979
177
explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
178
id select_type table type possible_keys key key_len ref rows Extra
179
1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1b 8 const,const 3891 Using where
180
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
181
id select_type table type possible_keys key key_len ref rows Extra
182
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 59 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
183
explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b)
184
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
185
id select_type table type possible_keys key key_len ref rows Extra
186
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
187
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b)
188
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
189
id select_type table type possible_keys key key_len ref rows Extra
190
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
191
explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b)
192
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
193
id select_type table type possible_keys key key_len ref rows Extra
194
1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 216 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where
195
explain select * from t1
196
where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1;
197
id select_type table type possible_keys key key_len ref rows Extra
198
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 59 Using intersect(sta_swt12a,stb_swt1a_2b); Using where
199
explain select * from t1
200
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
201
id select_type table type possible_keys key key_len ref rows Extra
202
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 233 Using intersect(sta_swt1a,stb_swt1b); Using where
203
explain select st_a from t1
204
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
205
id select_type table type possible_keys key key_len ref rows Extra
206
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 233 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
207
explain select st_a from t1
208
where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1;
209
id select_type table type possible_keys key key_len ref rows Extra
210
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 233 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index
211
drop table t0,t1;
212
create table t2 (
213
a char(10),
214
b char(10),
215
filler1 char(255),
216
filler2 char(255),
217
key(a(5)),
218
key(b(5))
219
);
220
select count(a) from t2 where a='BBBBBBBB';
221
count(a)
222
4
223
select count(a) from t2 where b='BBBBBBBB';
224
count(a)
225
4
226
expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
227
id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b
228
1 SIMPLE t2 ref a_or_b,a_or_b a_or_b 23 const 4 Using where
229
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
230
count(a)
231
4
232
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
233
count(a)
234
4
235
insert into t2 values ('ab', 'ab', 'uh', 'oh');
236
explain select a from t2 where a='ab';
237
id select_type table type possible_keys key key_len ref rows Extra
238
1 SIMPLE t2 ref a a 23 const 1 Using where
239
drop table t2;
240
CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
241
KEY(c1), KEY(c2), KEY(c3));
242
INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
243
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
244
INSERT INTO t1 VALUES(0,0,0);
245
CREATE TABLE t2(c1 int);
246
INSERT INTO t2 VALUES(1);
247
DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
248
SELECT * FROM t1;
249
c1 c2 c3
250
DROP TABLE t1,t2;
251
#---------------- Index merge test 2 -------------------------------------------
252
SET SESSION STORAGE_ENGINE = MyISAM;
253
drop table if exists t1,t2;
254
create table t1
255
(
256
key1 int not null,
257
key2 int not null,
258
INDEX i1(key1),
259
INDEX i2(key2)
260
);
261
explain select * from t1 where key1 < 5 or key2 > 197;
262
id select_type table type possible_keys key key_len ref rows Extra
263
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
264
select * from t1 where key1 < 5 or key2 > 197;
265
key1 key2
266
0 200
267
1 199
268
2 198
269
3 197
270
4 196
271
explain select * from t1 where key1 < 3 or key2 > 195;
272
id select_type table type possible_keys key key_len ref rows Extra
273
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
274
select * from t1 where key1 < 3 or key2 > 195;
275
key1 key2
276
0 200
277
1 199
278
2 198
279
3 197
280
4 196
281
alter table t1 add str1 char (100) not null,
282
add zeroval int not null default 0,
283
add str2 char (100) not null,
284
add str3 char (100) not null;
285
update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
286
alter table t1 add primary key (str1, zeroval, str2, str3);
287
explain select * from t1 where key1 < 5 or key2 > 197;
288
id select_type table type possible_keys key key_len ref rows Extra
289
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
290
select * from t1 where key1 < 5 or key2 > 197;
291
key1 key2 str1 zeroval str2 str3
292
0 200 aaa 0 bbb 200-0_a
293
1 199 aaa 0 bbb 199-0_A
294
2 198 aaa 0 bbb 198-1_a
295
3 197 aaa 0 bbb 197-1_A
296
4 196 aaa 0 bbb 196-2_a
297
explain select * from t1 where key1 < 3 or key2 > 195;
298
id select_type table type possible_keys key key_len ref rows Extra
299
1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where
300
select * from t1 where key1 < 3 or key2 > 195;
301
key1 key2 str1 zeroval str2 str3
302
0 200 aaa 0 bbb 200-0_a
303
1 199 aaa 0 bbb 199-0_A
304
2 198 aaa 0 bbb 198-1_a
305
3 197 aaa 0 bbb 197-1_A
306
4 196 aaa 0 bbb 196-2_a
307
drop table t1;
308
create table t1 (
309
pk integer not null auto_increment primary key,
310
key1 integer,
311
key2 integer not null,
312
filler char (200),
313
index (key1),
314
index (key2)
315
);
316
show warnings;
317
Level Code Message
318
explain select pk from t1 where key1 = 1 and key2 = 1;
319
id select_type table type possible_keys key key_len ref rows Extra
320
1 SIMPLE t1 ref key1,key2 key1 5 const 4 Using where
321
select pk from t1 where key2 = 1 and key1 = 1;
322
pk
323
26
324
27
325
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
326
pk
327
26
328
27
329
drop table t1;
330
create table t1 (
331
pk int primary key auto_increment,
332
key1a int,
333
key2a int,
334
key1b int,
335
key2b int,
336
dummy1 int,
337
dummy2 int,
338
dummy3 int,
339
dummy4 int,
340
key3a int,
341
key3b int,
342
filler1 char (200),
343
index i1(key1a, key1b),
344
index i2(key2a, key2b),
345
index i3(key3a, key3b)
346
) ENGINE=myisam;
347
create table t2 (a int);
348
insert into t2 values (0),(1),(2),(3),(4),(NULL);
349
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
350
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
351
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
352
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
353
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
354
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
355
analyze table t1;
356
Table Op Msg_type Msg_text
357
test.t1 analyze status OK
358
select count(*) from t1;
359
count(*)
360
5184
361
explain select count(*) from t1 where
362
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
363
id select_type table type possible_keys key key_len ref rows Extra
364
1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 2 Using intersect(i1,i2); Using where; Using index
365
select count(*) from t1 where
366
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
367
count(*)
368
4
369
explain select count(*) from t1 where
370
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
371
id select_type table type possible_keys key key_len ref rows Extra
372
1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 2 Using intersect(i1,i3); Using where; Using index
373
select count(*) from t1 where
374
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
375
count(*)
376
4
377
drop table t1,t2;
378
create table t1 (
379
id1 int,
380
id2 date ,
381
index idx2 (id1,id2),
382
index idx1 (id2)
383
);
384
insert into t1 values(1,'20040101'), (2,'20040102');
385
select * from t1 where id1 = 1 and id2= '20040101';
386
id1 id2
387
1 2004-01-01
388
drop table t1;
389
create table t1
390
(
391
key1 int not null,
392
key2 int not null default 0,
393
key3 int not null default 0
394
);
395
insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8);
396
set @d=8;
397
begin;
398
insert into t1 (key1) select key1+@d from t1;
399
set @d=@d*2;
400
insert into t1 (key1) select key1+@d from t1;
401
set @d=@d*2;
402
insert into t1 (key1) select key1+@d from t1;
403
set @d=@d*2;
404
insert into t1 (key1) select key1+@d from t1;
405
set @d=@d*2;
406
insert into t1 (key1) select key1+@d from t1;
407
set @d=@d*2;
408
insert into t1 (key1) select key1+@d from t1;
409
set @d=@d*2;
410
insert into t1 (key1) select key1+@d from t1;
411
set @d=@d*2;
412
commit;
413
alter table t1 add index i2(key2);
414
alter table t1 add index i3(key3);
415
update t1 set key2=key1,key3=key1;
416
explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
417
id select_type table type possible_keys key key_len ref rows Extra
418
1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where
419
select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40);
420
key1 key2 key3
421
31 31 31
422
32 32 32
423
33 33 33
424
34 34 34
425
35 35 35
426
36 36 36
427
37 37 37
428
38 38 38
429
39 39 39
430
drop table t1;
431
#---------------- 2-sweeps read Index merge test 2 -------------------------------
432
SET SESSION STORAGE_ENGINE = MyISAM;
433
drop table if exists t1;
434
create table t1 (
435
pk int primary key,
436
key1 int,
437
key2 int,
438
filler char(200),
439
filler2 char(200),
440
index(key1),
441
index(key2)
442
);
443
select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
444
pk key1 key2 filler filler2
445
10 10 10 filler-data filler-data-2
446
9 9 9 filler-data filler-data-2
447
8 8 8 filler-data filler-data-2
448
7 7 7 filler-data filler-data-2
449
6 6 6 filler-data filler-data-2
450
5 5 5 filler-data filler-data-2
451
4 4 4 filler-data filler-data-2
452
3 3 3 filler-data filler-data-2
453
2 2 2 filler-data filler-data-2
454
set @maxv=1000;
455
select * from t1 where
456
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
457
or key1=18 or key1=60;
458
pk key1 key2 filler filler2
459
1000 1000 1000 filler-data filler-data-2
460
999 999 999 filler-data filler-data-2
461
998 998 998 filler-data filler-data-2
462
997 997 997 filler-data filler-data-2
463
996 996 996 filler-data filler-data-2
464
995 995 995 filler-data filler-data-2
465
994 994 994 filler-data filler-data-2
466
993 993 993 filler-data filler-data-2
467
992 992 992 filler-data filler-data-2
468
991 991 991 filler-data filler-data-2
469
60 60 60 filler-data filler-data-2
470
54 54 54 filler-data filler-data-2
471
53 53 53 filler-data filler-data-2
472
52 52 52 filler-data filler-data-2
473
51 51 51 filler-data filler-data-2
474
50 50 50 filler-data filler-data-2
475
18 18 18 filler-data filler-data-2
476
14 14 14 filler-data filler-data-2
477
13 13 13 filler-data filler-data-2
478
12 12 12 filler-data filler-data-2
479
11 11 11 filler-data filler-data-2
480
4 4 4 filler-data filler-data-2
481
3 3 3 filler-data filler-data-2
482
2 2 2 filler-data filler-data-2
483
1 1 1 filler-data filler-data-2
484
select * from t1 where
485
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
486
or key1 < 3 or key1 > @maxv-11;
487
pk key1 key2 filler filler2
488
1000 1000 1000 filler-data filler-data-2
489
999 999 999 filler-data filler-data-2
490
998 998 998 filler-data filler-data-2
491
997 997 997 filler-data filler-data-2
492
996 996 996 filler-data filler-data-2
493
995 995 995 filler-data filler-data-2
494
994 994 994 filler-data filler-data-2
495
993 993 993 filler-data filler-data-2
496
992 992 992 filler-data filler-data-2
497
991 991 991 filler-data filler-data-2
498
990 990 990 filler-data filler-data-2
499
54 54 54 filler-data filler-data-2
500
53 53 53 filler-data filler-data-2
501
52 52 52 filler-data filler-data-2
502
51 51 51 filler-data filler-data-2
503
50 50 50 filler-data filler-data-2
504
14 14 14 filler-data filler-data-2
505
13 13 13 filler-data filler-data-2
506
12 12 12 filler-data filler-data-2
507
11 11 11 filler-data filler-data-2
508
4 4 4 filler-data filler-data-2
509
3 3 3 filler-data filler-data-2
510
2 2 2 filler-data filler-data-2
511
1 1 1 filler-data filler-data-2
512
select * from t1 where
513
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
514
or
515
(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
516
pk key1 key2 filler filler2
517
1000 1000 1000 filler-data filler-data-2
518
999 999 999 filler-data filler-data-2
519
998 998 998 filler-data filler-data-2
520
997 997 997 filler-data filler-data-2
521
996 996 996 filler-data filler-data-2
522
995 995 995 filler-data filler-data-2
523
994 994 994 filler-data filler-data-2
524
993 993 993 filler-data filler-data-2
525
992 992 992 filler-data filler-data-2
526
991 991 991 filler-data filler-data-2
527
54 54 54 filler-data filler-data-2
528
53 53 53 filler-data filler-data-2
529
52 52 52 filler-data filler-data-2
530
51 51 51 filler-data filler-data-2
531
50 50 50 filler-data filler-data-2
532
14 14 14 filler-data filler-data-2
533
13 13 13 filler-data filler-data-2
534
12 12 12 filler-data filler-data-2
535
11 11 11 filler-data filler-data-2
536
4 4 4 filler-data filler-data-2
537
3 3 3 filler-data filler-data-2
538
2 2 2 filler-data filler-data-2
539
1 1 1 filler-data filler-data-2
540
select * from t1 where
541
(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
542
or
543
(key1 < 5) or (key1 > @maxv-10);
544
pk key1 key2 filler filler2
545
1000 1000 1000 filler-data filler-data-2
546
999 999 999 filler-data filler-data-2
547
998 998 998 filler-data filler-data-2
548
997 997 997 filler-data filler-data-2
549
996 996 996 filler-data filler-data-2
550
995 995 995 filler-data filler-data-2
551
994 994 994 filler-data filler-data-2
552
993 993 993 filler-data filler-data-2
553
992 992 992 filler-data filler-data-2
554
991 991 991 filler-data filler-data-2
555
54 54 54 filler-data filler-data-2
556
53 53 53 filler-data filler-data-2
557
52 52 52 filler-data filler-data-2
558
51 51 51 filler-data filler-data-2
559
50 50 50 filler-data filler-data-2
560
14 14 14 filler-data filler-data-2
561
13 13 13 filler-data filler-data-2
562
12 12 12 filler-data filler-data-2
563
11 11 11 filler-data filler-data-2
564
4 4 4 filler-data filler-data-2
565
3 3 3 filler-data filler-data-2
566
2 2 2 filler-data filler-data-2
567
1 1 1 filler-data filler-data-2
568
drop table t1;
569
#---------------- Clustered PK ROR-index_merge tests -----------------------------
570
SET SESSION STORAGE_ENGINE = MyISAM;
571
drop table if exists t1;
572
create table t1
573
(
574
pk1 int not null,
575
pk2 int not null,
576
key1 int not null,
577
key2 int not null,
578
pktail1ok int not null,
579
pktail2ok int not null,
580
pktail3bad int not null,
581
pktail4bad int not null,
582
pktail5bad int not null,
583
pk2copy int not null,
584
badkey int not null,
585
filler1 char (200),
586
filler2 char (200),
587
key (key1),
588
key (key2),
589
/* keys with tails from CPK members */
590
key (pktail1ok, pk1),
591
key (pktail2ok, pk1, pk2),
592
key (pktail3bad, pk2, pk1),
593
key (pktail4bad, pk1, pk2copy),
594
key (pktail5bad, pk1, pk2, pk2copy),
595
primary key (pk1, pk2)
596
);
597
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
598
id select_type table type possible_keys key key_len ref rows Extra
599
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 9 Using where; Using MRR
600
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
601
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
602
1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
603
1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
604
1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
605
1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
606
1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
607
1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
608
1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
609
1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
610
1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
611
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
612
explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
613
id select_type table type possible_keys key key_len ref rows Extra
614
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
615
select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
616
pk1 pk2
617
95 59
618
95 58
619
95 57
620
95 56
621
95 55
622
95 54
623
95 53
624
95 52
625
95 51
626
95 50
627
explain select * from t1 where badkey=1 and key1=10;
628
id select_type table type possible_keys key key_len ref rows Extra
629
1 SIMPLE t1 ref key1 key1 4 const 83 Using where
630
explain select * from t1 where pk1 < 7500 and key1 = 10;
631
id select_type table type possible_keys key key_len ref rows Extra
632
1 SIMPLE t1 ref PRIMARY,key1 key1 4 const ROWS Using where
633
explain select * from t1 where pktail1ok=1 and key1=10;
634
id select_type table type possible_keys key key_len ref rows Extra
635
1 SIMPLE t1 ref key1,pktail1ok key1 4 const 83 Using where
636
explain select * from t1 where pktail2ok=1 and key1=10;
637
id select_type table type possible_keys key key_len ref rows Extra
638
1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 78 Using where
639
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
640
id select_type table type possible_keys key key_len ref rows Extra
641
1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 161 Using sort_union(pktail2ok,key1); Using where
642
explain select * from t1 where pktail3bad=1 and key1=10;
643
id select_type table type possible_keys key key_len ref rows Extra
644
1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 69 Using where
645
explain select * from t1 where pktail4bad=1 and key1=10;
646
id select_type table type possible_keys key key_len ref rows Extra
647
1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 78 Using where
648
explain select * from t1 where pktail5bad=1 and key1=10;
649
id select_type table type possible_keys key key_len ref rows Extra
650
1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 67 Using where
651
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
652
id select_type table type possible_keys key key_len ref rows Extra
653
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
654
select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
655
pk1 pk2 key1 key2
656
104 49 10 10
657
104 48 10 10
658
104 47 10 10
659
104 46 10 10
660
104 45 10 10
661
104 44 10 10
662
104 43 10 10
663
104 42 10 10
664
104 41 10 10
665
104 40 10 10
666
drop table t1;
667
create table t1
668
(
669
RUNID varchar(22),
670
SUBMITNR varchar(5),
671
ORDERNR char(1),
672
PROGRAMM varchar(8),
673
TESTID varchar(4),
674
UCCHECK char(1),
675
ETEXT varchar(80),
676
ETEXT_TYPE char(1),
677
INFO char(1),
678
SEVERITY int,
679
TADIRFLAG char(1),
680
PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
681
KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
682
);
683
update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
684
WHERE
685
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
686
`TESTID`='' AND `UCCHECK`='';
1
create table t1 (a int);
687
2
drop table t1;
Loggerhead is a web-based interface for Breezy
Version: 2.0.1