Logging: /home/wlad/bzr/mysql-5.5/mysql-test/mysql-test-run.pl --suite=main index_merge_myisam MySQL Version 5.5.19 Checking supported features... - skipping ndbcluster - skipping SSL, mysqld not compiled with SSL Collecting tests... vardir: /home/wlad/bzr/mysql-5.5/xxx/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/wlad/bzr/mysql-5.5/xxx/mysql-test/var'... Installing system database... Using server port 43815 ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009 main.index_merge_myisam [ fail ] Test ended at 2011-11-18 20:06:36 CURRENT_TEST: main.index_merge_myisam --- /home/wlad/bzr/mysql-5.5/mysql-test/r/index_merge_myisam.result 2011-11-18 21:57:32.993376000 +0300 +++ /home/wlad/bzr/mysql-5.5/mysql-test/r/index_merge_myisam.reject 2011-11-18 22:06:36.460454700 +0300 @@ -19,11 +19,11 @@ test.t0 analyze status OK explain select * from t0 where key1 < 3 or key1 > 1020; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 78 Using where +1 SIMPLE t0 range i1 i1 4 NULL # Using where explain select * from t0 where key1 < 3 or key2 > 1020; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 45 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where select * from t0 where key1 < 3 or key2 > 1020; key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 @@ -34,11 +34,11 @@ 1024 1024 1024 1024 1024 1024 1024 0 explain select * from t0 where key1 < 3 or key2 <4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain -select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); +select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40) ; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 11 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); key1 key2 key3 key4 key5 key6 key7 key8 31 31 31 31 31 31 31 993 @@ -52,55 +52,55 @@ 39 39 39 39 39 39 39 985 explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1 NULL NULL NULL 1024 Using where +1 SIMPLE t0 ALL i1 NULL NULL NULL # Using where explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1,i2,i3 i3 4 const 1 Using where +1 SIMPLE t0 ref i1,i2,i3 i3 4 const # Using where explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where (key1 > 1 or key2 > 2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where +1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 1024 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where key1<3 or key2<3 or (key1>5 and key1<8) or (key1>10 and key1<12) or (key2>100 and key2<110); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 17 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where key2 = 45 or key1 <=> null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1,i2 i2 4 NULL 1 Using where +1 SIMPLE t0 range i1,i2 i2 4 NULL # Using where explain select * from t0 where key2 = 45 or key1 is not null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where +1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where explain select * from t0 where key2 = 45 or key1 is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i2 i2 4 const 1 +1 SIMPLE t0 ref i2 i2 4 const # explain select * from t0 where key2=10 or key3=3 or key4 <=> null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where +1 SIMPLE t0 index_merge i2,i3,i4 i2,i3 4,4 NULL # Using union(i2,i3); Using where explain select * from t0 where key2=10 or key3=3 or key4 is null; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL 2 Using union(i2,i3); Using where +1 SIMPLE t0 index_merge i2,i3 i2,i3 4,4 NULL # Using union(i2,i3); Using where explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or (key3=10) or (key4 <=> null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL 6 Using sort_union(i2,i3); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL # Using sort_union(i2,i3); Using where explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or (key3=10) or (key4 <=> null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL 6 Using sort_union(i1,i3); Using where +1 SIMPLE t0 index_merge i1,i3,i4 i1,i3 4,4 NULL # Using sort_union(i1,i3); Using where explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 9 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4); key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 @@ -111,27 +111,27 @@ explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL 6 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 100); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1,i2,i3 i3 4 NULL 95 Using where +1 SIMPLE t0 range i1,i2,i3 i3 4 NULL # Using where explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 1000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where +1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where explain select * from t0 where ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) or key2 > 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL 1024 Using where +1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where explain select * from t0 where ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) or key1 < 7; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL 10 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where select * from t0 where ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4)) or @@ -148,37 +148,37 @@ or ((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL 19 Using sort_union(i1,i2,i5,i6); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL # Using sort_union(i1,i2,i5,i6); Using where explain select * from t0 where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key7 <7 or key8 < 4) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 20 Using sort_union(i3,i5,i7,i8); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL # Using sort_union(i3,i5,i7,i8); Using where explain select * from t0 where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 <7 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where explain select * from t0 where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) or (((key3 <7 and key7 < 6) or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL 11 Using sort_union(i3,i5); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where explain select * from t0 where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL 1024 Using where +1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL # Using where explain select * from t0 force index(i1, i2, i3, i4, i5, i6 ) where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) or ((key3 >=5 or key5 < 2) and (key5 < 5 or key6 < 6)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL 1024 Using sort_union(i3,i5); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 0,4 NULL # Using sort_union(i3,i5); Using where select * from t0 where key1 < 5 or key8 < 4 order by key1; key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 @@ -192,7 +192,7 @@ explain select * from t0 where key1 < 5 or key8 < 4 order by key1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 9 Using sort_union(i1,i8); Using where; Using filesort +1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL # Using sort_union(i1,i8); Using where; Using filesort create table t2 like t0; insert into t2 select * from t0; alter table t2 add index i1_3(key1, key3); @@ -202,13 +202,13 @@ alter table t2 add index i321(key3, key2, key1); explain select key3 from t2 where key1 = 100 or key2 = 100; id select_type table type possible_keys key key_len ref rows Extra -1 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 +1 SIMPLE t2 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL # Using sort_union(i1_3,i2_3); Using where explain select key3 from t2 where key1 <100 or key2 < 100; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL 1024 Using where; Using index +1 SIMPLE t2 index i1_3,i2_3 i321 12 NULL # Using where; Using index explain select key7 from t2 where key1 <100 or key2 < 100; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL 1024 Using where +1 SIMPLE t2 ALL i1_3,i2_3 NULL NULL NULL # Using where create table t4 ( key1a int not null, key1b int not null, @@ -228,23 +228,23 @@ 4 4 0 4 4 4 explain select * from t4 where key1a = 3 or key1b = 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL 2 Using sort_union(i1a,i1b); Using where +1 SIMPLE t4 index_merge i1a,i1b i1a,i1b 4,4 NULL # Using sort_union(i1a,i1b); Using where explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where +1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const # Using where explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const 10 Using where +1 SIMPLE t4 ref i2_1,i2_2 i2_1 4 const # Using where explain select * from t4 where key2_1 = 1 or key2_2 = 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t4 ALL NULL NULL NULL NULL 1024 Using where +1 SIMPLE t4 ALL NULL NULL NULL NULL # Using where create table t1 like t0; insert into t1 select * from t0; explain select * from t0 left join t1 on (t0.key1=t1.key1) where t0.key1=3 or t0.key2=4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where -1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where +1 SIMPLE t1 ref i1 i1 4 test.t0.key1 # select * from t0 left join t1 on (t0.key1=t1.key1) where t0.key1=3 or t0.key2=4; key1 key2 key3 key4 key5 key6 key7 key8 key1 key2 key3 key4 key5 key6 key7 key8 @@ -253,40 +253,40 @@ explain select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where -1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where +1 SIMPLE t1 ref i1 i1 4 test.t0.key1 # explain select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2=4) and t1.key1<200; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2 NULL NULL NULL 1024 Using where -1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 +1 SIMPLE t0 # i1,i2 # # test.t0.key1 # # +1 SIMPLE t1 # i1 # # test.t0.key1 # # explain select * from t0,t1 where (t0.key1=t1.key1) and (t0.key1=3 or t0.key2<4) and t1.key1=2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1,i2 i1 4 const 1 Using where -1 SIMPLE t1 ref i1 i1 4 const 1 +1 SIMPLE t0 ref i1,i2 i1 4 test.t0.key1 # Using where +1 SIMPLE t1 ref i1 i1 4 test.t0.key1 # explain select * from t0,t1 where t0.key1 = 5 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ref i1 i1 4 const 1 -1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using union(i1,i8); Using where; Using join buffer +1 SIMPLE t0 ref i1 i1 4 test.t0.key1 # +1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 test.t0.key1 # Using union(i1,i8); Using where; Using join buffer explain select * from t0,t1 where t0.key1 < 3 and (t1.key1 = t0.key1 or t1.key8 = t0.key1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 range i1 i1 4 NULL 3 Using where -1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 Range checked for each record (index map: 0x81) +1 SIMPLE t0 range i1 i1 4 test.t0.key1 # Using where +1 SIMPLE t1 ALL i1,i8 NULL NULL test.t0.key1 # Range checked for each record (index map: 0x81) explain select * from t1 where key1=3 or key2=4 union select * from t1 where key1<4 or key3=5; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where -2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using sort_union(i1,i3); Using where -NULL UNION RESULT ALL NULL NULL NULL NULL NULL +1 PRIMARY t1 index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where +2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL # Using sort_union(i1,i3); Using where +NULL UNION RESULT ALL NULL NULL NULL NULL # explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY system NULL NULL NULL NULL 1 -2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where; Using index +1 PRIMARY system NULL NULL NULL NULL # +2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where; Using index create table t3 like t0; insert into t3 select * from t0; alter table t3 add key9 int not null, add index i9(key9); @@ -299,7 +299,7 @@ key5=5 or key6=6 or key7=7 or key8=8 or key9=9 or keyA=10 or keyB=11 or keyC=12; id select_type table type possible_keys key key_len ref rows Extra -1 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 +1 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 # Using union(i1,i2,i3,i4,i5,i6,i7,i8,i9,iA,iB,iC); Using where select * from t3 where key1=1 or key2=2 or key3=3 or key4=4 or key5=5 or key6=6 or key7=7 or key8=8 or @@ -319,7 +319,7 @@ 1016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016 explain select * from t0 where key1 < 3 or key2 < 4; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 7 Using sort_union(i1,i2); Using where +1 SIMPLE t0 # i1,i2 # # NULL # # select * from t0 where key1 < 3 or key2 < 4; key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 @@ -347,8 +347,8 @@ where (A.key1 < 500000 or A.key2 < 3) and (B.key1 < 500000 or B.key2 < 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where -1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1013 Using sort_union(i1,i2); Using where; Using join buffer +1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where +1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where; Using join buffer select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 < 500000 or A.key2 < 3) @@ -361,8 +361,8 @@ where (A.key1 = 1 or A.key2 = 1) and (B.key1 = 1 or B.key2 = 1); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where -1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1020 Using union(i1,i2); Using where; Using join buffer +1 SIMPLE A index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where +1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL # Using union(i1,i2); Using where; Using join buffer select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A force index(i1,i2), t0 as B force index (i1,i2) where (A.key1 = 1 or A.key2 = 1) @@ -376,8 +376,8 @@ where (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) and (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); id select_type table type possible_keys key key_len ref rows Extra -1 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 -1 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 +1 SIMPLE A # i1,i2,i3,i4,i5,i6,i7?,i8 # # NULL # # +1 SIMPLE B # i1,i2,i3,i4,i5,i6,i7?,i8 # # NULL # # select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) from t0 as A, t0 as B where (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) @@ -399,10 +399,10 @@ 8704 explain select * from t1 WHERE cola = 'foo' AND colb = 'bar'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where +1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where explain select * from t1 force index(cola,colb) WHERE cola = 'foo' AND colb = 'bar'; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL 32 Using intersect(cola,colb); Using where +1 SIMPLE t1 index_merge cola,colb cola,colb 3,3 NULL # Using intersect(cola,colb); Using where drop table t1; create table t0 (a int); insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); @@ -545,9 +545,9 @@ where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY t3 ALL a,b NULL NULL NULL 1002 Range checked for each record (index map: 0x3) +1 PRIMARY t1 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL # Using where +2 DEPENDENT SUBQUERY t3 ALL a,b NULL NULL NULL # Range checked for each record (index map: 0x3) select * from t1 where exists (select 1 from t2, t3 where t2.a=t1.a and (t3.a=t2.b or t3.b=t2.b or t3.b=t2.b+1)); @@ -570,11 +570,11 @@ SET SESSION sort_buffer_size=1; Warnings: Warning 1292 Truncated incorrect sort_buffer_size value: '1' -EXPLAIN +explain SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' ORDER BY a,b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge a,b a,b 131,131 NULL 64 Using sort_union(a,b); Using where; Using filesort +1 SIMPLE t1 index_merge a,b a,b 131,131 NULL # Using sort_union(a,b); Using where; Using filesort SELECT * FROM t1 FORCE INDEX(a,b) WHERE a LIKE 'a%' OR b LIKE 'b%' ORDER BY a,b; SET SESSION sort_buffer_size=DEFAULT; @@ -629,13 +629,13 @@ 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 77 Using intersect(key1,key2); Using where; Using index +1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # 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 +1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL # 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 @@ -643,21 +643,21 @@ 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 77 Using intersect(key1,key2); Using where +1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # 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 77 Using intersect(key1,key2); Using where; Using index +1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # 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 +1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL # 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 @@ -673,14 +673,14 @@ -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 +1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL # 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 +1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL # 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 @@ -697,19 +697,19 @@ 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 +1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL # 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 +1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL # 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 76 Using intersect(key1,key2); Using where; Using index +1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # 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'); @@ -717,7 +717,7 @@ 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 +1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL # 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 @@ -726,7 +726,7 @@ 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 +1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL # 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 @@ -736,7 +736,7 @@ 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 +1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL # 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 @@ -746,50 +746,50 @@ -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 3515 Using intersect(st_a,st_b); Using where +1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b # 4,4 NULL # 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 3515 Using intersect(st_a,st_b); Using where; Using index +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 # 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 +1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,stb_swt1a_2b,stb_swt1b,st_b # 4 const # 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 +1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const # 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 +1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const # 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 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where +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 # 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 58 Using intersect(sta_swt12a,stb_swt1b); Using where +1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,st_a,stb_swt1b,st_b sta_swt12a,stb_swt1b 12,8 NULL # 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 57 Using intersect(sta_swt1a,sta_swt2a,stb_swt1b); Using where +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 # 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 223 Using intersect(sta_swt1a,sta_swt2a,st_b); Using where +1 SIMPLE t1 index_merge sta_swt1a,sta_swt2a,st_a,st_b sta_swt1a,sta_swt2a,st_b 8,8,4 NULL # 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 58 Using intersect(sta_swt12a,stb_swt1a_2b); Using where +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 # 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 232 Using intersect(sta_swt1a,stb_swt1b); Using where +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 # 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 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index +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 # 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 232 Using intersect(sta_swt1a,stb_swt1b); Using where; Using index +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 # Using intersect(sta_swt1a,stb_swt1b); Using where; Using index drop table t0,t1; create table t2 ( a char(10), @@ -817,7 +817,7 @@ 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 +1 SIMPLE t2 ref a a 6 const # Using where drop table t2; CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '', KEY(c1), KEY(c2), KEY(c3)); @@ -1116,46 +1116,6 @@ COUNT(*) 1 drop table t1; -create table t1 -( -key1 int not null, -key2 int not null default 0, -key3 int not null default 0 -); -insert into t1(key1) values (1),(2),(3),(4),(5),(6),(7),(8); -set @d=8; -insert into t1 (key1) select key1+@d from t1; -set @d=@d*2; -insert into t1 (key1) select key1+@d from t1; -set @d=@d*2; -insert into t1 (key1) select key1+@d from t1; -set @d=@d*2; -insert into t1 (key1) select key1+@d from t1; -set @d=@d*2; -insert into t1 (key1) select key1+@d from t1; -set @d=@d*2; -insert into t1 (key1) select key1+@d from t1; -set @d=@d*2; -insert into t1 (key1) select key1+@d from t1; -set @d=@d*2; -alter table t1 add index i2(key2); -alter table t1 add index i3(key3); -update t1 set key2=key1,key3=key1; -explain select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index_merge i2,i3 i3,i2 4,4 NULL 11 Using sort_union(i3,i2); Using where -select * from t1 where (key3 > 30 and key3<35) or (key2 >32 and key2 < 40); -key1 key2 key3 -31 31 31 -32 32 32 -33 33 33 -34 34 34 -35 35 35 -36 36 36 -37 37 37 -38 38 38 -39 39 39 -drop table t1; # # Bug#56423: Different count with SELECT and CREATE SELECT queries # mysqltest: Result content mismatch - saving '/home/wlad/bzr/mysql-5.5/xxx/mysql-test/var/log/main.index_merge_myisam/' to '/home/wlad/bzr/mysql-5.5/xxx/mysql-test/var/log/main.index_merge_myisam/' -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.000 of 10 seconds executing testcases Completed: Failed 1/1 tests, 0.00% were successful. Failing test(s): main.index_merge_myisam The log files in var/log may give you some hint of what went wrong. If you want to report this error, please read first the documentation at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html mysql-test-run: *** ERROR: there were failing test cases