drop table if exists t0,t1,t2; select count(*) from t1; count(*) 64801 explain select key1,key2 from t1 where key1=100 and key2=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 58 Using intersect(key1,key2); Using where; Using index select key1,key2 from t1 where key1=100 and key2=100; key1 key2 100 100 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; key1 key2 key3 key4 filler1 100 100 100 100 key1-key2-key3-key4 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2'); insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3'); explain select key1,key2,filler1 from t1 where key1=100 and key2=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 58 Using intersect(key1,key2); Using where select key1,key2,filler1 from t1 where key1=100 and key2=100; key1 key2 filler1 100 100 key1-key2-key3-key4 100 100 key1-key2 explain select key1,key2 from t1 where key1=100 and key2=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 58 Using intersect(key1,key2); Using where; Using index select key1,key2 from t1 where key1=100 and key2=100; key1 key2 100 100 100 100 explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; key1 key2 key3 key4 100 100 100 100 100 100 -1 -1 -1 -1 100 100 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; key1 key2 key3 key4 filler1 100 100 100 100 key1-key2-key3-key4 100 100 -1 -1 key1-key2 -1 -1 100 100 key4-key3 explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; key1 key2 key3 100 100 100 insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101'); explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 83 Using union(intersect(key1,key2),key3); Using where select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; key1 key2 key3 key4 filler1 100 100 100 100 key1-key2-key3-key4 100 100 -1 -1 key1-key2 101 101 101 101 key1234-101 select key1,key2, filler1 from t1 where key1=100 and key2=100; key1 key2 filler1 100 100 key1-key2-key3-key4 100 100 key1-key2 update t1 set filler1='to be deleted' where key1=100 and key2=100; update t1 set key1=200,key2=200 where key1=100 and key2=100; delete from t1 where key1=200 and key2=200; select key1,key2,filler1 from t1 where key2=100 and key2=200; key1 key2 filler1 explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 152 Using union(intersect(key1,key2),intersect(key3,key4)); Using where select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; key1 key2 key3 key4 filler1 -1 -1 100 100 key4-key3 delete from t1 where key3=100 and key4=100; explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 152 Using union(intersect(key1,key2),intersect(key3,key4)); Using where select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; key1 key2 key3 key4 filler1 explain select key1,key2 from t1 where key1=100 and key2=100; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 57 Using intersect(key1,key2); Using where; Using index select key1,key2 from t1 where key1=100 and key2=100; key1 key2 insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1'); insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2'); insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3'); explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 136 Using union(key3,intersect(key1,key2),key4); Using where select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; key1 key2 key3 key4 filler1 100 100 200 200 key1-key2-key3-key4-3 100 100 200 200 key1-key2-key3-key4-2 100 100 200 200 key1-key2-key3-key4-1 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4'); explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 146 Using union(key3,intersect(key1,key2),key4); Using where select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; key1 key2 key3 key4 filler1 100 100 200 200 key1-key2-key3-key4-3 100 100 200 200 key1-key2-key3-key4-2 100 100 200 200 key1-key2-key3-key4-1 -1 -1 -1 200 key4 insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3'); explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 156 Using union(key3,intersect(key1,key2),key4); Using where select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; key1 key2 key3 key4 filler1 100 100 200 200 key1-key2-key3-key4-3 100 100 200 200 key1-key2-key3-key4-2 100 100 200 200 key1-key2-key3-key4-1 -1 -1 -1 200 key4 -1 -1 200 -1 key3 explain select * from t1 where st_a=1 and st_b=1; id select_type table type possible_keys key key_len ref rows Extra 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 2637 Using intersect(st_a,st_b); Using where explain select st_a,st_b from t1 where st_a=1 and st_b=1; id select_type table type possible_keys key key_len ref rows Extra 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 2637 Using intersect(st_a,st_b); Using where; Using index explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b st_b 4 const 15093 Using where explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971 explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3879 Using where explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 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 44 Using intersect(sta_swt12a,stb_swt1a_2b); Using where explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b) where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL 44 Using intersect(sta_swt12a,stb_swt1b); Using where explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b) where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 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 43 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b) where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 168 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1; id select_type table type possible_keys key key_len ref rows Extra 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 44 Using intersect(sta_swt12a,stb_swt1a_2b); Using where explain select * from t1 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; id select_type table type possible_keys key key_len ref rows Extra 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 174 Using intersect(sta_swt1a,stb_swt1b); Using where explain select st_a from t1 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; id select_type table type possible_keys key key_len ref rows Extra 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 174 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index explain select st_a from t1 where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; id select_type table type possible_keys key key_len ref rows Extra 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 174 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index drop table t0,t1; create table t2 ( a char(10), b char(10), filler1 char(255), filler2 char(255), key(a(5)), key(b(5)) ); select count(a) from t2 where a='BBBBBBBB'; count(a) 4 select count(a) from t2 where b='BBBBBBBB'; count(a) 4 expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA'; id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b 1 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 4 Using where select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA'; count(a) 4 select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA'; count(a) 4 insert into t2 values ('ab', 'ab', 'uh', 'oh'); explain select a from t2 where a='ab'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ref a a 6 const 1 Using where drop table t2; CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '', KEY(c1), KEY(c2), KEY(c3)); INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); INSERT INTO t1 VALUES(0,0,0); CREATE TABLE t2(c1 int); INSERT INTO t2 VALUES(1); DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0; SELECT * FROM t1; c1 c2 c3 DROP TABLE t1,t2;