drop table if exists t1; CREATE TABLE t1 ( event_date date DEFAULT '0000-00-00' NOT NULL, type int(11) DEFAULT '0' NOT NULL, event_id int(11) DEFAULT '0' NOT NULL, PRIMARY KEY (event_date,type,event_id) ); INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), ('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), ('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), ('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), ('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), ('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), ('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), ('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), ('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), ('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), ('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), ('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), ('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), ('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), ('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), ('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), ('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), ('1999-09-19',100100,37), ('2000-12-18',100700,38); 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; event_date type event_id 1999-07-10 100100 24 1999-07-11 100100 25 1999-07-13 100600 0 1999-07-13 100600 4 1999-07-13 100600 26 1999-07-14 100600 10 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; Comment Impossible WHERE 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; event_date type event_id 1999-07-10 100100 24 1999-07-11 100100 25 1999-07-13 100600 0 1999-07-13 100600 4 1999-07-13 100600 26 1999-07-14 100600 10 1999-07-15 100600 16 drop table t1; CREATE TABLE t1 ( PAPER_ID smallint(6) DEFAULT '0' NOT NULL, YEAR smallint(6) DEFAULT '0' NOT NULL, ISSUE smallint(6) DEFAULT '0' NOT NULL, CLOSED tinyint(4) DEFAULT '0' NOT NULL, ISS_DATE date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (PAPER_ID,YEAR,ISSUE) ); INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), (1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), (3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), (3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), (3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), (1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), (1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), (1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), (1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), (1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), (1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), (1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), (1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), (1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), (3,1999,35,0,'1999-07-12'); select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; YEAR ISSUE 1999 29 1999 30 1999 31 1999 32 1999 33 1999 34 1999 35 check table t1; Table Op Msg_type Msg_text test.t1 check status OK repair table t1; Table Op Msg_type Msg_text test.t1 repair status OK drop table t1; CREATE TABLE t1 ( id int(11) NOT NULL auto_increment, parent_id int(11) DEFAULT '0' NOT NULL, level tinyint(4) DEFAULT '0' NOT NULL, PRIMARY KEY (id), KEY parent_id (parent_id), KEY level (level) ); INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), (22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), (203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), (15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), (26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), (19,3,2), (5,1,1), (179,5,2); SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; id parent_id level 3 1 1 4 1 1 2 1 1 6 1 1 7 1 1 5 1 1 SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; id parent_id level 2 1 1 3 1 1 4 1 1 5 1 1 6 1 1 7 1 1 drop table t1; create table t1( Satellite varchar(25) not null, SensorMode varchar(25) not null, FullImageCornersUpperLeftLongitude double not null, FullImageCornersUpperRightLongitude double not null, FullImageCornersUpperRightLatitude double not null, FullImageCornersLowerRightLatitude double not null, index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude)); insert into t1 values("OV-3","PAN1",91,-92,40,50); insert into t1 values("OV-4","PAN1",91,-92,40,50); select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000; Satellite SensorMode FullImageCornersUpperLeftLongitude FullImageCornersUpperRightLongitude FullImageCornersUpperRightLatitude FullImageCornersLowerRightLatitude OV-3 PAN1 91 -92 40 50 drop table t1; create table t1 ( aString char(100) not null default "", key aString (aString(10)) ); insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love"); select * from t1 where aString < "believe in myself" order by aString; aString baaa believe believe in love select * from t1 where aString > "believe in love" order by aString; aString believe in myself alter table t1 drop key aString; select * from t1 where aString < "believe in myself" order by aString; aString baaa believe believe in love select * from t1 where aString > "believe in love" order by aString; aString believe in myself drop table t1; CREATE TABLE t1 ( t1ID int(10) unsigned NOT NULL auto_increment, art char(1) binary NOT NULL default '', KNR char(5) NOT NULL default '', RECHNR char(6) NOT NULL default '', POSNR char(2) NOT NULL default '', ARTNR char(10) NOT NULL default '', TEX char(70) NOT NULL default '', PRIMARY KEY (t1ID), KEY IdxArt (art), KEY IdxKnr (KNR), KEY IdxArtnr (ARTNR) ) TYPE=MyISAM; INSERT INTO t1 (art) VALUES ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'),('j'),('J'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'), ('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'),('j'); select count(*) from t1 where upper(art) = 'J'; count(*) 602 select count(*) from t1 where art = 'J' or art = 'j'; count(*) 602 select count(*) from t1 where art = 'j' or art = 'J'; count(*) 602 select count(*) from t1 where art = 'j'; count(*) 389 select count(*) from t1 where art = 'J'; count(*) 213 drop table t1; create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); 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; id1 idnull drop table t1; create table t1 (x int, y int, index(x), index(y)); insert into t1 (x) values (1),(2),(3),(4),(5),(6),(7),(8),(9); update t1 set y=x; explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 7 and t1.y+0; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where t2 range x x 5 NULL 4 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 7 and t2.x <= t1.y+0; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where t2 range x x 5 NULL 4 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x between t1.y-1 and t1.y+1; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where t2 ALL x NULL NULL NULL 9 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= t1.y-1 and t2.x <= t1.y+1; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where t2 ALL x NULL NULL NULL 9 Range checked for each record (index map: 1) explain select * from t1, t1 t2 where t1.y = 2 and t2.x between 0 and t1.y; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where t2 ALL x NULL NULL NULL 9 Using where explain select * from t1, t1 t2 where t1.y = 2 and t2.x >= 0 and t2.x <= t1.y; table type possible_keys key key_len ref rows Extra t1 ref y y 5 const 1 Using where t2 range x x 5 NULL 2 Using where drop table t1;