1#---------------- Index merge test 1 ------------------------------------------- 2SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 3drop table if exists t0, t1, t2, t3, t4; 4create table t0 5( 6key1 int not null, 7key2 int not null, 8key3 int not null, 9key4 int not null, 10key5 int not null, 11key6 int not null, 12key7 int not null, 13key8 int not null, 14INDEX i1(key1), 15INDEX i2(key2), 16INDEX i3(key3), 17INDEX i4(key4), 18INDEX i5(key5), 19INDEX i6(key6), 20INDEX i7(key7), 21INDEX i8(key8) 22); 23analyze table t0; 24Table Op Msg_type Msg_text 25test.t0 analyze status OK 26explain select * from t0 where key1 < 3 or key1 > 1020; 27id select_type table type possible_keys key key_len ref rows Extra 281 SIMPLE t0 range i1 i1 4 NULL 78 Using index condition 29explain 30select * from t0 where key1 < 3 or key2 > 1020; 31id select_type table type possible_keys key key_len ref rows Extra 321 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 78 Using sort_union(i1,i2); Using where 33select * from t0 where key1 < 3 or key2 > 1020; 34key1 key2 key3 key4 key5 key6 key7 key8 351 1 1 1 1 1 1 1023 362 2 2 2 2 2 2 1022 371021 1021 1021 1021 1021 1021 1021 3 381022 1022 1022 1022 1022 1022 1022 2 391023 1023 1023 1023 1023 1023 1023 1 401024 1024 1024 1024 1024 1024 1024 0 41explain select * from t0 where key1 < 2 or key2 <3; 42id select_type table type possible_keys key key_len ref rows Extra 431 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 5 Using sort_union(i1,i2); Using where 44explain 45select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 46id select_type table type possible_keys key key_len ref rows Extra 471 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 11 Using sort_union(i1,i2); Using where 48select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); 49key1 key2 key3 key4 key5 key6 key7 key8 5031 31 31 31 31 31 31 993 5132 32 32 32 32 32 32 992 5233 33 33 33 33 33 33 991 5334 34 34 34 34 34 34 990 5435 35 35 35 35 35 35 989 5536 36 36 36 36 36 36 988 5637 37 37 37 37 37 37 987 5738 38 38 38 38 38 38 986 5839 39 39 39 39 39 39 985 59explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4; 60id select_type table type possible_keys key key_len ref rows Extra 611 SIMPLE t0 ALL i1 NULL NULL NULL 1024 Using where 62explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50; 63id select_type table type possible_keys key key_len ref rows Extra 641 SIMPLE t0 ref i1,i2,i3 i3 4 const 1 Using where 65explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50; 66id select_type table type possible_keys key key_len ref rows Extra 671 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 5 Using sort_union(i1,i2); Using where 68explain select * from t0 where (key1 > 1 or key2 > 2); 69id select_type table type possible_keys key key_len ref rows Extra 701 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where 71explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); 72id select_type table type possible_keys key key_len ref rows Extra 731 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 1024 Using sort_union(i1,i2); Using where 74explain 75select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or 76(key1>10 and key1<12) or (key2>100 and key2<102); 77id select_type table type possible_keys key key_len ref rows Extra 781 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 8 Using sort_union(i1,i2); Using where 79explain select * from t0 where key2 = 45 or key1 <=> null; 80id select_type table type possible_keys key key_len ref rows Extra 811 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where 82explain select * from t0 where key2 = 45 or key1 is not null; 83id select_type table type possible_keys key key_len ref rows Extra 841 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where 85explain select * from t0 where key2 = 45 or key1 is null; 86id select_type table type possible_keys key key_len ref rows Extra 871 SIMPLE t0 ref i2 i2 4 const 1 NULL 88explain select * from t0 where key2=10 or key3=3 or key4 <=> null; 89id select_type table type possible_keys key key_len ref rows Extra 901 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where 91explain select * from t0 where key2=10 or key3=3 or key4 is null; 92id select_type table type possible_keys key key_len ref rows Extra 931 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where 94explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or 95(key3=10) or (key4 <=> null); 96id select_type table type possible_keys key key_len ref rows Extra 971 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL 3 Using sort_union(i2,i3); Using where 98explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or 99(key3=10) or (key4 <=> null); 100id select_type table type possible_keys key key_len ref rows Extra 1011 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 6 Using sort_union(i1,i3); Using where 102explain select * from t0 where 103(key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5); 104id select_type table type possible_keys key key_len ref rows Extra 1051 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i3,i4 4,4 NULL 6 Using sort_union(i3,i4); Using where 106explain 107select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); 108id select_type table type possible_keys key key_len ref rows Extra 1091 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where 110select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); 111key1 key2 key3 key4 key5 key6 key7 key8 1121 1 1 1 1 1 1 1023 1132 2 2 2 2 2 2 1022 1143 3 3 3 3 3 3 1021 115explain select * from t0 where 116(key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2); 117id select_type table type possible_keys key key_len ref rows Extra 1181 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i3,i4 4,4 NULL 6 Using sort_union(i3,i4); Using where 119explain select * from t0 where 120(key1 < 3 or key2 < 3) and (key3 < 70); 121id select_type table type possible_keys key key_len ref rows Extra 1221 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where 123explain select * from t0 where 124(key1 < 3 or key2 < 3) and (key3 < 1000); 125id select_type table type possible_keys key key_len ref rows Extra 1261 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where 127explain select * from t0 where 128((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3)) 129or 130key2 > 4; 131id select_type table type possible_keys key key_len ref rows Extra 1321 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where 133explain select * from t0 where 134((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) 135or 136key1 < 5; 137id select_type table type possible_keys key key_len ref rows Extra 1381 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where 139select * from t0 where 140((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) 141or 142key1 < 5; 143key1 key2 key3 key4 key5 key6 key7 key8 1441 1 1 1 1 1 1 1023 1452 2 2 2 2 2 2 1022 1463 3 3 3 3 3 3 1021 1474 4 4 4 4 4 4 1020 148explain select * from t0 where 149((key1 < 2 or key2 < 2) and (key3 <4 or key5 < 3)) 150or 151((key5 < 3 or key6 < 3) and (key7 <3 or key8 < 3)); 152id select_type table type possible_keys key key_len ref rows Extra 1531 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL 10 Using sort_union(i1,i2,i5,i6); Using where 154explain select * from t0 where 155((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) 156or 157((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4)); 158id select_type table type possible_keys key key_len ref rows Extra 1591 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 16 Using sort_union(i3,i5,i7,i8); Using where 160explain select * from t0 where 161((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4)) 162or 163((key3 <4 or key5 < 2) and (key5 < 5 or key6 < 3)); 164id select_type table type possible_keys key key_len ref rows Extra 1651 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 8 Using sort_union(i3,i5); Using where 166explain select * from t0 where 167((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3)) 168or 169(((key3 <5 and key7 < 5) or key5 < 2) and (key5 < 4 or key6 < 4)); 170id select_type table type possible_keys key key_len ref rows Extra 1711 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 8 Using sort_union(i3,i5); Using where 172explain select * from t0 where 173((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 174or 175((key3 >5 or key5 < 2) and (key5 < 5 or key6 < 6)); 176id select_type table type possible_keys key key_len ref rows Extra 1771 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where 178explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 179((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) 180or 181((key3 >4 or key5 < 2) and (key5 < 5 or key6 < 4)); 182id select_type table type possible_keys key key_len ref rows Extra 1831 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 1024 Using sort_union(i3,i5); Using where 184explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where 185((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) 186or 187((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); 188id select_type table type possible_keys key key_len ref rows Extra 1891 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where 190select * from t0 where key1 < 3 or key8 < 2 order by key1; 191key1 key2 key3 key4 key5 key6 key7 key8 1921 1 1 1 1 1 1 1023 1932 2 2 2 2 2 2 1022 1941023 1023 1023 1023 1023 1023 1023 1 1951024 1024 1024 1024 1024 1024 1024 0 196explain 197select * from t0 where key1 < 3 or key8 < 2 order by key1; 198id select_type table type possible_keys key key_len ref rows Extra 1991 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 6 Using sort_union(i1,i8); Using where; Using filesort 200create table t2 like t0; 201insert into t2 select * from t0; 202alter table t2 add index i1_3(key1, key3); 203alter table t2 add index i2_3(key2, key3); 204alter table t2 drop index i1; 205alter table t2 drop index i2; 206alter table t2 add index i321(key3, key2, key1); 207explain select key3 from t2 where key1 = 100 or key2 = 100; 208id select_type table type possible_keys key key_len ref rows Extra 2091 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 2 Using sort_union(i1_3,i2_3); Using where 210explain select key3 from t2 where key1 <100 or key2 < 100; 211id select_type table type possible_keys key key_len ref rows Extra 2121 SIMPLE t2 index i1_3,i2_3 i321 12 NULL 1024 Using where; Using index 213explain select key7 from t2 where key1 <100 or key2 < 100; 214id select_type table type possible_keys key key_len ref rows Extra 2151 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL 1024 Using where 216create table t4 ( 217key1a int not null, 218key1b int not null, 219key2 int not null, 220key2_1 int not null, 221key2_2 int not null, 222key3 int not null, 223index i1a (key1a, key1b), 224index i1b (key1b, key1a), 225index i2_1(key2, key2_1), 226index i2_2(key2, key2_1) 227); 228Warnings: 229Note 1831 Duplicate index 'i2_2' defined on the table 'test.t4'. This is deprecated and will be disallowed in a future release. 230insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0; 231select * from t4 where key1a = 3 or key1b = 4; 232key1a key1b key2 key2_1 key2_2 key3 2333 3 0 3 3 3 2344 4 0 4 4 4 235explain select * from t4 where key1a = 3 or key1b = 4; 236id select_type table type possible_keys key key_len ref rows Extra 2371 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using sort_union(i1a,i1b); Using where 238explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5); 239id select_type table type possible_keys key key_len ref rows Extra 2401 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where 241explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5); 242id select_type table type possible_keys key key_len ref rows Extra 2431 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where 244explain select * from t4 where key2_1 = 1 or key2_2 = 5; 245id select_type table type possible_keys key key_len ref rows Extra 2461 SIMPLE t4 ALL NULL NULL NULL NULL 1024 Using where 247create table t1 like t0; 248insert into t1 select * from t0; 249explain select * from t0 left join t1 on (t0.key1=t1.key1) 250where t0.key1=3 or t0.key2=4; 251id select_type table type possible_keys key key_len ref rows Extra 2521 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2531 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 NULL 254select * from t0 left join t1 on (t0.key1=t1.key1) 255where t0.key1=3 or t0.key2=4; 256key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8 2573 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021 2584 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020 259explain 260select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4); 261id select_type table type possible_keys key key_len ref rows Extra 2621 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2631 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 NULL 264explain 265select * from t0,t1 where (t0.key1=t1.key1) and 266(t0.key1=3 or t0.key2=4) and t1.key1<200; 267id select_type table type possible_keys key key_len ref rows Extra 2681 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2691 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 NULL 270explain 271select * from t0,t1 where (t0.key1=t1.key1) and 272(t0.key1=3 or t0.key2<4) and t1.key1=2; 273id select_type table type possible_keys key key_len ref rows Extra 2741 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where 2751 SIMPLE t1 ref i1 i1 4 const 1 NULL 276explain select * from t0,t1 where t0.key1 = 5 and 277(t1.key1 = t0.key1 or t1.key8 = t0.key1); 278id select_type table type possible_keys key key_len ref rows Extra 2791 SIMPLE t0 ref i1 i1 4 const 1 NULL 2801 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer (Block Nested Loop) 281explain select * from t0,t1 where t0.key1 < 3 and 282(t1.key1 = t0.key1 or t1.key8 = t0.key1); 283id select_type table type possible_keys key key_len ref rows Extra 2841 SIMPLE t0 range i1 i1 4 NULL 3 Using index condition 2851 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81) 286explain select * from t1 where key1=3 or key2=4 287union select * from t1 where key1<4 or key3=5; 288id select_type table type possible_keys key key_len ref rows Extra 2891 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 2902 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where 291NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL Using temporary 292explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; 293id select_type table type possible_keys key key_len ref rows Extra 2941 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where 2952 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where 296create table t3 like t0; 297insert into t3 select * from t0; 298alter table t3 add key9 int not null, add index i9(key9); 299alter table t3 add keyA int not null, add index iA(keyA); 300alter table t3 add keyB int not null, add index iB(keyB); 301alter table t3 add keyC int not null, add index iC(keyC); 302update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1; 303SET @@GLOBAL.innodb_fast_shutdown = 0; 304SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 305explain select * from t3 where 306key1=1 or key2=2 or key3=3 or key4=4 or 307key5=5 or key6=6 or key7=7 or key8=8 or 308key9=9 or keyA=10 or keyB=11 or keyC=12; 309id select_type table type possible_keys key key_len ref rows Extra 3101 SIMPLE t3 index_merge i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC 4,4,4,4,4,4,4,4,4,4,4,4 NULL 12 Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where 311select * from t3 where 312key1=1 or key2=2 or key3=3 or key4=4 or 313key5=5 or key6=6 or key7=7 or key8=8 or 314key9=9 or keyA=10 or keyB=11 or keyC=12; 315key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC 3161 1 1 1 1 1 1 1023 1 1 1 1 3172 2 2 2 2 2 2 1022 2 2 2 2 3183 3 3 3 3 3 3 1021 3 3 3 3 3194 4 4 4 4 4 4 1020 4 4 4 4 3205 5 5 5 5 5 5 1019 5 5 5 5 3216 6 6 6 6 6 6 1018 6 6 6 6 3227 7 7 7 7 7 7 1017 7 7 7 7 3239 9 9 9 9 9 9 1015 9 9 9 9 32410 10 10 10 10 10 10 1014 10 10 10 10 32511 11 11 11 11 11 11 1013 11 11 11 11 32612 12 12 12 12 12 12 1012 12 12 12 12 3271016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016 328explain select * from t0 where key1 < 3 or key2 < 4; 329id select_type table type possible_keys key key_len ref rows Extra 3301 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where 331select * from t0 where key1 < 3 or key2 < 4; 332key1 key2 key3 key4 key5 key6 key7 key8 3331 1 1 1 1 1 1 1023 3342 2 2 2 2 2 2 1022 3353 3 3 3 3 3 3 1021 336update t0 set key8=123 where key1 < 3 or key2 < 4; 337select * from t0 where key1 < 3 or key2 < 4; 338key1 key2 key3 key4 key5 key6 key7 key8 3391 1 1 1 1 1 1 123 3402 2 2 2 2 2 2 123 3413 3 3 3 3 3 3 123 342delete from t0 where key1 < 3 or key2 < 4; 343select * from t0 where key1 < 3 or key2 < 4; 344key1 key2 key3 key4 key5 key6 key7 key8 345select count(*) from t0; 346count(*) 3471021 348drop table t4; 349create table t4 (a int); 350insert into t4 values (1),(4),(3); 351set @save_join_buffer_size=@@join_buffer_size; 352set join_buffer_size= 4096; 353explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 354from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 355where (A.key1 < 500000 or A.key2 < 3) 356and (B.key1 < 500000 or B.key2 < 3); 357id select_type table type possible_keys key key_len ref rows Extra 3581 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where 3591 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where; Using join buffer (Block Nested Loop) 360select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 361from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 362where (A.key1 < 500000 or A.key2 < 3) 363and (B.key1 < 500000 or B.key2 < 3); 364max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 36510240 366update t0 set key1=1; 367explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 368from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 369where (A.key1 = 1 or A.key2 = 1) 370and (B.key1 = 1 or B.key2 = 1); 371id select_type table type possible_keys key key_len ref rows Extra 3721 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where 3731 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where; Using join buffer (Block Nested Loop) 374select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 375from t0 as A force index(i1,i2), t0 as B force index (i1,i2) 376where (A.key1 = 1 or A.key2 = 1) 377and (B.key1 = 1 or B.key2 = 1); 378max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 3798194 380alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200); 381update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500; 382explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 383from t0 as A, t0 as B 384where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) 385and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1); 386id select_type table type possible_keys key key_len ref rows Extra 3871 SIMPLE A index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where 3881 SIMPLE B index_merge i1,i2,i3,i4,i5,i6,i7?,i8 i2,i3,i4,i5,i6,i7?,i8 X NULL # Using union(intersect(i2,i3,i4,i5,i6,i7?),i8); Using where; Using join buffer (Block Nested Loop) 389select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 390from t0 as A, t0 as B 391where (A.key1 = 1 and A.key2 = 1 and A.key3 = 1 and A.key4=1 and A.key5=1 and A.key6=1 and A.key7 = 1 or A.key8=1) 392and (B.key1 = 1 and B.key2 = 1 and B.key3 = 1 and B.key4=1 and B.key5=1 and B.key6=1 and B.key7 = 1 or B.key8=1); 393max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) 3948186 395set join_buffer_size= @save_join_buffer_size; 396drop table t0, t1, t2, t3, t4; 397CREATE TABLE t1 ( 398cola char(3) not null, colb char(3) not null, filler char(200), 399key(cola), key(colb) 400); 401INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ'); 402OPTIMIZE TABLE t1; 403Table Op Msg_type Msg_text 404test.t1 optimize status OK 405select count(*) from t1; 406count(*) 4078704 408explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; 409id select_type table type possible_keys key key_len ref rows Extra 4101 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where 411explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; 412id select_type table type possible_keys key key_len ref rows Extra 4131 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where 414drop table t1; 415create table t0 (a int); 416insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 417create table t1 ( 418a int, b int, 419filler1 char(200), filler2 char(200), 420key(a),key(b) 421); 422insert into t1 select @v:= A.a, @v, 't1', 'filler2' from t0 A, t0 B, t0 C; 423create table t2 like t1; 424create table t3 ( 425a int, b int, 426filler1 char(200), filler2 char(200), 427key(a),key(b) 428) engine=merge union=(t1,t2); 429explain select * from t1 where a=1 and b=1; 430id select_type table type possible_keys key key_len ref rows Extra 4311 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where 432explain select * from t3 where a=1 and b=1; 433id select_type table type possible_keys key key_len ref rows Extra 4341 SIMPLE t3 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where 435drop table t3; 436drop table t0, t1, t2; 437CREATE TABLE t1(a INT); 438INSERT INTO t1 VALUES(1); 439CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b)); 440INSERT INTO t2(a,b) VALUES 441(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 442(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 443(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 444(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 445(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 446(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 447(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 448(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 449(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 450(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 451(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 452(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 453(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 454(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 455(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 456(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 457(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 458(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0), 459(1,2); 460LOCK TABLES t1 WRITE, t2 WRITE; 461INSERT INTO t2(a,b) VALUES(1,2); 462SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1; 463a 4641 4651 466UNLOCK TABLES; 467DROP TABLE t1, t2; 468CREATE TABLE `t1` ( 469`a` int(11) DEFAULT NULL, 470`filler` char(200) DEFAULT NULL, 471`b` int(11) DEFAULT NULL, 472KEY `a` (`a`), 473KEY `b` (`b`) 474) ENGINE=MEMORY DEFAULT CHARSET=latin1; 475insert into t1 values 476(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3), 477(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7), 478(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1), 479(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5), 480(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9), 481(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13), 482(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17), 483(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0), 484(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4); 485create table t2( 486`a` int(11) DEFAULT NULL, 487`filler` char(200) DEFAULT NULL, 488`b` int(11) DEFAULT NULL, 489KEY USING BTREE (`a`), 490KEY USING BTREE (`b`) 491) ENGINE=MEMORY DEFAULT CHARSET=latin1; 492insert into t2 select * from t1; 493must use sort-union rather than union: 494explain select * from t1 where a=4 or b=4; 495id select_type table type possible_keys key key_len ref rows Extra 4961 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where 497select * from t1 where a=4 or b=4; 498a filler b 4994 4 0 5004 5 0 5014 filler 4 5024 filler 4 5034 qq 5 5044 zz 4 5055 qq 4 506select * from t1 ignore index(a,b) where a=4 or b=4; 507a filler b 5084 4 0 5094 5 0 5104 filler 4 5114 filler 4 5124 qq 5 5134 zz 4 5145 qq 4 515must use union, not sort-union: 516explain select * from t2 where a=4 or b=4; 517id select_type table type possible_keys key key_len ref rows Extra 5181 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where 519select * from t2 where a=4 or b=4; 520a filler b 5214 4 0 5224 5 0 5234 filler 4 5244 filler 4 5254 qq 5 5264 zz 4 5275 qq 4 528drop table t1, t2; 529CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'), 530KEY b(b), KEY a(a)); 531INSERT INTO t1 VALUES ('y',''), ('z',''); 532SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR 533(a='pure-S') OR (a='DE80337a') OR (a='DE80799'); 534b a 535 y 536 z 537DROP TABLE t1; 538# 539# BUG#40974: Incorrect query results when using clause evaluated using range check 540# 541create table t0 (a int); 542insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 543create table t1 (a int); 544insert into t1 values (1),(2); 545create table t2(a int, b int); 546insert into t2 values (1,1), (2, 1000); 547create table t3 (a int, b int, filler char(100), key(a), key(b)); 548insert into t3 select 1000, 1000,'filler' from t0 A, t0 B, t0 C; 549insert into t3 values (1,1,'data'); 550insert into t3 values (1,1,'data'); 551The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3) 552explain select * from t1 553where exists (select 1 from t2, t3 554where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 555id select_type table type possible_keys key key_len ref rows Extra 5561 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 5572 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 5582 DEPENDENT SUBQUERY t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) 559select * from t1 560where exists (select 1 from t2, t3 561where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); 562a 5631 5642 565drop table t0, t1, t2, t3; 566# 567# BUG#44810: index merge and order by with low sort_buffer_size 568# crashes server! 569# 570CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B)); 571INSERT INTO t1 VALUES (REPEAT('a',128),REPEAT('b',128)); 572INSERT INTO t1 SELECT * FROM t1; 573INSERT INTO t1 SELECT * FROM t1; 574INSERT INTO t1 SELECT * FROM t1; 575INSERT INTO t1 SELECT * FROM t1; 576INSERT INTO t1 SELECT * FROM t1; 577INSERT INTO t1 SELECT * FROM t1; 578SET SESSION sort_buffer_size=1; 579Warnings: 580Warning 1292 Truncated incorrect sort_buffer_size value: '1' 581EXPLAIN 582SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 583ORDER BY a,b; 584id select_type table type possible_keys key key_len ref rows Extra 5851 SIMPLE t1 index_merge a,b a,b 131,131 NULL 64 Using sort_union(a,b); Using where; Using filesort 586SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' 587ORDER BY a,b; 588SET SESSION sort_buffer_size=DEFAULT; 589DROP TABLE t1; 590End of 5.0 tests 591#---------------- ROR-index_merge tests ----------------------- 592SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 593drop table if exists t0,t1,t2; 594create table t1 595( 596/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */ 597st_a int not null default 0, 598swt1a int not null default 0, 599swt2a int not null default 0, 600st_b int not null default 0, 601swt1b int not null default 0, 602swt2b int not null default 0, 603/* fields/keys for row retrieval tests */ 604key1 int, 605key2 int, 606key3 int, 607key4 int, 608/* make rows much bigger then keys */ 609filler1 char (200), 610filler2 char (200), 611filler3 char (200), 612filler4 char (200), 613filler5 char (200), 614filler6 char (200), 615/* order of keys is important */ 616key sta_swt12a(st_a,swt1a,swt2a), 617key sta_swt1a(st_a,swt1a), 618key sta_swt2a(st_a,swt2a), 619key sta_swt21a(st_a,swt2a,swt1a), 620key st_a(st_a), 621key stb_swt1a_2b(st_b,swt1b,swt2a), 622key stb_swt1b(st_b,swt1b), 623key st_b(st_b), 624key(key1), 625key(key2), 626key(key3), 627key(key4) 628) ; 629create table t0 as select * from t1; 630# Printing of many insert into t0 values (....) disabled. 631alter table t1 disable keys; 632# Printing of many insert into t1 select .... from t0 disabled. 633# Printing of many insert into t1 (...) values (....) disabled. 634alter table t1 enable keys; 635select count(*) from t1; 636count(*) 63764801 638explain select key1,key2 from t1 where key1=100 and key2=100; 639id select_type table type possible_keys key key_len ref rows Extra 6401 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index 641select key1,key2 from t1 where key1=100 and key2=100; 642key1 key2 643100 100 644explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 645id select_type table type possible_keys key key_len ref rows Extra 6461 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 647explain format=json select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 648EXPLAIN 649{ 650 "query_block": { 651 "select_id": 1, 652 "table": { 653 "table_name": "t1", 654 "access_type": "index_merge", 655 "possible_keys": [ 656 "key1", 657 "key2", 658 "key3", 659 "key4" 660 ], 661 "key": "union(intersect(key1,key2),intersect(key3,key4))", 662 "key_length": "5,5,5,5", 663 "rows": 154, 664 "filtered": 50, 665 "attached_condition": "(((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100)))" 666 } 667 } 668} 669Warnings: 670Note 1003 /* select#1 */ select `test`.`t1`.`key1` AS `key1`,`test`.`t1`.`key2` AS `key2`,`test`.`t1`.`key3` AS `key3`,`test`.`t1`.`key4` AS `key4`,`test`.`t1`.`filler1` AS `filler1` from `test`.`t1` where (((`test`.`t1`.`key2` = 100) and (`test`.`t1`.`key1` = 100)) or ((`test`.`t1`.`key4` = 100) and (`test`.`t1`.`key3` = 100))) 671select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 672key1 key2 key3 key4 filler1 673100 100 100 100 key1-key2-key3-key4 674insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2'); 675insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3'); 676explain select key1,key2,filler1 from t1 where key1=100 and key2=100; 677id select_type table type possible_keys key key_len ref rows Extra 6781 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where 679select key1,key2,filler1 from t1 where key1=100 and key2=100; 680key1 key2 filler1 681100 100 key1-key2-key3-key4 682100 100 key1-key2 683explain select key1,key2 from t1 where key1=100 and key2=100; 684id select_type table type possible_keys key key_len ref rows Extra 6851 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index 686select key1,key2 from t1 where key1=100 and key2=100; 687key1 key2 688100 100 689100 100 690explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 691id select_type table type possible_keys key key_len ref rows Extra 6921 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 693select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 694key1 key2 key3 key4 695100 100 100 100 696100 100 -1 -1 697-1 -1 100 100 698explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 699id select_type table type possible_keys key key_len ref rows Extra 7001 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 701select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 702key1 key2 key3 key4 filler1 703100 100 100 100 key1-key2-key3-key4 704100 100 -1 -1 key1-key2 705-1 -1 100 100 key4-key3 706explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; 707id select_type table type possible_keys key key_len ref rows Extra 7081 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index 709select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100; 710key1 key2 key3 711100 100 100 712insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101'); 713explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; 714id select_type table type possible_keys key key_len ref rows Extra 7151 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 83 Using union(intersect(key1,key2),key3); Using where 716select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101; 717key1 key2 key3 key4 filler1 718100 100 100 100 key1-key2-key3-key4 719100 100 -1 -1 key1-key2 720101 101 101 101 key1234-101 721select key1,key2, filler1 from t1 where key1=100 and key2=100; 722key1 key2 filler1 723100 100 key1-key2-key3-key4 724100 100 key1-key2 725update t1 set filler1='to be deleted' where key1=100 and key2=100; 726update t1 set key1=200,key2=200 where key1=100 and key2=100; 727delete from t1 where key1=200 and key2=200; 728select key1,key2,filler1 from t1 where key2=100 and key2=200; 729key1 key2 filler1 730explain select key1,key2,key34,key34,filler1 from t1 where key1=100 and key2=100 or key34=100 and key34=100; 731id select_type table type possible_keys key key_len ref rows Extra 7321 SIMPLE t1 index_merge key1,key2,key34,key34 key1,key2,key34,key34 5,5,5,5 NULL 152 Using union(intersect(key1,key2),intersect(key34,key34)); Using where 733select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 734key1 key2 key3 key4 filler1 735-1 -1 100 100 key4-key3 736delete from t1 where key3=100 and key4=100; 737SET @@GLOBAL.innodb_fast_shutdown = 0; 738SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 739explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 740id select_type table type possible_keys key key_len ref rows Extra 7411 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 742select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100; 743key1 key2 key3 key4 filler1 744explain select key1,key2 from t1 where key1=100 and key2=100; 745id select_type table type possible_keys key key_len ref rows Extra 7461 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 76 Using intersect(key1,key2); Using where; Using index 747select key1,key2 from t1 where key1=100 and key2=100; 748key1 key2 749insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1'); 750insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2'); 751insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3'); 752explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 753id select_type table type possible_keys key key_len ref rows Extra 7541 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 755select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 756key1 key2 key3 key4 filler1 757100 100 200 200 key1-key2-key3-key4-3 758100 100 200 200 key1-key2-key3-key4-2 759100 100 200 200 key1-key2-key3-key4-1 760insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4'); 761explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 762id select_type table type possible_keys key key_len ref rows Extra 7631 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 764select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 765key1 key2 key3 key4 filler1 766100 100 200 200 key1-key2-key3-key4-3 767100 100 200 200 key1-key2-key3-key4-2 768100 100 200 200 key1-key2-key3-key4-1 769-1 -1 -1 200 key4 770insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3'); 771explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 772id select_type table type possible_keys key key_len ref rows Extra 7731 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 774select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200; 775key1 key2 key3 key4 filler1 776100 100 200 200 key1-key2-key3-key4-3 777100 100 200 200 key1-key2-key3-key4-2 778100 100 200 200 key1-key2-key3-key4-1 779-1 -1 -1 200 key4 780-1 -1 200 -1 key3 781explain select * from t1 where st_a=1 and st_b=1; 782id select_type table type possible_keys key key_len ref rows Extra 7831 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 3515 Using intersect(st_a,st_b); Using where 784explain select st_a,st_b from t1 where st_a=1 and st_b=1; 785id select_type table type possible_keys key key_len ref rows Extra 7861 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 3515 Using intersect(st_a,st_b); Using where; Using index 787explain select st_a from t1 ignore index (st_a) where st_a=1 and st_b=1; 788id select_type table type possible_keys key key_len ref rows Extra 7891 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 790explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1; 791id select_type table type possible_keys key key_len ref rows Extra 7921 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971 NULL 793explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1; 794id select_type table type possible_keys key key_len ref rows Extra 7951 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3879 Using where 796explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 797id select_type table type possible_keys key key_len ref rows Extra 7981 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 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where 799explain select * from t1 ignore index (sta_swt21a, stb_swt1a_2b) 800where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 801id select_type table type possible_keys key key_len ref rows Extra 8021 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 803explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b) 804where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 805id select_type table type possible_keys key key_len ref rows Extra 8061 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt1a,stb_swt1b,sta_swt2a 8,8,8 NULL 57 Using intersect(sta_swt1a,stb_swt1b,sta_swt2a); Using where 807explain select * from t1 ignore index (sta_swt21a, sta_swt12a, stb_swt1a_2b, stb_swt1b) 808where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1; 809id select_type table type possible_keys key key_len ref rows Extra 8101 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL 223 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where 811explain select * from t1 812where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1; 813id select_type table type possible_keys key key_len ref rows Extra 8141 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 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where 815explain select * from t1 816where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; 817id select_type table type possible_keys key key_len ref rows Extra 8181 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 232 Using intersect(sta_swt1a,stb_swt1b); Using where 819explain select st_a from t1 820where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; 821id select_type table type possible_keys key key_len ref rows Extra 8221 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 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index 823explain select st_a from t1 824where st_a=1 and swt1a=1 and st_b=1 and swt1b=1 and swt1b=1; 825id select_type table type possible_keys key key_len ref rows Extra 8261 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 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index 827drop table t0,t1; 828create table t2 ( 829a char(10), 830b char(10), 831filler1 char(255), 832filler2 char(255), 833key(a(5)), 834key(b(5)) 835); 836select count(a) from t2 where a='BBBBBBBB'; 837count(a) 8384 839select count(a) from t2 where b='BBBBBBBB'; 840count(a) 8414 842expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA'; 843id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b 8441 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 4 Using where 845select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA'; 846count(a) 8474 848select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA'; 849count(a) 8504 851insert into t2 values ('ab', 'ab', 'uh', 'oh'); 852explain select a from t2 where a='ab'; 853id select_type table type possible_keys key key_len ref rows Extra 8541 SIMPLE t2 ref a a 6 const 1 Using where 855drop table t2; 856CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '', 857KEY(c1), KEY(c2), KEY(c3)); 858INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0), 859(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0); 860INSERT INTO t1 VALUES(0,0,0); 861CREATE TABLE t2(c1 int); 862INSERT INTO t2 VALUES(1); 863DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0; 864SELECT * FROM t1; 865c1 c2 c3 866DROP TABLE t1,t2; 867#---------------- Index merge test 2 ------------------------------------------- 868SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 869drop table if exists t1,t2; 870create table t1 871( 872key1 int not null, 873key2 int not null, 874INDEX i1(key1), 875INDEX i2(key2) 876); 877explain select * from t1 where key1 < 5 or key2 > 197; 878id select_type table type possible_keys key key_len ref rows Extra 8791 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where 880select * from t1 where key1 < 5 or key2 > 197; 881key1 key2 8820 200 8831 199 8842 198 8853 197 8864 196 887explain select * from t1 where key1 < 3 or key2 > 195; 888id select_type table type possible_keys key key_len ref rows Extra 8891 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where 890select * from t1 where key1 < 3 or key2 > 195; 891key1 key2 8920 200 8931 199 8942 198 8953 197 8964 196 897alter table t1 add str1 char (255) not null, 898add zeroval int not null default 0, 899add str2 char (255) not null, 900add str3 char (255) not null; 901update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A')); 902alter table t1 add primary key (str1, zeroval, str2, str3); 903explain select * from t1 where key1 < 5 or key2 > 197; 904id select_type table type possible_keys key key_len ref rows Extra 9051 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where 906select * from t1 where key1 < 5 or key2 > 197; 907key1 key2 str1 zeroval str2 str3 9080 200 aaa 0 bbb 200-0_a 9091 199 aaa 0 bbb 199-0_A 9102 198 aaa 0 bbb 198-1_a 9113 197 aaa 0 bbb 197-1_A 9124 196 aaa 0 bbb 196-2_a 913explain select * from t1 where key1 < 3 or key2 > 195; 914id select_type table type possible_keys key key_len ref rows Extra 9151 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where 916select * from t1 where key1 < 3 or key2 > 195; 917key1 key2 str1 zeroval str2 str3 9180 200 aaa 0 bbb 200-0_a 9191 199 aaa 0 bbb 199-0_A 9202 198 aaa 0 bbb 198-1_a 9213 197 aaa 0 bbb 197-1_A 9224 196 aaa 0 bbb 196-2_a 923drop table t1; 924create table t1 ( 925pk integer not null auto_increment primary key, 926key1 integer, 927key2 integer not null, 928filler char (200), 929index (key1), 930index (key2) 931); 932show warnings; 933Level Code Message 934explain select pk from t1 where key1 = 1 and key2 = 1; 935id select_type table type possible_keys key key_len ref rows Extra 9361 SIMPLE t1 ref key1,key2 key1 5 const 4 Using where 937select pk from t1 where key2 = 1 and key1 = 1; 938pk 93926 94027 941select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1; 942pk 94326 94427 945drop table t1; 946create table t1 ( 947pk int primary key auto_increment, 948key1a int, 949key2a int, 950key1b int, 951key2b int, 952dummy1 int, 953dummy2 int, 954dummy3 int, 955dummy4 int, 956key3a int, 957key3b int, 958filler1 char (200), 959index i1(key1a, key1b), 960index i2(key2a, key2b), 961index i3(key3a, key3b) 962); 963create table t2 (a int); 964insert into t2 values (0),(1),(2),(3),(4),(NULL); 965insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 966select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D; 967insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 968select key1a, key1b, key2a, key2b, key3a, key3b from t1; 969insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b) 970select key1a, key1b, key2a, key2b, key3a, key3b from t1; 971analyze table t1; 972Table Op Msg_type Msg_text 973test.t1 analyze status OK 974select count(*) from t1; 975count(*) 9765184 977explain select count(*) from t1 where 978key1a = 2 and key1b is null and key2a = 2 and key2b is null; 979id select_type table type possible_keys key key_len ref rows Extra 9801 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL 2 Using intersect(i1,i2); Using where; Using index 981select count(*) from t1 where 982key1a = 2 and key1b is null and key2a = 2 and key2b is null; 983count(*) 9844 985explain select count(*) from t1 where 986key1a = 2 and key1b is null and key3a = 2 and key3b is null; 987id select_type table type possible_keys key key_len ref rows Extra 9881 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL 2 Using intersect(i1,i3); Using where; Using index 989select count(*) from t1 where 990key1a = 2 and key1b is null and key3a = 2 and key3b is null; 991count(*) 9924 993drop table t1,t2; 994create table t1 ( 995id1 int, 996id2 date , 997index idx2 (id1,id2), 998index idx1 (id2) 999); 1000insert into t1 values(1,'20040101'), (2,'20040102'); 1001select * from t1 where id1 = 1 and id2= '20040101'; 1002id1 id2 10031 2004-01-01 1004drop table t1; 1005drop view if exists v1; 1006CREATE TABLE t1 ( 1007`oid` int(11) unsigned NOT NULL auto_increment, 1008`fk_bbk_niederlassung` int(11) unsigned NOT NULL, 1009`fk_wochentag` int(11) unsigned NOT NULL, 1010`uhrzeit_von` time NOT NULL COMMENT 'HH:MM', 1011`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM', 1012`geloescht` tinyint(4) NOT NULL, 1013`version` int(5) NOT NULL, 1014PRIMARY KEY (`oid`), 1015KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`), 1016KEY `fk_wochentag` (`fk_wochentag`), 1017KEY `ix_version` (`version`) 1018) DEFAULT CHARSET=latin1; 1019insert into t1 values 1020(1, 38, 1, '08:00:00', '13:00:00', 0, 1), 1021(2, 38, 2, '08:00:00', '13:00:00', 0, 1), 1022(3, 38, 3, '08:00:00', '13:00:00', 0, 1), 1023(4, 38, 4, '08:00:00', '13:00:00', 0, 1), 1024(5, 38, 5, '08:00:00', '13:00:00', 0, 1), 1025(6, 38, 5, '08:00:00', '13:00:00', 1, 2), 1026(7, 38, 3, '08:00:00', '13:00:00', 1, 2), 1027(8, 38, 1, '08:00:00', '13:00:00', 1, 2), 1028(9, 38, 2, '08:00:00', '13:00:00', 1, 2), 1029(10, 38, 4, '08:00:00', '13:00:00', 1, 2), 1030(11, 38, 1, '08:00:00', '13:00:00', 0, 3), 1031(12, 38, 2, '08:00:00', '13:00:00', 0, 3), 1032(13, 38, 3, '08:00:00', '13:00:00', 0, 3), 1033(14, 38, 4, '08:00:00', '13:00:00', 0, 3), 1034(15, 38, 5, '08:00:00', '13:00:00', 0, 3), 1035(16, 38, 4, '08:00:00', '13:00:00', 0, 4), 1036(17, 38, 5, '08:00:00', '13:00:00', 0, 4), 1037(18, 38, 1, '08:00:00', '13:00:00', 0, 4), 1038(19, 38, 2, '08:00:00', '13:00:00', 0, 4), 1039(20, 38, 3, '08:00:00', '13:00:00', 0, 4), 1040(21, 7, 1, '08:00:00', '13:00:00', 0, 1), 1041(22, 7, 2, '08:00:00', '13:00:00', 0, 1), 1042(23, 7, 3, '08:00:00', '13:00:00', 0, 1), 1043(24, 7, 4, '08:00:00', '13:00:00', 0, 1), 1044(25, 7, 5, '08:00:00', '13:00:00', 0, 1); 1045create view v1 as 1046select 1047zeit1.oid AS oid, 1048zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung, 1049zeit1.fk_wochentag AS fk_wochentag, 1050zeit1.uhrzeit_von AS uhrzeit_von, 1051zeit1.uhrzeit_bis AS uhrzeit_bis, 1052zeit1.geloescht AS geloescht, 1053zeit1.version AS version 1054from 1055t1 zeit1 1056where 1057(zeit1.version = 1058(select max(zeit2.version) AS `max(version)` 1059 from t1 zeit2 1060where 1061((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and 1062(zeit1.fk_wochentag = zeit2.fk_wochentag) and 1063(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and 1064(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis) 1065) 1066) 1067) 1068and (zeit1.geloescht = 0); 1069select * from v1 where oid = 21; 1070oid fk_bbk_niederlassung fk_wochentag uhrzeit_von uhrzeit_bis geloescht version 107121 7 1 08:00:00 13:00:00 0 1 1072drop view v1; 1073drop table t1; 1074CREATE TABLE t1( 1075t_cpac varchar(2) NOT NULL, 1076t_vers varchar(4) NOT NULL, 1077t_rele varchar(2) NOT NULL, 1078t_cust varchar(4) NOT NULL, 1079filler1 char(250) default NULL, 1080filler2 char(250) default NULL, 1081PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust), 1082UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele), 1083KEY IX_5 (t_vers,t_rele,t_cust) 1084); 1085insert into t1 values 1086('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''), 1087('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''), 1088('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''), 1089('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''), 1090('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''), 1091('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''), 1092('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''), 1093('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''), 1094('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''), 1095('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''), 1096('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''), 1097('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''), 1098('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''), 1099('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''), 1100('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''), 1101('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''), 1102('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''), 1103('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''), 1104('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''), 1105('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''), 1106('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''), 1107('wh','B61U','a ','stnd','',''); 1108show create table t1; 1109Table Create Table 1110t1 CREATE TABLE `t1` ( 1111 `t_cpac` varchar(2) NOT NULL, 1112 `t_vers` varchar(4) NOT NULL, 1113 `t_rele` varchar(2) NOT NULL, 1114 `t_cust` varchar(4) NOT NULL, 1115 `filler1` char(250) DEFAULT NULL, 1116 `filler2` char(250) DEFAULT NULL, 1117 PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`), 1118 UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`), 1119 KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`) 1120) ENGINE=MyISAM DEFAULT CHARSET=latin1 1121select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'; 1122t_vers t_rele t_cust filler1 11237.6 a 11247.6 a 1125select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6' 1126 and t_rele='a' and t_cust = ' '; 1127t_vers t_rele t_cust filler1 11287.6 a 11297.6 a 1130drop table t1; 1131create table t1 ( 1132pk int(11) not null auto_increment, 1133a int(11) not null default '0', 1134b int(11) not null default '0', 1135c int(11) not null default '0', 1136filler1 datetime, filler2 varchar(15), 1137filler3 longtext, 1138kp1 varchar(4), kp2 varchar(7), 1139kp3 varchar(2), kp4 varchar(4), 1140kp5 varchar(7), 1141filler4 char(1), 1142primary key (pk), 1143key idx1(a,b,c), 1144key idx2(c), 1145key idx3(kp1,kp2,kp3,kp4,kp5) 1146) default charset=latin1; 1147set @fill=NULL; 1148SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND 1149kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R '; 1150COUNT(*) 11511 1152drop table t1; 1153create table t1 1154( 1155key1 int not null, 1156key2 int not null default 0, 1157key3 int not null default 0 1158); 1159insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); 1160set @d=8; 1161insert into t1 (key1) select key1+@d from t1; 1162set @d=@d*2; 1163insert into t1 (key1) select key1+@d from t1; 1164set @d=@d*2; 1165insert into t1 (key1) select key1+@d from t1; 1166set @d=@d*2; 1167insert into t1 (key1) select key1+@d from t1; 1168set @d=@d*2; 1169insert into t1 (key1) select key1+@d from t1; 1170set @d=@d*2; 1171insert into t1 (key1) select key1+@d from t1; 1172set @d=@d*2; 1173insert into t1 (key1) select key1+@d from t1; 1174set @d=@d*2; 1175alter table t1 add index i2(key2); 1176alter table t1 add index i3(key3); 1177update t1 set key2=key1,key3=key1; 1178SET @@GLOBAL.innodb_fast_shutdown = 0; 1179SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 1180explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); 1181id select_type table type possible_keys key key_len ref rows Extra 11821 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where 1183select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); 1184key1 key2 key3 118531 31 31 118632 32 32 118733 33 33 118834 34 34 118935 35 35 119036 36 36 119137 37 37 119238 38 38 119339 39 39 1194drop table t1; 1195# 1196# Bug#56423: Different count with SELECT and CREATE SELECT queries 1197# 1198CREATE TABLE t1 ( 1199a INT, 1200b INT, 1201c INT, 1202d INT, 1203PRIMARY KEY (a), 1204KEY (c), 1205KEY bd (b,d) 1206); 1207INSERT INTO t1 VALUES 1208(1, 0, 1, 0), 1209(2, 1, 1, 1), 1210(3, 1, 1, 1), 1211(4, 0, 1, 1); 1212EXPLAIN 1213SELECT a 1214FROM t1 1215WHERE c = 1 AND b = 1 AND d = 1; 1216id select_type table type possible_keys key key_len ref rows Extra 12171 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where 1218CREATE TABLE t2 ( a INT ) 1219SELECT a 1220FROM t1 1221WHERE c = 1 AND b = 1 AND d = 1; 1222SELECT * FROM t2; 1223a 12242 12253 1226DROP TABLE t1, t2; 1227CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) ); 1228INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2); 1229SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2; 1230a b 12311 2 12321 2 12331 2 12341 2 1235DROP TABLE t1; 1236# Code coverage of fix. 1237CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT); 1238INSERT INTO t1 (b) VALUES (1); 1239UPDATE t1 SET b = 2 WHERE a = 1; 1240SELECT * FROM t1; 1241a b 12421 2 1243CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) ); 1244INSERT INTO t2 (b) VALUES ('a'); 1245UPDATE t2 SET b = 'b' WHERE a = 1; 1246SELECT * FROM t2; 1247a b 12481 b 1249DROP TABLE t1, t2; 1250# 1251# BUG#13970015: ASSERT `MIN_ENDP || MAX_ENDP' FAILED IN 1252# HANDLER::MULTI_RANGE_READ_INFO_CONST 1253# 1254CREATE TABLE t1 ( 1255pk INT NOT NULL, 1256col_int_key INT NOT NULL, 1257col_varchar_key VARCHAR(1) NOT NULL, 1258PRIMARY KEY (pk), 1259KEY col_int_key (col_int_key), 1260KEY col_varchar_key (col_varchar_key,col_int_key) 1261); 1262INSERT INTO t1 VALUES (1,1,'a'), (2,2,'b'); 1263EXPLAIN 1264SELECT col_int_key 1265FROM t1 1266WHERE col_varchar_key >= 'l' OR 1267(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') 1268AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); 1269id select_type table type possible_keys key key_len ref rows Extra 12701 SIMPLE t1 ALL PRIMARY,col_int_key,col_varchar_key NULL NULL NULL 2 Using where 1271SELECT col_int_key 1272FROM t1 1273WHERE col_varchar_key >= 'l' OR 1274(((pk BETWEEN 141 AND 141) OR col_varchar_key <> 'l') 1275AND ((pk BETWEEN 141 AND 141) OR (col_int_key > 141))); 1276col_int_key 1277DROP TABLE t1; 1278#---------------- 2-sweeps read Index merge test 2 ------------------------------- 1279SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 1280drop table if exists t1; 1281create table t1 ( 1282pk int primary key, 1283key1 int, 1284key2 int, 1285filler char(200), 1286filler2 char(200), 1287index(key1), 1288index(key2) 1289); 1290select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 ); 1291pk key1 key2 filler filler2 129210 10 10 filler-data filler-data-2 12939 9 9 filler-data filler-data-2 12948 8 8 filler-data filler-data-2 12957 7 7 filler-data filler-data-2 12966 6 6 filler-data filler-data-2 12975 5 5 filler-data filler-data-2 12984 4 4 filler-data filler-data-2 12993 3 3 filler-data filler-data-2 13002 2 2 filler-data filler-data-2 1301set @maxv=1000; 1302select * from t1 where 1303(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1304or key1=18 or key1=60; 1305pk key1 key2 filler filler2 13061000 1000 1000 filler-data filler-data-2 1307999 999 999 filler-data filler-data-2 1308998 998 998 filler-data filler-data-2 1309997 997 997 filler-data filler-data-2 1310996 996 996 filler-data filler-data-2 1311995 995 995 filler-data filler-data-2 1312994 994 994 filler-data filler-data-2 1313993 993 993 filler-data filler-data-2 1314992 992 992 filler-data filler-data-2 1315991 991 991 filler-data filler-data-2 131660 60 60 filler-data filler-data-2 131754 54 54 filler-data filler-data-2 131853 53 53 filler-data filler-data-2 131952 52 52 filler-data filler-data-2 132051 51 51 filler-data filler-data-2 132150 50 50 filler-data filler-data-2 132218 18 18 filler-data filler-data-2 132314 14 14 filler-data filler-data-2 132413 13 13 filler-data filler-data-2 132512 12 12 filler-data filler-data-2 132611 11 11 filler-data filler-data-2 13274 4 4 filler-data filler-data-2 13283 3 3 filler-data filler-data-2 13292 2 2 filler-data filler-data-2 13301 1 1 filler-data filler-data-2 1331select * from t1 where 1332(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1333or key1 < 3 or key1 > @maxv-11; 1334pk key1 key2 filler filler2 13351000 1000 1000 filler-data filler-data-2 1336999 999 999 filler-data filler-data-2 1337998 998 998 filler-data filler-data-2 1338997 997 997 filler-data filler-data-2 1339996 996 996 filler-data filler-data-2 1340995 995 995 filler-data filler-data-2 1341994 994 994 filler-data filler-data-2 1342993 993 993 filler-data filler-data-2 1343992 992 992 filler-data filler-data-2 1344991 991 991 filler-data filler-data-2 1345990 990 990 filler-data filler-data-2 134654 54 54 filler-data filler-data-2 134753 53 53 filler-data filler-data-2 134852 52 52 filler-data filler-data-2 134951 51 51 filler-data filler-data-2 135050 50 50 filler-data filler-data-2 135114 14 14 filler-data filler-data-2 135213 13 13 filler-data filler-data-2 135312 12 12 filler-data filler-data-2 135411 11 11 filler-data filler-data-2 13554 4 4 filler-data filler-data-2 13563 3 3 filler-data filler-data-2 13572 2 2 filler-data filler-data-2 13581 1 1 filler-data filler-data-2 1359select * from t1 where 1360(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10) 1361or 1362(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10); 1363pk key1 key2 filler filler2 13641000 1000 1000 filler-data filler-data-2 1365999 999 999 filler-data filler-data-2 1366998 998 998 filler-data filler-data-2 1367997 997 997 filler-data filler-data-2 1368996 996 996 filler-data filler-data-2 1369995 995 995 filler-data filler-data-2 1370994 994 994 filler-data filler-data-2 1371993 993 993 filler-data filler-data-2 1372992 992 992 filler-data filler-data-2 1373991 991 991 filler-data filler-data-2 137454 54 54 filler-data filler-data-2 137553 53 53 filler-data filler-data-2 137652 52 52 filler-data filler-data-2 137751 51 51 filler-data filler-data-2 137850 50 50 filler-data filler-data-2 137914 14 14 filler-data filler-data-2 138013 13 13 filler-data filler-data-2 138112 12 12 filler-data filler-data-2 138211 11 11 filler-data filler-data-2 13834 4 4 filler-data filler-data-2 13843 3 3 filler-data filler-data-2 13852 2 2 filler-data filler-data-2 13861 1 1 filler-data filler-data-2 1387select * from t1 where 1388(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) 1389or 1390(key1 < 5) or (key1 > @maxv-10); 1391pk key1 key2 filler filler2 13921000 1000 1000 filler-data filler-data-2 1393999 999 999 filler-data filler-data-2 1394998 998 998 filler-data filler-data-2 1395997 997 997 filler-data filler-data-2 1396996 996 996 filler-data filler-data-2 1397995 995 995 filler-data filler-data-2 1398994 994 994 filler-data filler-data-2 1399993 993 993 filler-data filler-data-2 1400992 992 992 filler-data filler-data-2 1401991 991 991 filler-data filler-data-2 140254 54 54 filler-data filler-data-2 140353 53 53 filler-data filler-data-2 140452 52 52 filler-data filler-data-2 140551 51 51 filler-data filler-data-2 140650 50 50 filler-data filler-data-2 140714 14 14 filler-data filler-data-2 140813 13 13 filler-data filler-data-2 140912 12 12 filler-data filler-data-2 141011 11 11 filler-data filler-data-2 14114 4 4 filler-data filler-data-2 14123 3 3 filler-data filler-data-2 14132 2 2 filler-data filler-data-2 14141 1 1 filler-data filler-data-2 1415drop table t1; 1416#---------------- Clustered PK ROR-index_merge tests ----------------------------- 1417SET SESSION DEFAULT_STORAGE_ENGINE = MyISAM; 1418drop table if exists t1; 1419create table t1 1420( 1421pk1 int not null, 1422pk2 int not null, 1423key1 int not null, 1424key2 int not null, 1425pktail1ok int not null, 1426pktail2ok int not null, 1427pktail3bad int not null, 1428pktail4bad int not null, 1429pktail5bad int not null, 1430pk2copy int not null, 1431badkey int not null, 1432filler1 char (200), 1433filler2 char (200), 1434key (key1), 1435key (key2), 1436/* keys with tails from CPK members */ 1437key (pktail1ok, pk1), 1438key (pktail2ok, pk1, pk2), 1439key (pktail3bad, pk2, pk1), 1440key (pktail4bad, pk1, pk2copy), 1441key (pktail5bad, pk1, pk2, pk2copy), 1442primary key (pk1, pk2) 1443); 1444explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; 1445id select_type table type possible_keys key key_len ref rows Extra 14461 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL ROWS Using index condition; Using where 1447select * from t1 where pk1 = 1 and pk2 < 80 and key1=0; 1448pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2 14491 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2 14501 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2 14511 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2 14521 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2 14531 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2 14541 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2 14551 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2 14561 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2 14571 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2 14581 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2 1459explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; 1460id select_type table type possible_keys key key_len ref rows Extra 14611 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where 1462select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1; 1463pk1 pk2 146495 59 146595 58 146695 57 146795 56 146895 55 146995 54 147095 53 147195 52 147295 51 147395 50 1474explain select * from t1 where badkey=1 and key1=10; 1475id select_type table type possible_keys key key_len ref rows Extra 14761 SIMPLE t1 ref key1 key1 4 const ROWS Using where 1477explain select * from t1 where pk1 < 7500 and key1 = 10; 1478id select_type table type possible_keys key key_len ref rows Extra 14791 SIMPLE t1 ref PRIMARY,key1 key1 4 const ROWS Using where 1480explain select * from t1 where pktail1ok=1 and key1=10; 1481id select_type table type possible_keys key key_len ref rows Extra 14821 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where 1483explain select * from t1 where pktail2ok=1 and key1=10; 1484id select_type table type possible_keys key key_len ref rows Extra 14851 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where 1486explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10; 1487id select_type table type possible_keys key key_len ref rows Extra 14881 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL ROWS Using sort_union(pktail2ok,key1); Using where 1489explain select * from t1 where pktail3bad=1 and key1=10; 1490id select_type table type possible_keys key key_len ref rows Extra 14911 SIMPLE t1 ref key1,pktail3bad EITHER_KEY 4 const ROWS Using where 1492explain select * from t1 where pktail4bad=1 and key1=10; 1493id select_type table type possible_keys key key_len ref rows Extra 14941 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const ROWS Using where 1495explain select * from t1 where pktail5bad=1 and key1=10; 1496id select_type table type possible_keys key key_len ref rows Extra 14971 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const ROWS Using where 1498explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; 1499id select_type table type possible_keys key key_len ref rows Extra 15001 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where 1501select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10; 1502pk1 pk2 key1 key2 1503104 49 10 10 1504104 48 10 10 1505104 47 10 10 1506104 46 10 10 1507104 45 10 10 1508104 44 10 10 1509104 43 10 10 1510104 42 10 10 1511104 41 10 10 1512104 40 10 10 1513drop table t1; 1514create table t1 1515( 1516RUNID varchar(22), 1517SUBMITNR varchar(5), 1518ORDERNR char(1), 1519PROGRAMM varchar(8), 1520TESTID varchar(4), 1521UCCHECK char(1), 1522ETEXT varchar(80), 1523ETEXT_TYPE char(1), 1524INFO char(1), 1525SEVERITY tinyint(3), 1526TADIRFLAG char(1), 1527PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK), 1528KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK) 1529) DEFAULT CHARSET=latin1; 1530update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`='' 1531WHERE 1532`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND 1533`TESTID`='' AND `UCCHECK`=''; 1534drop table t1; 1535# 1536# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB 1537# 1538CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1)); 1539INSERT INTO t1 VALUES (2); 1540CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1), 1541PRIMARY KEY (f1), KEY (f2), KEY (f3) ); 1542INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, ''); 1543SELECT t1.f1 FROM t1 1544WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; 1545f1 15462 1547EXPLAIN SELECT t1.f1 FROM t1 1548WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2; 1549id select_type table type possible_keys key key_len ref rows Extra 15501 PRIMARY t1 system PRIMARY NULL NULL NULL 1 NULL 15512 DEPENDENT SUBQUERY t2 ref f2,f3 f2 5 const 1 Using where 1552DROP TABLE t1,t2; 1553# 1554# Generic @@optimizer_switch tests (move those into a separate file if 1555# we get another @@optimizer_switch user) 1556# 1557select @@optimizer_switch; 1558@@optimizer_switch 1559index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1560set optimizer_switch='index_merge=off,index_merge_union=off'; 1561select @@optimizer_switch; 1562@@optimizer_switch 1563index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1564set optimizer_switch='index_merge_union=on'; 1565select @@optimizer_switch; 1566@@optimizer_switch 1567index_merge=off,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1568set optimizer_switch='default,index_merge_sort_union=off'; 1569select @@optimizer_switch; 1570@@optimizer_switch 1571index_merge=on,index_merge_union=on,index_merge_sort_union=off,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1572set optimizer_switch=4; 1573set optimizer_switch=NULL; 1574ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'NULL' 1575set optimizer_switch='default,index_merge'; 1576ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge' 1577set optimizer_switch='index_merge=index_merge'; 1578ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=index_merge' 1579set optimizer_switch='index_merge=on,but...'; 1580ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'but...' 1581set optimizer_switch='index_merge='; 1582ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=' 1583set optimizer_switch='index_merge'; 1584ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge' 1585set optimizer_switch='on'; 1586ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'on' 1587set optimizer_switch='index_merge=on,index_merge=off'; 1588ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off' 1589set optimizer_switch='index_merge_union=on,index_merge_union=default'; 1590ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge_union=default' 1591set optimizer_switch='default,index_merge=on,index_merge=off,default'; 1592ERROR 42000: Variable 'optimizer_switch' can't be set to the value of 'index_merge=off,default' 1593set optimizer_switch=default; 1594set optimizer_switch='index_merge=off,index_merge_union=off,default'; 1595select @@optimizer_switch; 1596@@optimizer_switch 1597index_merge=off,index_merge_union=off,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1598set optimizer_switch=default; 1599select @@global.optimizer_switch; 1600@@global.optimizer_switch 1601index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1602set @@global.optimizer_switch=default; 1603select @@global.optimizer_switch; 1604@@global.optimizer_switch 1605index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1606# 1607# Check index_merge's @@optimizer_switch flags 1608# 1609select @@optimizer_switch; 1610@@optimizer_switch 1611index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1612create table t0 (a int); 1613insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1614create table t1 (a int, b int, c int, filler char(100), 1615key(a), key(b), key(c)); 1616insert into t1 select 1617A.a * B.a*10 + C.a*100, 1618A.a * B.a*10 + C.a*100, 1619A.a, 1620'filler' 1621from t0 A, t0 B, t0 C; 1622This should use union: 1623explain select * from t1 where a=1 or b=1; 1624id select_type table type possible_keys key key_len ref rows Extra 16251 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where 1626This should use ALL: 1627set optimizer_switch='default,index_merge=off'; 1628explain select * from t1 where a=1 or b=1; 1629id select_type table type possible_keys key key_len ref rows Extra 16301 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where 1631This should use sort-union: 1632set optimizer_switch='default,index_merge_union=off'; 1633explain select * from t1 where a=1 or b=1; 1634id select_type table type possible_keys key key_len ref rows Extra 16351 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using sort_union(a,b); Using where 1636This will use sort-union: 1637set optimizer_switch=default; 1638explain select * from t1 where a<1 or b <1; 1639id select_type table type possible_keys key key_len ref rows Extra 16401 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where 1641This should use ALL: 1642set optimizer_switch='default,index_merge_sort_union=off'; 1643explain select * from t1 where a<1 or b <1; 1644id select_type table type possible_keys key key_len ref rows Extra 16451 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where 1646This should use ALL: 1647set optimizer_switch='default,index_merge=off'; 1648explain select * from t1 where a<1 or b <1; 1649id select_type table type possible_keys key key_len ref rows Extra 16501 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where 1651This will use sort-union: 1652set optimizer_switch='default,index_merge_union=off'; 1653explain select * from t1 where a<1 or b <1; 1654id select_type table type possible_keys key key_len ref rows Extra 16551 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where 1656alter table t1 add d int, add key(d); 1657update t1 set d=a; 1658This will use sort_union: 1659set optimizer_switch=default; 1660explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); 1661id select_type table type possible_keys key key_len ref rows Extra 16621 SIMPLE t1 index_merge a,b,c,d a,b 5,5 NULL 3 Using sort_union(a,b); Using where 1663And if we disable sort_union, union: 1664set optimizer_switch='default,index_merge_sort_union=off'; 1665explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4); 1666id select_type table type possible_keys key key_len ref rows Extra 16671 SIMPLE t1 index_merge a,b,c,d c,d 5,5 NULL 100 Using union(c,d); Using where 1668drop table t1; 1669create table t1 ( 1670a int, b int, c int, 1671filler1 char(200), filler2 char(200), 1672key(a),key(b),key(c) 1673); 1674insert into t1 1675select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar' 1676from t0 A, t0 B, t0 C, t0 D where D.a<5; 1677This should be intersect: 1678set optimizer_switch=default; 1679explain select * from t1 where a=10 and b=10; 1680id select_type table type possible_keys key key_len ref rows Extra 16811 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where 1682No intersect when index_merge is disabled: 1683set optimizer_switch='default,index_merge=off'; 1684explain select * from t1 where a=10 and b=10; 1685id select_type table type possible_keys key key_len ref rows Extra 16861 SIMPLE t1 ref a,b a 5 const 49 Using where 1687No intersect if it is disabled: 1688set optimizer_switch='default,index_merge_intersection=off'; 1689explain select * from t1 where a=10 and b=10; 1690id select_type table type possible_keys key key_len ref rows Extra 16911 SIMPLE t1 ref a,b a 5 const 49 Using where 1692Do intersect when union was disabled 1693set optimizer_switch='default,index_merge_union=off'; 1694explain select * from t1 where a=10 and b=10; 1695id select_type table type possible_keys key key_len ref rows Extra 16961 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where 1697Do intersect when sort_union was disabled 1698set optimizer_switch='default,index_merge_sort_union=off'; 1699explain select * from t1 where a=10 and b=10; 1700id select_type table type possible_keys key key_len ref rows Extra 17011 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where 1702This will use intersection inside a union: 1703set optimizer_switch=default; 1704explain select * from t1 where a=10 and b=10 or c=10; 1705id select_type table type possible_keys key key_len ref rows Extra 17061 SIMPLE t1 index_merge a,b,c a,b,c 5,5,5 NULL 6 Using union(intersect(a,b),c); Using where 1707Should be only union left: 1708set optimizer_switch='default,index_merge_intersection=off'; 1709explain select * from t1 where a=10 and b=10 or c=10; 1710id select_type table type possible_keys key key_len ref rows Extra 17111 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using union(a,c); Using where 1712This will switch to sort-union (intersection will be gone, too, 1713thats a known limitation: 1714set optimizer_switch='default,index_merge_union=off'; 1715explain select * from t1 where a=10 and b=10 or c=10; 1716id select_type table type possible_keys key key_len ref rows Extra 17171 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using sort_union(a,c); Using where 1718set optimizer_switch=default; 1719show variables like 'optimizer_switch'; 1720Variable_name Value 1721optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on,favor_range_scan=off 1722drop table t0, t1; 1723