1
drop table if exists t1, t2, t3;
4
type int DEFAULT '0' NOT NULL,
5
event_id int DEFAULT '0' NOT NULL,
6
PRIMARY KEY (event_date,type,event_id)
8
INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25),
9
('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26),
10
('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45),
11
('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5),
12
('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17),
13
('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40),
14
('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6),
15
('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41),
16
('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41),
17
('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29),
18
('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19),
19
('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42),
20
('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30),
21
('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8),
22
('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22),
23
('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43),
24
('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37),
25
('1999-09-19',100100,37), ('2000-12-18',100700,38);
26
select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date;
27
event_date type event_id
34
explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date;
35
id select_type table type possible_keys key key_len ref rows Extra
36
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
37
select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND (type=100600 OR type=100100) or event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND type=100099;
38
event_date type event_id
48
PAPER_ID int DEFAULT '0' NOT NULL,
49
YEAR int DEFAULT '0' NOT NULL,
50
ISSUE int DEFAULT '0' NOT NULL,
51
CLOSED int DEFAULT '0' NOT NULL,
53
PRIMARY KEY (PAPER_ID,YEAR,ISSUE)
55
INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'),
56
(1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'),
57
(3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'),
58
(3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'),
59
(3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'),
60
(1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'),
61
(1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'),
62
(1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'),
63
(1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'),
64
(1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'),
65
(1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'),
66
(1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'),
67
(1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'),
68
(1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'),
69
(3,1999,35,0,'1999-07-12');
70
select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE;
80
Table Op Msg_type Msg_text
81
test.t1 check status OK
84
id int NOT NULL auto_increment,
85
parent_id int DEFAULT '0' NOT NULL,
86
level int DEFAULT '0' NOT NULL,
88
KEY parent_id (parent_id),
91
INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2),
92
(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2),
93
(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1),
94
(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2),
95
(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2),
96
(19,3,2), (5,1,1), (179,5,2);
97
SELECT * FROM t1 WHERE level = 1 AND parent_id = 1;
105
SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id;
115
Satellite varchar(25) not null,
116
SensorMode varchar(25) not null,
117
FullImageCornersUpperLeftLongitude double not null,
118
FullImageCornersUpperRightLongitude double not null,
119
FullImageCornersUpperRightLatitude double not null,
120
FullImageCornersLowerRightLatitude double not null,
121
index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude));
122
insert into t1 values("OV-3","PAN1",91,-92,40,50);
123
insert into t1 values("OV-4","PAN1",91,-92,40,50);
124
select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000;
125
Satellite SensorMode FullImageCornersUpperLeftLongitude FullImageCornersUpperRightLongitude FullImageCornersUpperRightLatitude FullImageCornersLowerRightLatitude
126
OV-3 PAN1 91 -92 40 50
128
create table t1 ( aString char(100) not null default "", key aString (aString(10)) );
129
insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love");
130
select * from t1 where aString < "believe in myself" order by aString;
135
select * from t1 where aString > "believe in love" order by aString;
138
alter table t1 drop key aString;
139
select * from t1 where aString < "believe in myself" order by aString;
144
select * from t1 where aString > "believe in love" order by aString;
148
CREATE TEMPORARY TABLE t1 (
149
t1ID int NOT NULL auto_increment,
150
art varbinary(1) NOT NULL default '',
151
KNR char(5) NOT NULL default '',
152
RECHNR char(6) NOT NULL default '',
153
POSNR char(2) NOT NULL default '',
154
ARTNR char(10) NOT NULL default '',
155
TEX char(70) NOT NULL default '',
161
INSERT INTO t1 (art) VALUES ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
162
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
163
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
164
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
165
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
166
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
167
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
168
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
169
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
170
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
171
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
172
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
173
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
174
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
175
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
176
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
177
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
178
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
179
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
180
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
181
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
182
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
183
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
184
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
185
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
186
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
187
('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),
188
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
189
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
190
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
191
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
192
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
193
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
194
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
195
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
196
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
197
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),
198
('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j');
199
select count(*) from t1 where upper(art) = 'J';
202
select count(*) from t1 where art = 'J' or art = 'j';
205
select count(*) from t1 where art = 'j' or art = 'J';
208
select count(*) from t1 where art = 'j';
211
select count(*) from t1 where art = 'J';
215
create table t1 (x int, y int, index(x), index(y));
216
insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
218
explain select * from t1, t1 t2 where t1.y = 8 and t2.x between 7 and t1.y+0;
219
id select_type table type possible_keys key key_len ref rows Extra
220
1 SIMPLE t1 ref y y 5 const 1
221
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
222
explain select * from t1, t1 t2 where t1.y = 8 and t2.x >= 7 and t2.x <= t1.y+0;
223
id select_type table type possible_keys key key_len ref rows Extra
224
1 SIMPLE t1 ref y y 5 const 1
225
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
226
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1;
227
id select_type table type possible_keys key key_len ref rows Extra
228
1 SIMPLE t1 ref y y 5 const 1
229
1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer
230
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1;
231
id select_type table type possible_keys key key_len ref rows Extra
232
1 SIMPLE t1 ref y y 5 const 1
233
1 SIMPLE t2 range x x 5 NULL 3 Using where; Using join buffer
234
explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y;
235
id select_type table type possible_keys key key_len ref rows Extra
236
1 SIMPLE t1 ref y y 5 const 1
237
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
238
explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y;
239
id select_type table type possible_keys key key_len ref rows Extra
240
1 SIMPLE t1 ref y y 5 const 1
241
1 SIMPLE t2 range x x 5 NULL 2 Using where; Using join buffer
242
explain select count(*) from t1 where x in (1);
243
id select_type table type possible_keys key key_len ref rows Extra
244
1 SIMPLE t1 ref x x 5 const 1 Using index
245
explain select count(*) from t1 where x in (1,2);
246
id select_type table type possible_keys key key_len ref rows Extra
247
1 SIMPLE t1 index x x 5 NULL 9 Using where; Using index
249
CREATE TABLE t1 (key1 int NOT NULL default '0', KEY i1 (key1));
250
INSERT INTO t1 VALUES (0),(0),(0),(0),(0),(1),(1);
251
CREATE TABLE t2 (keya int NOT NULL default '0', KEY j1 (keya));
252
INSERT INTO t2 VALUES (0),(0),(1),(1),(2),(2);
253
explain select * from t1, t2 where (t1.key1 <t2.keya + 1) and t2.keya=3;
254
id select_type table type possible_keys key key_len ref rows Extra
255
1 SIMPLE t2 ref j1 j1 4 const 1 Using index
256
1 SIMPLE t1 range i1 i1 4 NULL 3 Using where; Using index; Using join buffer
257
explain select * from t1 force index(i1), t2 force index(j1) where
258
(t1.key1 <t2.keya + 1) and t2.keya=3;
259
id select_type table type possible_keys key key_len ref rows Extra
260
1 SIMPLE t2 ref j1 j1 4 const 1 Using index
261
1 SIMPLE t1 range i1 i1 4 NULL 3 Using where; Using index; Using join buffer
263
CREATE TEMPORARY TABLE t1 (
269
INSERT INTO t1 VALUES
270
(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,2),(10,2),
271
(13,2),(14,2),(15,2),(16,2),(17,3),(17,3),(16,3),(17,3),(19,3),(20,3),
272
(21,4),(22,5),(23,5),(24,5),(25,5),(26,5),(30,5),(31,5),(32,5),(33,5),
273
(33,5),(33,5),(33,5),(33,5),(34,5),(35,5);
274
EXPLAIN SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
275
id select_type table type possible_keys key key_len ref rows Extra
276
1 SIMPLE t1 range a,b a 5 NULL 2 Using where
277
SELECT * FROM t1 WHERE a IN(1,2) AND b=5;
280
CREATE TABLE t1 (a int, b int, c int, INDEX (c,a,b));
281
INSERT INTO t1 VALUES (1,0,0),(1,0,0),(1,0,0);
282
INSERT INTO t1 VALUES (0,1,0),(0,1,0),(0,1,0);
283
SELECT COUNT(*) FROM t1 WHERE (c=0 and a=1) or (c=0 and b=1);
286
SELECT COUNT(*) FROM t1 WHERE (c=0 and b=1) or (c=0 and a=1);
290
CREATE TABLE t1 ( a int not null, b int not null, INDEX ab(a,b) );
291
INSERT INTO t1 VALUES (47,1), (70,1), (15,1), (15, 4);
295
( b =1 AND a BETWEEN 14 AND 21 ) OR
296
( b =2 AND a BETWEEN 16 AND 18 ) OR
297
( b =3 AND a BETWEEN 15 AND 19 ) OR
298
(a BETWEEN 19 AND 47)
304
CREATE TEMPORARY TABLE t1 (
305
id int NOT NULL AUTO_INCREMENT ,
306
line int NOT NULL default '0',
307
columnid int NOT NULL default '0',
308
owner int NOT NULL default '0',
309
ordinal int NOT NULL default '0',
310
showid int NOT NULL default '1',
311
tableid int NOT NULL default '1',
312
content int NOT NULL default '188',
313
PRIMARY KEY ( owner, id ) ,
314
KEY menu( owner, showid, columnid ) ,
315
KEY `COLUMN` ( owner, columnid, line ) ,
316
KEY `LINES` ( owner, tableid, content, id ) ,
317
KEY recount( owner, line )
319
INSERT into t1 (owner,id,columnid,line) values (11,15,15,1),(11,13,13,5);
320
SELECT id, columnid, tableid, content, showid, line, ordinal FROM t1 WHERE owner=11 AND ((columnid IN ( 15, 13, 14 ) AND line IN ( 1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 31 )) OR (columnid IN ( 13, 14 ) AND line IN ( 15 ))) LIMIT 0 , 30;
321
id columnid tableid content showid line ordinal
325
create table t1 (id int primary key);
326
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);
327
select id from t1 where id in (2,5,9) ;
332
select id from t1 where id=2 or id=5 or id=9 ;
338
create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2));
339
insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"),
340
(3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"),
341
(6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"),
342
(9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"),
343
(12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"),
344
(15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"),
345
(18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa");
346
select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1;
350
id int not null auto_increment,
351
name char(1) not null,
354
index uid_index (uid));
356
id int not null auto_increment,
357
name char(1) not null,
360
index uid_index (uid));
361
insert into t1(id, uid, name) values(1, 0, ' ');
362
insert into t1(uid, name) values(0, ' ');
363
insert into t2(uid, name) select uid, name from t1;
364
insert into t1(uid, name) select uid, name from t2;
365
insert into t2(uid, name) select uid, name from t1;
366
insert into t1(uid, name) select uid, name from t2;
367
insert into t2(uid, name) select uid, name from t1;
368
insert into t1(uid, name) select uid, name from t2;
369
insert into t2(uid, name) select uid, name from t1;
370
insert into t1(uid, name) select uid, name from t2;
371
insert into t2(uid, name) select uid, name from t1;
372
insert into t1(uid, name) select uid, name from t2;
373
insert into t2(uid, name) select uid, name from t1;
374
insert into t2(uid, name) select uid, name from t1;
375
insert into t2(uid, name) select uid, name from t1;
376
insert into t2(uid, name) select uid, name from t1;
377
insert into t1(uid, name) select uid, name from t2;
379
insert into t2(uid, name) values
406
insert into t1(uid, name) select uid, name from t2 order by uid;
408
insert into t2(id, uid, name) select id, uid, name from t1;
409
select count(*) from t1;
412
select count(*) from t2;
416
Table Op Msg_type Msg_text
417
test.t1 analyze status OK
418
test.t2 analyze status OK
419
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
420
id select_type table type possible_keys key key_len ref rows Extra
421
1 SIMPLE # range uid_index uid_index 4 # # Using where
422
1 SIMPLE # ref uid_index uid_index 4 # #
423
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid > 0;
424
id select_type table type possible_keys key key_len ref rows Extra
425
1 SIMPLE # range uid_index uid_index 4 # # Using where
426
1 SIMPLE # ref uid_index uid_index 4 # #
427
explain select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
428
id select_type table type possible_keys key key_len ref rows Extra
429
1 SIMPLE # range uid_index uid_index 4 # # Using where
430
1 SIMPLE # ref uid_index uid_index 4 # #
431
explain select * from t1, t2 where t1.uid=t2.uid AND t2.uid != 0;
432
id select_type table type possible_keys key key_len ref rows Extra
433
1 SIMPLE # range uid_index uid_index 4 # # Using where
434
1 SIMPLE # ref uid_index uid_index 4 # #
435
select * from t1, t2 where t1.uid=t2.uid AND t1.uid > 0;
436
id name uid id name uid
463
select * from t1, t2 where t1.uid=t2.uid AND t1.uid != 0;
464
id name uid id name uid
492
create table t1 (x bigint not null);
493
insert into t1(x) values (0x0ffffffffffffff0);
494
insert into t1(x) values (0x0ffffffffffffff1);
499
select count(*) from t1 where x>0;
502
select count(*) from t1 where x=0;
505
select count(*) from t1 where x<0;
508
select count(*) from t1 where x < -16;
511
select count(*) from t1 where x = -16;
514
select count(*) from t1 where x > -16;
517
select count(*) from t1 where x = 18446744073709551601;
520
create table t2 (x bigint not null);
521
insert into t2(x) values (-16);
522
insert into t2(x) values (-15);
527
select count(*) from t2 where x>0;
530
select count(*) from t2 where x=0;
533
select count(*) from t2 where x<0;
536
select count(*) from t2 where x < -16;
539
select count(*) from t2 where x = -16;
542
select count(*) from t2 where x > -16;
545
select count(*) from t2 where x = 18446744073709551601;
549
create table t1 (x bigint not null primary key) engine=innodb;
550
insert into t1(x) values (0x0ffffffffffffff0);
551
insert into t1(x) values (0x0ffffffffffffff1);
556
select count(*) from t1 where x>0;
559
select count(*) from t1 where x=0;
562
select count(*) from t1 where x<0;
565
select count(*) from t1 where x < -16;
568
select count(*) from t1 where x = -16;
571
select count(*) from t1 where x > -16;
574
select count(*) from t1 where x = 18446744073709551601;
578
create table t1 (a bigint);
579
create index t1i on t1(a);
580
insert into t1 select 18446744073709551615;
581
insert into t1 select 18446744073709551614;
582
explain select * from t1 where a <> -1;
583
id select_type table type possible_keys key key_len ref rows Extra
584
1 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index
585
select * from t1 where a <> -1;
588
explain select * from t1 where a > -1 or a < -1;
589
id select_type table type possible_keys key key_len ref rows Extra
590
1 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index
591
select * from t1 where a > -1 or a < -1;
594
explain select * from t1 where a > -1;
595
id select_type table type possible_keys key key_len ref rows Extra
596
1 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index
597
select * from t1 where a > -1;
599
explain select * from t1 where a < -1;
600
id select_type table type possible_keys key key_len ref rows Extra
601
1 SIMPLE t1 index t1i t1i 9 NULL 2 Using where; Using index
602
select * from t1 where a < -1;
606
create table t1 (a char(10), b text, key (a));
607
INSERT INTO t1 (a) VALUES
608
('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
609
explain select * from t1 where a='aaa';
610
id select_type table type possible_keys key key_len ref rows Extra
611
1 SIMPLE t1 ref a a 43 const 2 Using where
612
explain select * from t1 where a=binary 'aaa';
613
id select_type table type possible_keys key key_len ref rows Extra
614
1 SIMPLE t1 range a a 43 NULL 2 Using where
615
explain select * from t1 where a='aaa' collate utf8_bin;
616
id select_type table type possible_keys key key_len ref rows Extra
617
1 SIMPLE t1 range a a 43 NULL 2 Using where
620
`CLIENT` char(3) collate utf8_bin NOT NULL default '000',
621
`ARG1` char(3) collate utf8_bin NOT NULL default '',
622
`ARG2` char(3) collate utf8_bin NOT NULL default '',
623
`FUNCTION` varchar(10) collate utf8_bin NOT NULL default '',
624
`FUNCTINT` int NOT NULL default '0',
625
KEY `VERI_CLNT~2` (`ARG1`)
627
INSERT INTO t1 VALUES ('000',' 0',' 0','Text 001',0), ('000',' 0',' 1','Text 002',0),
628
('000',' 1',' 2','Text 003',0), ('000',' 2',' 3','Text 004',0),
629
('001',' 3',' 0','Text 017',0);
630
SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 1' OR ARG1 != ' 2');
633
SELECT count(*) FROM t1 WHERE CLIENT='000' AND (ARG1 != ' 2' OR ARG1 != ' 1');
637
create table t1 (a int);
638
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
645
PRIMARY KEY (pk1,pk2,pk3,pk4)
647
insert into t2 select 1, A.a+10*B.a, 432, 44, 'fillerZ' from t1 A, t1 B;
648
INSERT INTO t2 VALUES (2621, 2635, 0, 0,'filler'), (2621, 2635, 1, 0,'filler'),
649
(2621, 2635, 10, 0,'filler'), (2621, 2635, 11, 0,'filler'),
650
(2621, 2635, 14, 0,'filler'), (2621, 2635, 1000015, 0,'filler');
652
WHERE ((((pk4 =0) AND (pk1 =2621) AND (pk2 =2635)))
653
OR ((pk4 =1) AND (((pk1 IN ( 7, 2, 1 ))) OR (pk1 =522)) AND ((pk2 IN ( 0, 2635))))
654
) AND (pk3 >=1000000);
655
pk1 pk2 pk3 pk4 filler
656
2621 2635 1000015 0 filler
658
create table t1(a char(2), key(a(1)));
659
insert into t1 values ('x'), ('xx');
660
explain select a from t1 where a > 'x';
661
id select_type table type possible_keys key key_len ref rows Extra
662
1 SIMPLE t1 range a a 7 NULL 1 Using where
663
select a from t1 where a > 'x';
668
OXID varchar(32) NOT NULL DEFAULT '',
669
OXPARENTID varchar(32) NOT NULL DEFAULT 'oxrootid',
670
OXLEFT int NOT NULL DEFAULT '0',
671
OXRIGHT int NOT NULL DEFAULT '0',
672
OXROOTID varchar(32) NOT NULL DEFAULT '',
676
KEY OXRIGHT (OXRIGHT),
677
KEY OXROOTID (OXROOTID)
679
INSERT INTO t1 VALUES
680
('d8c4177d09f8b11f5.52725521','oxrootid',1,40,'d8c4177d09f8b11f5.52725521'),
681
('d8c4177d151affab2.81582770','d8c4177d09f8b11f5.52725521',2,3,
682
'd8c4177d09f8b11f5.52725521'),
683
('d8c4177d206a333d2.74422679','d8c4177d09f8b11f5.52725521',4,5,
684
'd8c4177d09f8b11f5.52725521'),
685
('d8c4177d225791924.30714720','d8c4177d09f8b11f5.52725521',6,7,
686
'd8c4177d09f8b11f5.52725521'),
687
('d8c4177d2380fc201.39666693','d8c4177d09f8b11f5.52725521',8,9,
688
'd8c4177d09f8b11f5.52725521'),
689
('d8c4177d24ccef970.14957924','d8c4177d09f8b11f5.52725521',10,11,
690
'd8c4177d09f8b11f5.52725521');
692
SELECT s.oxid FROM t1 v, t1 s
693
WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
694
v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
695
s.oxleft > v.oxleft AND s.oxleft < v.oxright;
696
id select_type table type possible_keys key key_len ref rows Extra
697
1 SIMPLE # ALL OXLEFT,OXRIGHT,OXROOTID NULL NULL # # Using where
698
1 SIMPLE # ALL OXLEFT NULL NULL # # Range checked for each record (index map: 0x4)
699
SELECT s.oxid FROM t1 v, t1 s
700
WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
701
v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
702
s.oxleft > v.oxleft AND s.oxleft < v.oxright;
704
d8c4177d151affab2.81582770
705
d8c4177d206a333d2.74422679
706
d8c4177d225791924.30714720
707
d8c4177d2380fc201.39666693
708
d8c4177d24ccef970.14957924
711
c1 char(10), c2 char(10), c3 char(10), c4 char(10),
712
c5 char(10), c6 char(10), c7 char(10), c8 char(10),
713
c9 char(10), c10 char(10), c11 char(10), c12 char(10),
714
c13 char(10), c14 char(10), c15 char(10), c16 char(10),
715
index(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12,c13,c14,c15,c16)
717
insert into t1 (c1) values ('1'),('1'),('1'),('1');
718
select * from t1 where
719
c1 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
720
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
721
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
722
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
723
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
724
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
725
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
726
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
727
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
728
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
729
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
730
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
731
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
732
and c2 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
733
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
734
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
735
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
736
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
737
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
738
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
739
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
740
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
741
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
742
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
743
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
744
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
745
and c3 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
746
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
747
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
748
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
749
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
750
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
751
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
752
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
753
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
754
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
755
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
756
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
757
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
758
and c4 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
759
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
760
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
761
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
762
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
763
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
764
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
765
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
766
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
767
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
768
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
769
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
770
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
771
and c5 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
772
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
773
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
774
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
775
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
776
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
777
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
778
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
779
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
780
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
781
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
782
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
783
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
784
and c6 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
785
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
786
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
787
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
788
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
789
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
790
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
791
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
792
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
793
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
794
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
795
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
796
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
797
and c7 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
798
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
799
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
800
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
801
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
802
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
803
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
804
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
805
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
806
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
807
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
808
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
809
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
810
and c8 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
811
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
812
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
813
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
814
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
815
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
816
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
817
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
818
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
819
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
820
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
821
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
822
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
823
and c9 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
824
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
825
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
826
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
827
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
828
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
829
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
830
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
831
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
832
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
833
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
834
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
835
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC")
836
and c10 in ("abcdefgh", "123456789", "qwertyuio", "asddfgh",
837
"abcdefg1", "123456781", "qwertyui1", "asddfg1",
838
"abcdefg2", "123456782", "qwertyui2", "asddfg2",
839
"abcdefg3", "123456783", "qwertyui3", "asddfg3",
840
"abcdefg4", "123456784", "qwertyui4", "asddfg4",
841
"abcdefg5", "123456785", "qwertyui5", "asddfg5",
842
"abcdefg6", "123456786", "qwertyui6", "asddfg6",
843
"abcdefg7", "123456787", "qwertyui7", "asddfg7",
844
"abcdefg8", "123456788", "qwertyui8", "asddfg8",
845
"abcdefg9", "123456789", "qwertyui9", "asddfg9",
846
"abcdefgA", "12345678A", "qwertyuiA", "asddfgA",
847
"abcdefgB", "12345678B", "qwertyuiB", "asddfgB",
848
"abcdefgC", "12345678C", "qwertyuiC", "asddfgC");
849
c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16
853
id int NOT NULL auto_increment,
858
INSERT INTO t1 VALUES
859
(1,'B'), (2,'B'), (3,'B'), (4,'B'), (5,'B'), (6,'B'),
860
(7,'B'), (8,'B'), (9,'B'), (10,'B'), (11,'B'), (12,'B'),
861
(13,'B'), (14,'B'), (15,'B'), (16,'B'), (17,'B'), (18,'B'),
862
(19,'B'), (20,'B'), (21,'B'), (22,'B'), (23,'B'), (24,'B'),
863
(25,'A'), (26,'A'), (27,'A'), (28,'A'), (29,'A'), (30,'A'),
864
(31,'A'), (32,'A'), (33,'A'), (34,'A'), (35,'A'), (36,'A'),
865
(37,'A'), (38,'A'), (39,'A'), (40,'A'), (41,'A'), (42,'A'),
866
(43,'A'), (44,'A'), (45,'A'), (46,'A'), (47,'A'), (48,'A'),
867
(49,'A'), (50,'A'), (51,'A'), (52,'A'), (53,'C'), (54,'C'),
868
(55,'C'), (56,'C'), (57,'C'), (58,'C'), (59,'C'), (60,'C');
869
EXPLAIN SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
870
id select_type table type possible_keys key key_len ref rows Extra
871
1 SIMPLE t1 range status status 83 NULL 28 Using where; Using index
872
EXPLAIN SELECT * FROM t1 WHERE status NOT IN ('A','B');
873
id select_type table type possible_keys key key_len ref rows Extra
874
1 SIMPLE t1 range status status 83 NULL 28 Using where; Using index
875
SELECT * FROM t1 WHERE status <> 'A' AND status <> 'B';
885
SELECT * FROM t1 WHERE status NOT IN ('A','B');
895
EXPLAIN SELECT status FROM t1 WHERE status <> 'A' AND status <> 'B';
896
id select_type table type possible_keys key key_len ref rows Extra
897
1 SIMPLE t1 range status status 83 NULL 28 Using where; Using index
898
EXPLAIN SELECT status FROM t1 WHERE status NOT IN ('A','B');
899
id select_type table type possible_keys key key_len ref rows Extra
900
1 SIMPLE t1 range status status 83 NULL 28 Using where; Using index
901
EXPLAIN SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
902
id select_type table type possible_keys key key_len ref rows Extra
903
1 SIMPLE t1 range status status 83 NULL 18 Using where; Using index
904
EXPLAIN SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
905
id select_type table type possible_keys key key_len ref rows Extra
906
1 SIMPLE t1 range status status 83 NULL 18 Using where; Using index
907
SELECT * FROM t1 WHERE status NOT BETWEEN 'A' AND 'B';
917
SELECT * FROM t1 WHERE status < 'A' OR status > 'B';
928
CREATE TABLE t1 (name varchar(15) NOT NULL, KEY idx(name));
929
INSERT INTO t1 VALUES ('Betty'), ('Anna');
934
DELETE FROM t1 WHERE name NOT LIKE 'A%a';
939
CREATE TABLE t1 (a int, KEY idx(a));
940
INSERT INTO t1 VALUES (NULL), (1), (2), (3);
947
DELETE FROM t1 WHERE NOT(a <=> 2);
952
create table t3 (a int);
953
insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
954
create table t1 (a varchar(10), filler char(200), key(a));
955
insert into t1 values ('a','');
956
insert into t1 values ('a ','');
957
insert into t1 values ('a ', '');
958
insert into t1 select concat('a', 1000 + A.a + 10 * (B.a + 10 * C.a)), ''
959
from t3 A, t3 B, t3 C;
960
create table t2 (a varchar(10), filler char(200), key(a));
961
insert into t2 select * from t1;
962
explain select * from t1 where a between 'a' and 'a ';
963
id select_type table type possible_keys key key_len ref rows Extra
964
1 SIMPLE # ref a a 43 # # Using where
965
explain select * from t1 where a = 'a' or a='a ';
966
id select_type table type possible_keys key key_len ref rows Extra
967
1 SIMPLE # ref a a 43 # # Using where
968
explain select * from t2 where a between 'a' and 'a ';
969
id select_type table type possible_keys key key_len ref rows Extra
970
1 SIMPLE # ref a a 43 # # Using where
971
explain select * from t2 where a = 'a' or a='a ';
972
id select_type table type possible_keys key key_len ref rows Extra
973
1 SIMPLE # ref a a 43 # # Using where
974
update t1 set a='b' where a<>'a';
975
explain select * from t1 where a not between 'b' and 'b';
976
id select_type table type possible_keys key key_len ref rows Extra
977
1 SIMPLE # ALL a NULL NULL # # Using where
978
select a, hex(filler) from t1 where a not between 'b' and 'b';
985
id int NOT NULL DEFAULT '0',
986
b int NOT NULL DEFAULT '0',
987
c int NOT NULL DEFAULT '0',
988
INDEX idx1(b,c), INDEX idx2(c));
989
INSERT INTO t1(id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
990
INSERT INTO t1(b,c) VALUES (3,4), (3,4);
991
SELECT * FROM t1 WHERE b<=3 AND 3<=c;
995
SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
999
EXPLAIN SELECT * FROM t1 WHERE b<=3 AND 3<=c;
1000
id select_type table type possible_keys key key_len ref rows Extra
1001
1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 2 Using where
1002
EXPLAIN SELECT * FROM t1 WHERE 3 BETWEEN b AND c;
1003
id select_type table type possible_keys key key_len ref rows Extra
1004
1 SIMPLE t1 range idx1,idx2 idx2 4 NULL 2 Using where
1005
SELECT * FROM t1 WHERE 0 < b OR 0 > c;
1009
SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
1013
EXPLAIN SELECT * FROM t1 WHERE 0 < b OR 0 > c;
1014
id select_type table type possible_keys key key_len ref rows Extra
1015
1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
1016
EXPLAIN SELECT * FROM t1 WHERE 0 NOT BETWEEN b AND c;
1017
id select_type table type possible_keys key key_len ref rows Extra
1018
1 SIMPLE t1 ALL idx1,idx2 NULL NULL NULL 10 Using where
1020
CREATE TEMPORARY TABLE t1 (
1021
item char(20) NOT NULL default '',
1023
price decimal(16,3) NOT NULL default '0.000',
1024
PRIMARY KEY (item,started)
1026
INSERT INTO t1 VALUES
1027
('A1','2005-11-01 08:00:00',1000),
1028
('A1','2005-11-15 00:00:00',2000),
1029
('A1','2005-12-12 08:00:00',3000),
1030
('A2','2005-12-01 08:00:00',1000);
1031
EXPLAIN SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 23:59:59';
1032
id select_type table type possible_keys key key_len ref rows Extra
1033
1 SIMPLE # range PRIMARY PRIMARY 90 # # Using where
1034
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-01 23:59:59';
1036
A1 2005-11-01 08:00:00 1000.000
1037
A1 2005-11-15 00:00:00 2000.000
1038
SELECT * FROM t1 WHERE item='A1' AND started<='2005-12-02 00:00:00';
1040
A1 2005-11-01 08:00:00 1000.000
1041
A1 2005-11-15 00:00:00 2000.000
1044
BUG#32198 "Comparison of DATE with DATETIME still not using indexes correctly"
1047
id int NOT NULL auto_increment,
1048
dateval date default NULL,
1050
KEY dateval (dateval)
1051
) AUTO_INCREMENT=173;
1052
INSERT INTO t1 VALUES
1053
(1,'2007-01-01'),(2,'2007-01-02'),(3,'2007-01-03'),(4,'2007-01-04'),
1054
(5,'2007-01-05'),(6,'2007-01-06'),(7,'2007-01-07'),(8,'2007-01-08'),
1055
(9,'2007-01-09'),(10,'2007-01-10'),(11,'2007-01-11');
1056
This must use range access:
1057
explain select * from t1 where dateval >= '2007-01-01 00:00:00' and dateval <= '2007-01-02 23:59:59';
1058
id select_type table type possible_keys key key_len ref rows Extra
1059
1 SIMPLE t1 index dateval PRIMARY 4 NULL 11 Using where
1062
a varchar(32), index (a)
1063
) DEFAULT COLLATE=utf8_bin;
1064
INSERT INTO t1 VALUES
1065
('B'), ('A'), ('A'), ('C'), ('B'), ('A'), ('A');
1066
SELECT a FROM t1 WHERE a='b' OR a='B';
1070
EXPLAIN SELECT a FROM t1 WHERE a='b' OR a='B';
1071
id select_type table type possible_keys key key_len ref rows Extra
1072
1 SIMPLE t1 range a a 131 NULL 3 Using where; Using index
1074
CREATE TABLE t1 (f1 int NOT NULL, PRIMARY KEY (f1));
1075
INSERT INTO t1 VALUES (127),(254),(0),(1),(255);
1076
SELECT COUNT(*) FROM t1 WHERE f1 < 256;
1079
SELECT COUNT(*) FROM t1 WHERE f1 < 256.0;
1082
SELECT COUNT(*) FROM t1 WHERE f1 < 255;
1085
SELECT COUNT(*) FROM t1 WHERE f1 < -1;
1088
SELECT COUNT(*) FROM t1 WHERE f1 > -1;
1092
CREATE TABLE t1 ( f1 int NOT NULL, PRIMARY KEY (f1));
1093
INSERT INTO t1 VALUES (127),(126),(0),(-128),(-127);
1094
SELECT COUNT(*) FROM t1 WHERE f1 < 128;
1097
SELECT COUNT(*) FROM t1 WHERE f1 < 128.0;
1100
SELECT COUNT(*) FROM t1 WHERE f1 < 127;
1103
SELECT COUNT(*) FROM t1 WHERE f1 > -129;
1106
SELECT COUNT(*) FROM t1 WHERE f1 > -129.0;
1109
SELECT COUNT(*) FROM t1 WHERE f1 > -128;
1113
create table t1 (a int);
1114
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1115
create table t2 (a int, b int, filler char(100));
1116
insert into t2 select A.a + 10 * (B.a + 10 * C.a), 10, 'filler' from t1 A,
1117
t1 B, t1 C where A.a < 5;
1118
insert into t2 select 1000, b, 'filler' from t2;
1119
alter table t2 add index (a,b);
1120
select 'In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)' Z;
1122
In following EXPLAIN the access method should be ref, #rows~=500 (and not 2)
1123
create temporary table t2e like t2;
1124
alter table t2e engine=myisam;
1125
insert into t2e select * from t2;
1127
Table Op Msg_type Msg_text
1128
test.t2e analyze note The storage engine for the table doesn't support analyze
1129
explain select * from t2e where a=1000 and b<11;
1130
id select_type table type possible_keys key key_len ref rows Extra
1131
1 SIMPLE t2e ref a a 5 const 11 Using where
1134
CREATE TABLE t1 (c1 DECIMAL(10,0),INDEX(c1));
1135
INSERT INTO t1 VALUES (1),(2),(3);
1136
SELECT c1 FROM t1 WHERE c1 >= 'A' GROUP BY 1;
1142
Warning 1366 Incorrect decimal value: 'A' for column 'c1' at row 1
1143
Warning 1292 Truncated incorrect DOUBLE value: 'A'
1144
Warning 1292 Truncated incorrect DOUBLE value: 'A'
1145
Warning 1292 Truncated incorrect DOUBLE value: 'A'
1147
create table t1 (a int,b int,key (b),key (a),key (b,a));
1148
insert into t1(a,b) values (1,2),(3,4),(5,6),(7,8);
1149
create table t2 (c int);
1150
insert into t2(c) values (1),(5),(6),(7),(8);
1151
select 1 from (select c from t1,t2 where b >= 1 and a <=> c group by 1 limit 1) as d1;