1SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB; 2drop table if exists t1, t2, t3,t4; 3create table t1 ( 4pk1 int not NULL, 5key1 int(11), 6key2 int(11), 7PRIMARY KEY (pk1), 8KEY key1 (key1), 9KEY key2 (key2) 10); 11insert into t1 values (-5, 1, 1), 12(-100, 1, 1), 13(3, 1, 1), 14(0, 1, 1), 15(10, 1, 1); 16explain select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 17id select_type table partitions type possible_keys key key_len ref rows filtered Extra 181 SIMPLE t1 NULL index_merge key1,key2 key1,key2 5,5 NULL 5 100.00 Using sort_union(key1,key2); Using where 19Warnings: 20Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2` from `test`.`t1` FORCE INDEX (`key2`) FORCE INDEX (`key1`) where ((`test`.`t1`.`key1` < 3) or (`test`.`t1`.`key2` < 3)) 21select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 22pk1 key1 key2 23-100 1 1 24-5 1 1 250 1 1 263 1 1 2710 1 1 28drop table t1; 29create table t1 ( 30pk1 int unsigned not NULL, 31key1 int(11), 32key2 int(11), 33PRIMARY KEY (pk1), 34KEY key1 (key1), 35KEY key2 (key2) 36); 37insert into t1 values (0, 1, 1), 38(0xFFFFFFFF, 1, 1), 39(0xFFFFFFFE, 1, 1), 40(1, 1, 1), 41(2, 1, 1); 42select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 43pk1 key1 key2 440 1 1 451 1 1 462 1 1 474294967294 1 1 484294967295 1 1 49drop table t1; 50create table t1 ( 51pk1 char(4) not NULL, 52key1 int(11), 53key2 int(11), 54PRIMARY KEY (pk1), 55KEY key1 (key1), 56KEY key2 (key2) 57) collate latin2_general_ci; 58insert into t1 values ('a1', 1, 1), 59('b2', 1, 1), 60('A3', 1, 1), 61('B4', 1, 1); 62select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 63pk1 key1 key2 64a1 1 1 65A3 1 1 66b2 1 1 67B4 1 1 68drop table t1; 69create table t1 ( 70pk1 int not NULL, 71pk2 char(4) not NULL collate latin1_german1_ci, 72pk3 char(4) not NULL collate latin1_bin, 73key1 int(11), 74key2 int(11), 75PRIMARY KEY (pk1,pk2,pk3), 76KEY key1 (key1), 77KEY key2 (key2) 78); 79insert into t1 values 80(1, 'u', 'u', 1, 1), 81(1, 'u', char(0xEC), 1, 1), 82(1, 'u', 'x', 1, 1); 83insert ignore into t1 select pk1, char(0xEC), pk3, key1, key2 from t1; 84insert ignore into t1 select pk1, 'x', pk3, key1, key2 from t1 where pk2='u'; 85insert ignore into t1 select 2, pk2, pk3, key1, key2 from t1; 86select * from t1; 87pk1 pk2 pk3 key1 key2 881 � u 1 1 891 � x 1 1 901 � � 1 1 911 u u 1 1 921 u x 1 1 931 u � 1 1 941 x u 1 1 951 x x 1 1 961 x � 1 1 972 � u 1 1 982 � x 1 1 992 � � 1 1 1002 u u 1 1 1012 u x 1 1 1022 u � 1 1 1032 x u 1 1 1042 x x 1 1 1052 x � 1 1 106select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 107pk1 pk2 pk3 key1 key2 1081 � u 1 1 1091 � x 1 1 1101 � � 1 1 1111 u u 1 1 1121 u x 1 1 1131 u � 1 1 1141 x u 1 1 1151 x x 1 1 1161 x � 1 1 1172 � u 1 1 1182 � x 1 1 1192 � � 1 1 1202 u u 1 1 1212 u x 1 1 1222 u � 1 1 1232 x u 1 1 1242 x x 1 1 1252 x � 1 1 126alter table t1 drop primary key; 127select * from t1; 128pk1 pk2 pk3 key1 key2 1291 � u 1 1 1301 � x 1 1 1311 � � 1 1 1321 u u 1 1 1331 u x 1 1 1341 u � 1 1 1351 x u 1 1 1361 x x 1 1 1371 x � 1 1 1382 � u 1 1 1392 � x 1 1 1402 � � 1 1 1412 u u 1 1 1422 u x 1 1 1432 u � 1 1 1442 x u 1 1 1452 x x 1 1 1462 x � 1 1 147select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 148pk1 pk2 pk3 key1 key2 1491 � u 1 1 1501 � x 1 1 1511 � � 1 1 1521 u u 1 1 1531 u x 1 1 1541 u � 1 1 1551 x u 1 1 1561 x x 1 1 1571 x � 1 1 1582 � u 1 1 1592 � x 1 1 1602 � � 1 1 1612 u u 1 1 1622 u x 1 1 1632 u � 1 1 1642 x u 1 1 1652 x x 1 1 1662 x � 1 1 167drop table t1; 168create table t1 ( 169pk1 varchar(8) NOT NULL default '', 170pk2 varchar(4) NOT NULL default '', 171key1 int(11), 172key2 int(11), 173primary key(pk1, pk2), 174KEY key1 (key1), 175KEY key2 (key2) 176); 177insert into t1 values ('','empt',2,2), 178('a','a--a',2,2), 179('bb','b--b',2,2), 180('ccc','c--c',2,2), 181('dddd','d--d',2,2); 182select * from t1 force index(key1, key2) where key1 < 3 or key2 < 3; 183pk1 pk2 key1 key2 184 empt 2 2 185a a--a 2 2 186bb b--b 2 2 187ccc c--c 2 2 188dddd d--d 2 2 189drop table t1; 190