summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--BitKeeper/etc/logging_ok1
-rw-r--r--mysql-test/r/index_merge.result312
-rw-r--r--mysql-test/r/index_merge_innodb.result55
-rw-r--r--mysql-test/t/index_merge.test267
-rw-r--r--mysql-test/t/index_merge_innodb.test54
-rw-r--r--sql/opt_ft.h2
-rw-r--r--sql/opt_range.cc37
-rw-r--r--sql/opt_range.h7
-rw-r--r--sql/sql_select.cc2
-rw-r--r--sql/sql_union.cc3
10 files changed, 718 insertions, 22 deletions
diff --git a/BitKeeper/etc/logging_ok b/BitKeeper/etc/logging_ok
index 9efd243ee92..a359db6fb5e 100644
--- a/BitKeeper/etc/logging_ok
+++ b/BitKeeper/etc/logging_ok
@@ -119,6 +119,7 @@ serg@serg.mylan
serg@serg.mysql.com
serg@sergbook.mylan
serg@sergbook.mysql.com
+sergefp@mysql.com
sinisa@rhols221.adsl.netsonic.fi
tfr@beta.frontier86.ee
tfr@indrek.tfr.cafe.ee
diff --git a/mysql-test/r/index_merge.result b/mysql-test/r/index_merge.result
new file mode 100644
index 00000000000..5b0c2ffd7a2
--- /dev/null
+++ b/mysql-test/r/index_merge.result
@@ -0,0 +1,312 @@
+drop table if exists t0, t1, t2, t3;
+create table t0
+(
+key1 int not null,
+INDEX i1(key1),
+);
+alter table t0 add key2 int not null, add index i2(key2);
+alter table t0 add key3 int not null, add index i3(key3);
+alter table t0 add key4 int not null, add index i4(key4);
+alter table t0 add key5 int not null, add index i5(key5);
+alter table t0 add key6 int not null, add index i6(key6);
+alter table t0 add key7 int not null, add index i7(key7);
+alter table t0 add key8 int not null, add index i8(key8);
+update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
+analyze table t0;
+Table Op Msg_type Msg_text
+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 55 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 31 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
+2 2 2 2 2 2 2 1022
+1021 1021 1021 1021 1021 1021 1021 3
+1022 1022 1022 1022 1022 1022 1022 2
+1023 1023 1023 1023 1023 1023 1023 1
+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 where
+explain
+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 9 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
+32 32 32 32 32 32 32 992
+33 33 33 33 33 33 33 991
+34 34 34 34 34 34 34 990
+35 35 35 35 35 35 35 989
+36 36 36 36 36 36 36 988
+37 37 37 37 37 37 37 987
+38 38 38 38 38 38 38 986
+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
+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
+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 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
+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 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 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
+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
+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 ALL i1,i2 NULL NULL NULL 1024 Using where
+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 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 ALL i2,i3,i4 NULL NULL NULL 1024 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 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 5 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 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 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
+2 2 2 2 2 2 2 1022
+3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020
+5 5 5 5 5 5 5 1019
+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 i5,i6 4,4 NULL 4 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 96 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
+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
+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 where
+select * from t0 where
+((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+or
+key1 < 7;
+key1 key2 key3 key4 key5 key6 key7 key8
+1 1 1 1 1 1 1 1023
+2 2 2 2 2 2 2 1022
+3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020
+5 5 5 5 5 5 5 1019
+6 6 6 6 6 6 6 1018
+explain select * from t0 where
+((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
+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 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 21 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 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 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
+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 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
+2 2 2 2 2 2 2 1022
+3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020
+1021 1021 1021 1021 1021 1021 1021 3
+1022 1022 1022 1022 1022 1022 1022 2
+1023 1023 1023 1023 1023 1023 1023 1
+1024 1024 1024 1024 1024 1024 1024 0
+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 where; Using filesort
+create table t2 like t0;
+insert into t2 select * from t0;
+alter table t2 add index i1_3(key1, key3);
+alter table t2 add index i2_3(key2, key3);
+alter table t2 drop index i1;
+alter table t2 drop index i2;
+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 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
+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
+create table t4 (
+key1a int not null,
+key1b int not null,
+key2 int not null,
+key2_1 int not null,
+key2_2 int not null,
+key3 int not null,
+index i1a (key1a, key1b),
+index i1b (key1b, key1a),
+index i2_1(key2, key2_1),
+index i2_2(key2, key2_1),
+);
+insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
+select * from t4 where key1a = 3 or key1b = 4;
+key1a key1b key2 key2_1 key2_2 key3
+3 3 0 3 3 3
+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 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
+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
+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
+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 where
+1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
+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
+3 3 3 3 3 3 3 1021 3 3 3 3 3 3 3 1021
+4 4 4 4 4 4 4 1020 4 4 4 4 4 4 4 1020
+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 where
+1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1
+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 index_merge i1,i2 i1,i2 4,4 NULL 2 Using where
+1 SIMPLE t1 ref i1 i1 4 test.t0.key1 1 Using where
+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 Using where
+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 Using where
+1 SIMPLE t1 index_merge i1,i8 i1,i8 4,4 NULL 2 Using where
+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 where
+2 UNION t1 index_merge i1,i3 i1,i3 4,4 NULL 5 Using where
+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 <derived2> system NULL NULL NULL NULL 1
+2 DERIVED t1 index_merge i1,i2 i1,i2 4,4 NULL 2 Using where
+create table t3 like t0;
+insert into t3 select * from t0;
+alter table t3 add key9 int not null, add index i9(key9);
+alter table t3 add keyA int not null, add index iA(keyA);
+alter table t3 add keyB int not null, add index iB(keyB);
+alter table t3 add keyC int not null, add index iC(keyC);
+update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
+explain 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
+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 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
+key9=9 or keyA=10 or keyB=11 or keyC=12;
+key1 key2 key3 key4 key5 key6 key7 key8 key9 keyA keyB keyC
+1 1 1 1 1 1 1 1023 1 1 1 1
+2 2 2 2 2 2 2 1022 2 2 2 2
+3 3 3 3 3 3 3 1021 3 3 3 3
+4 4 4 4 4 4 4 1020 4 4 4 4
+5 5 5 5 5 5 5 1019 5 5 5 5
+6 6 6 6 6 6 6 1018 6 6 6 6
+7 7 7 7 7 7 7 1017 7 7 7 7
+1016 1016 1016 1016 1016 1016 1016 8 1016 1016 1016 1016
+9 9 9 9 9 9 9 1015 9 9 9 9
+10 10 10 10 10 10 10 1014 10 10 10 10
+11 11 11 11 11 11 11 1013 11 11 11 11
+12 12 12 12 12 12 12 1012 12 12 12 12
+drop table t0, t1, t2, t3, t4;
diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result
new file mode 100644
index 00000000000..ef6ffa82da7
--- /dev/null
+++ b/mysql-test/r/index_merge_innodb.result
@@ -0,0 +1,55 @@
+drop table if exists t1;
+create table t1
+(
+key1 int not null,
+key2 int not null,
+INDEX i1(key1),
+INDEX i2(key2),
+) type=innodb;
+explain select * from t1 where key1 < 5 or key2 > 197;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using where
+select * from t1 where key1 < 5 or key2 > 197;
+key1 key2
+0 200
+1 199
+2 198
+3 197
+4 196
+explain select * from t1 where key1 < 3 or key2 > 195;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using where
+select * from t1 where key1 < 3 or key2 > 195;
+key1 key2
+0 200
+1 199
+2 198
+4 196
+3 197
+alter table t1 add str1 char (255) not null,
+add zeroval int not null default 0,
+add str2 char (255) not null,
+add str3 char (255) not null;
+update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
+alter table t1 add primary key (str1, zeroval, str2, str3);
+explain select * from t1 where key1 < 5 or key2 > 197;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using where
+select * from t1 where key1 < 5 or key2 > 197;
+key1 key2 str1 zeroval str2 str3
+0 200 aaa 0 bbb 200-0_a
+1 199 aaa 0 bbb 199-0_A
+2 198 aaa 0 bbb 198-1_a
+3 197 aaa 0 bbb 197-1_A
+4 196 aaa 0 bbb 196-2_a
+explain select * from t1 where key1 < 3 or key2 > 195;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge i1,i2 i1,i2 4,4 NULL 8 Using where
+select * from t1 where key1 < 3 or key2 > 195;
+key1 key2 str1 zeroval str2 str3
+0 200 aaa 0 bbb 200-0_a
+1 199 aaa 0 bbb 199-0_A
+2 198 aaa 0 bbb 198-1_a
+4 196 aaa 0 bbb 196-2_a
+3 197 aaa 0 bbb 197-1_A
+drop table t1;
diff --git a/mysql-test/t/index_merge.test b/mysql-test/t/index_merge.test
new file mode 100644
index 00000000000..793ae848c52
--- /dev/null
+++ b/mysql-test/t/index_merge.test
@@ -0,0 +1,267 @@
+#
+# Index merge tests
+#
+
+--disable_warnings
+drop table if exists t0, t1, t2, t3;
+--enable_warnings
+
+# Create and fill a table with simple keys
+create table t0
+(
+ key1 int not null,
+ INDEX i1(key1),
+);
+
+--disable_query_log
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+
+let $1=7;
+set @d=8;
+while ($1)
+{
+ eval insert into t0 select key1+@d from t0;
+ eval set @d=@d*2;
+ dec $1;
+}
+--enable_query_log
+
+alter table t0 add key2 int not null, add index i2(key2);
+alter table t0 add key3 int not null, add index i3(key3);
+alter table t0 add key4 int not null, add index i4(key4);
+alter table t0 add key5 int not null, add index i5(key5);
+alter table t0 add key6 int not null, add index i6(key6);
+alter table t0 add key7 int not null, add index i7(key7);
+alter table t0 add key8 int not null, add index i8(key8);
+
+update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1;
+analyze table t0;
+
+# 1. One index
+explain select * from t0 where key1 < 3 or key1 > 1020;
+
+# 2. Simple cases
+explain
+select * from t0 where key1 < 3 or key2 > 1020;
+select * from t0 where key1 < 3 or key2 > 1020;
+
+explain select * from t0 where key1 < 3 or key2 <4;
+
+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);
+
+# 3. Check that index_merge doesn't break "ignore/force/use index"
+explain select * from t0 ignore index (i2) where key1 < 3 or key2 <4;
+explain select * from t0 where (key1 < 3 or key2 <4) and key3 = 50;
+explain select * from t0 use index (i1,i2) where (key1 < 3 or key2 <4) and key3 = 50;
+
+explain select * from t0 where (key1 > 1 or key2 > 2);
+explain select * from t0 force index (i1,i2) where (key1 > 1 or key2 > 2);
+
+
+# 4. Check if conjuncts are grouped by keyuse
+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);
+
+# 5. Check index_merge with conjuncts that are always true/false
+# verify fallback to "range" if there is only one non-confluent condition
+explain select * from t0 where key2 = 45 or key1 <=> null;
+
+explain select * from t0 where key2 = 45 or key1 is not null;
+explain select * from t0 where key2 = 45 or key1 is null;
+
+# the last conj. is always false and will be discarded
+explain select * from t0 where key2=10 or key3=3 or key4 <=> null;
+
+# the last conj. is always true and will cause 'all' scan
+explain select * from t0 where key2=10 or key3=3 or key4 is null;
+
+# some more complicated cases
+explain select key1 from t0 where (key1 <=> null) or (key2 < 5) or
+ (key3=10) or (key4 <=> null);
+explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or
+ (key3=10) or (key4 <=> null);
+
+# 6.Several ways to do index_merge, (ignored) index_merge vs. range
+explain select * from t0 where
+ (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 5 or key6 < 5);
+
+explain
+select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
+
+select * from t0 where (key1 < 3 or key2 < 6) and (key1 < 7 or key3 < 4);
+
+
+explain select * from t0 where
+ (key1 < 3 or key2 < 3) and (key3 < 4 or key4 < 4) and (key5 < 2 or key6 < 2);
+
+# now index_merge is not used at all when "range" is possible
+explain select * from t0 where
+ (key1 < 3 or key2 < 3) and (key3 < 100);
+
+# this even can cause "all" scan:
+explain select * from t0 where
+ (key1 < 3 or key2 < 3) and (key3 < 1000);
+
+
+# 7. Complex cases
+# tree_or(List<SEL_IMERGE>, range SEL_TREE).
+explain select * from t0 where
+ ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+ or
+ key2 > 5;
+
+explain select * from t0 where
+ ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+ or
+ key1 < 7;
+
+select * from t0 where
+ ((key1 < 4 or key2 < 4) and (key2 <5 or key3 < 4))
+ or
+ key1 < 7;
+
+# tree_or(List<SEL_IMERGE>, List<SEL_IMERGE>).
+explain select * from t0 where
+ ((key1 < 4 or key2 < 4) and (key3 <5 or key5 < 4))
+ or
+ ((key5 < 5 or key6 < 6) and (key7 <7 or key8 < 4));
+
+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));
+
+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));
+
+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));
+
+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));
+
+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));
+
+# 8. Verify that "order by" after index merge uses filesort
+select * from t0 where key1 < 5 or key8 < 4 order by key1;
+
+explain
+select * from t0 where key1 < 5 or key8 < 4 order by key1;
+
+# 9. Check that index_merge cost is compared to 'index' where possible
+create table t2 like t0;
+insert into t2 select * from t0;
+
+alter table t2 add index i1_3(key1, key3);
+alter table t2 add index i2_3(key2, key3);
+alter table t2 drop index i1;
+alter table t2 drop index i2;
+alter table t2 add index i321(key3, key2, key1);
+
+# index_merge vs 'index', index_merge is better.
+explain select key3 from t2 where key1 = 100 or key2 = 100;
+
+# index_merge vs 'index', 'index' is better.
+explain select key3 from t2 where key1 <100 or key2 < 100;
+
+# index_merge vs 'all', index_merge is better.
+explain select key7 from t2 where key1 <100 or key2 < 100;
+
+# 10. Multipart keys.
+create table t4 (
+ key1a int not null,
+ key1b int not null,
+ key2 int not null,
+ key2_1 int not null,
+ key2_2 int not null,
+ key3 int not null,
+
+ index i1a (key1a, key1b),
+ index i1b (key1b, key1a),
+
+ index i2_1(key2, key2_1),
+ index i2_2(key2, key2_1),
+);
+
+insert into t4 select key1,key1,key1 div 10, key1 % 10, key1 % 10, key1 from t0;
+
+# the following will be handled by index_merge:
+select * from t4 where key1a = 3 or key1b = 4;
+explain select * from t4 where key1a = 3 or key1b = 4;
+
+# and the following will not
+explain select * from t4 where key2 = 1 and (key2_1 = 1 or key3 = 5);
+
+explain select * from t4 where key2 = 1 and (key2_1 = 1 or key2_2 = 5);
+
+explain select * from t4 where key2_1 = 1 or key2_2 = 5;
+
+
+# 11. Multitable selects
+create table t1 like t0;
+insert into t1 select * from t0;
+
+# index_merge on first table in join
+explain select * from t0 left join t1 on (t0.key1=t1.key1)
+ where t0.key1=3 or t0.key2=4;
+
+select * from t0 left join t1 on (t0.key1=t1.key1)
+ where t0.key1=3 or t0.key2=4;
+
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and ( t0.key1=3 or t0.key2=4);
+
+# index_merge vs. ref
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and
+ (t0.key1=3 or t0.key2=4) and t1.key1<200;
+
+# index_merge vs. ref
+explain
+select * from t0,t1 where (t0.key1=t1.key1) and
+ (t0.key1=3 or t0.key2<4) and t1.key1=2;
+
+# index_merge on second table in join
+explain select * from t0,t1 where t0.key1 = 5 and
+ (t1.key1 = t0.key1 or t1.key8 = t0.key1);
+
+# index_merge inside union
+explain select * from t1 where key1=3 or key2=4
+ union select * from t1 where key1<4 or key3=5;
+
+# index merge in subselect
+explain select * from (select * from t1 where key1 = 3 or key2 =3) as Z where key8 >5;
+
+# 12. check for long index_merges.
+create table t3 like t0;
+insert into t3 select * from t0;
+alter table t3 add key9 int not null, add index i9(key9);
+alter table t3 add keyA int not null, add index iA(keyA);
+alter table t3 add keyB int not null, add index iB(keyB);
+alter table t3 add keyC int not null, add index iC(keyC);
+update t3 set key9=key1,keyA=key1,keyB=key1,keyC=key1;
+
+explain 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
+ key9=9 or keyA=10 or keyB=11 or keyC=12;
+
+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
+ key9=9 or keyA=10 or keyB=11 or keyC=12;
+
+
+drop table t0, t1, t2, t3, t4;
diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test
new file mode 100644
index 00000000000..d3943918468
--- /dev/null
+++ b/mysql-test/t/index_merge_innodb.test
@@ -0,0 +1,54 @@
+#
+# Index merge tests
+#
+-- source include/have_innodb.inc
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+create table t1
+(
+ key1 int not null,
+ key2 int not null,
+
+ INDEX i1(key1),
+ INDEX i2(key2),
+) type=innodb;
+
+--disable_query_log
+let $1=200;
+while ($1)
+{
+ eval insert into t1 values (200-$1, $1);
+ dec $1;
+}
+--enable_query_log
+
+# No primary key
+explain select * from t1 where key1 < 5 or key2 > 197;
+
+select * from t1 where key1 < 5 or key2 > 197;
+
+explain select * from t1 where key1 < 3 or key2 > 195;
+select * from t1 where key1 < 3 or key2 > 195;
+
+# Primary key as case-sensitive string with \0s.
+# also make primary key be longer then max. index length of MyISAM.
+alter table t1 add str1 char (255) not null,
+ add zeroval int not null default 0,
+ add str2 char (255) not null,
+ add str3 char (255) not null;
+
+update t1 set str1='aaa', str2='bbb', str3=concat(key2, '-', key1 div 2, '_' ,if(key1 mod 2 = 0, 'a', 'A'));
+
+alter table t1 add primary key (str1, zeroval, str2, str3);
+
+explain select * from t1 where key1 < 5 or key2 > 197;
+
+select * from t1 where key1 < 5 or key2 > 197;
+
+explain select * from t1 where key1 < 3 or key2 > 195;
+select * from t1 where key1 < 3 or key2 > 195;
+
+drop table t1;
diff --git a/sql/opt_ft.h b/sql/opt_ft.h
index 65a36ea495d..954c25b6caa 100644
--- a/sql/opt_ft.h
+++ b/sql/opt_ft.h
@@ -30,6 +30,8 @@ public:
FT_SELECT(THD *thd, TABLE *table, TABLE_REF *tref) :
QUICK_RANGE_SELECT (thd, table, tref->key, 1), ref(tref) { init(); }
+
+ int init() { return error=file->ft_init(); }
int get_next() { return error=file->ft_read(record); }
int get_type() { return QS_TYPE_FULLTEXT; }
};
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index a25de57107b..06c6b2a6b56 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -609,12 +609,19 @@ SQL_SELECT::~SQL_SELECT()
#undef index // Fix for Unixware 7
-QUICK_SELECT::QUICK_SELECT(THD *thd, TABLE *table, uint key_nr, bool no_alloc)
- :dont_free(0),error(0),index(key_nr),max_used_key_length(0),
- used_key_parts(0), head(table), it(ranges),range(0)
+QUICK_SELECT_I::QUICK_SELECT_I()
+ :max_used_key_length(0),
+ used_key_parts(0)
+{}
+
+QUICK_RANGE_SELECT::QUICK_RANGE_SELECT(THD *thd, TABLE *table, uint key_nr,
+ bool no_alloc, MEM_ROOT *parent_alloc)
+ :dont_free(0),error(0),it(ranges),range(0)
{
- //!!psergey: split this again to QUICK_SELECT_I
- if (!no_alloc)
+ index= key_nr;
+ head= table;
+
+ if (!no_alloc && !parent_alloc)
{
// Allocates everything through the internal memroot
init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0);
@@ -860,7 +867,6 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
uint idx;
double scan_time;
QUICK_INDEX_MERGE_SELECT *quick_imerge;
- THD *thd= current_thd;
DBUG_ENTER("test_quick_select");
DBUG_PRINT("enter",("keys_to_use: %lu prev_tables: %lu const_tables: %lu",
(ulong) keys_to_use, (ulong) prev_tables,
@@ -909,7 +915,6 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use,
thd->no_errors=1; // Don't warn about NULL
init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0);
- //!!todo:remerge this.
if (!(param.key_parts = (KEY_PART*) alloc_root(&alloc,
sizeof(KEY_PART)*
head->key_parts)))
@@ -1106,7 +1111,6 @@ imerge_fail:;
QUICK_RANGE_SELECT::QUICK_RANGE_SELECT leaves THR_MALLOC
pointing to its allocator, restore it back
*/
- //my_pthread_setspecific_ptr(THR_MALLOC,old_root);
quick_imerge->last_quick_select= new_quick;
if (quick_imerge->push_quick_back(new_quick))
@@ -1141,12 +1145,14 @@ imerge_fail:;
quick->reset();
}
- needed_reg|= (key_map) 1 << keynr; /!!todo entire function is not merged properly
+ goto end;
+ }
}
}
end_free:
free_root(&alloc,MYF(0)); // Return memory & allocator
my_pthread_setspecific_ptr(THR_MALLOC,old_root);
+end:
thd->no_errors=0;
}
@@ -1225,7 +1231,7 @@ static int get_quick_select_params(SEL_TREE *tree, PARAM& param,
param.range_count,
found_records)+
(double) found_records / TIME_FOR_COMPARE);
- if (*read_time > found_read_time)
+ if (*read_time > found_read_time && found_records != HA_POS_ERROR)
{
*read_time= found_read_time;
*records= found_records;
@@ -2822,9 +2828,9 @@ get_quick_select(PARAM *param,uint idx,SEL_ARG *key_tree,
{
QUICK_RANGE_SELECT *quick;
DBUG_ENTER("get_quick_select");
- if ((quick=new QUICK_RANGE_SELECT(param->table,param->real_keynr[idx],
- test(parent_alloc),
- parent_alloc))) //!!todo: add thd to param
+ if ((quick=new QUICK_RANGE_SELECT(param->thd, param->table,
+ param->real_keynr[idx],test(parent_alloc),
+ parent_alloc)))
{
if (quick->error ||
get_quick_keys(param,quick,param->key[idx],key_tree,param->min_key,0,
@@ -2996,7 +3002,8 @@ static bool null_part_in_key(KEY_PART *key_part, const char *key, uint length)
** Create a QUICK RANGE based on a key
****************************************************************************/
-QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, TABLE_REF *ref) //!!todo: make use of thd
+QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
+ TABLE_REF *ref)
{
table->file->index_end(); // Remove old cursor
QUICK_RANGE_SELECT *quick=new QUICK_RANGE_SELECT(thd, table, ref->key, 1);
@@ -3006,7 +3013,7 @@ QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table, TABLE_REF *
if (!quick)
return 0; /* no ranges found */
- if (quick->init()) /* !!todo: psergey: check if init is called exactly one time.*/
+ if (quick->init())
{
delete quick;
return 0;
diff --git a/sql/opt_range.h b/sql/opt_range.h
index e4fcbf657b7..48549f8ab91 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -119,7 +119,7 @@ public:
protected:
friend void print_quick_sel_range(QUICK_RANGE_SELECT *quick,
key_map needed_reg);
- friend QUICK_RANGE_SELECT *get_quick_select_for_ref(TABLE *table,
+ friend QUICK_RANGE_SELECT *get_quick_select_for_ref(THD *thd, TABLE *table,
struct st_table_ref *ref);
friend bool get_quick_keys(struct st_qsel_param *param,
QUICK_RANGE_SELECT *quick,KEY_PART *key,
@@ -134,11 +134,10 @@ protected:
List_iterator<QUICK_RANGE> it;
QUICK_RANGE *range;
MEM_ROOT alloc;
- KEY_PART *key_parts;
- QUICK_SELECT(THD *thd, TABLE *table,uint index_arg,bool no_alloc=0); //!!todo: add thd to my ctor below
+ KEY_PART *key_parts;
int cmp_next(QUICK_RANGE *range);
public:
- QUICK_RANGE_SELECT(TABLE *table,uint index_arg,bool no_alloc=0,
+ QUICK_RANGE_SELECT(THD *thd, TABLE *table,uint index_arg,bool no_alloc=0,
MEM_ROOT *parent_alloc=NULL);
~QUICK_RANGE_SELECT();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b26be9c44d9..da86cb4c638 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -33,7 +33,7 @@
const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
"MAYBE_REF","ALL","range","index","fulltext",
"ref_or_null","unique_subquery","index_subquery",
- "index_merge" //!!todo: psergey: check if constant values are same
+ "index_merge"
};
static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index ff68f26d765..c2bf9e0c66a 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -117,7 +117,6 @@ int st_select_lex_unit::prepare(THD *thd, select_result *sel_result,
{
SELECT_LEX *lex_select_save= thd->lex->current_select;
SELECT_LEX *select_cursor,*sl;
- SELECT_LEX *sl;
DBUG_ENTER("st_select_lex_unit::prepare");
if (prepared)
@@ -188,7 +187,7 @@ int st_select_lex_unit::prepare(THD *thd, select_result *sel_result,
union_result->not_describe=1;
union_result->tmp_table_param=tmp_table_param;
- for (;sl; sl= sl->next_select()) //!!todo: psergey: all my changes around this were to shut up the compiler. check they didn't make it here
+ for (;sl; sl= sl->next_select())
{
JOIN *join= new JOIN(thd, sl->item_list,
sl->options | thd->options | SELECT_NO_UNLOCK,
View Lorry Controller Status