mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
1798 lines
76 KiB
Text
1798 lines
76 KiB
Text
SET DEFAULT_STORAGE_ENGINE = MyISAM;
|
|
set @optimizer_switch_save= @@optimizer_switch;
|
|
set optimizer_switch='rowid_filter=off';
|
|
set optimizer_switch='index_merge_sort_intersection=off';
|
|
#---------------- Index merge test 1 -------------------------------------------
|
|
create table t0
|
|
(
|
|
key1 int not null,
|
|
INDEX i1(key1)
|
|
);
|
|
insert into t0(key1) select seq from seq_1_to_1024;
|
|
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 Engine-independent statistics collected
|
|
test.t0 analyze status OK
|
|
explain select * from t0 where key1 < 3 or key1 > 920 and key1 < 924;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t0 range i1 i1 4 NULL 5 Using index condition
|
|
explain
|
|
select * from t0 where key1 < 3 or key2 > 920 and key2 < 924;
|
|
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 5 Using sort_union(i1,i2); Using where
|
|
select * from t0 where key1 < 3 or key2 > 920 and key2 < 924;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 1023
|
|
2 2 2 2 2 2 2 1022
|
|
921 921 921 921 921 921 921 103
|
|
922 922 922 922 922 922 922 102
|
|
923 923 923 923 923 923 923 101
|
|
select * from t0 where key1=1022;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1022 1022 1022 1022 1022 1022 1022 2
|
|
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 5 Using sort_union(i1,i2); 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 11 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
|
|
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 5 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
|
|
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
|
|
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 15 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 index condition
|
|
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 ref i2 i2 4 const 1
|
|
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
|
|
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
|
|
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 5 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 5 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 4 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 7 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
|
|
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 2 Using sort_union(i5,i6); 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 index_merge i1,i2,i3 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); 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 index_merge i1,i2,i3 i1,i2 4,4 NULL 4 Using sort_union(i1,i2); 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 8 Using sort_union(i1,i2); 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
|
|
select count(*) 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));
|
|
count(*)
|
|
5
|
|
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 15 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 16 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 9 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 9 Using sort_union(i3,i5); Using where
|
|
explain select * from t0 where
|
|
((key3 < 4 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 < 4 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 4,4 NULL 1024 Using sort_union(i3,i5); 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 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 < 10 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
|
|
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 7 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);
|
|
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 sort_union(i1_3,i2_3); Using where
|
|
explain select key3 from t2 where key1 < 600 or key2 < 600;
|
|
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 index_merge i1_3,i2_3 i1_3,i2_3 4,4 NULL 186 Using sort_union(i1_3,i2_3); 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)
|
|
);
|
|
Warnings:
|
|
Note 1831 Duplicate index `i2_2`. This is deprecated and will be disallowed in a future release
|
|
insert into t4 select seq,seq,seq div 10, seq % 10, seq % 10, seq from seq_1_to_1024;
|
|
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 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 9 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 9 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 union(i1,i2); 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 union(i1,i2); 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 union(i1,i2); 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=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
|
|
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 (flat, BNL join)
|
|
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 2 Using index condition
|
|
1 SIMPLE t1 ALL i1,i8 NULL NULL NULL 1024 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 4 Using sort_union(i1,i3); Using where
|
|
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
|
|
set @tmp_optimizer_switch=@@optimizer_switch;
|
|
set optimizer_switch='derived_merge=off,derived_with_keys=off';
|
|
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> ALL NULL NULL NULL NULL 2 Using where
|
|
2 DERIVED t1 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
|
|
set optimizer_switch=@tmp_optimizer_switch;
|
|
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 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
|
|
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
|
|
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
|
|
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 5 Using sort_union(i1,i2); Using where
|
|
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
|
|
2 2 2 2 2 2 2 1022
|
|
3 3 3 3 3 3 3 1021
|
|
update t0 set key8=123 where key1 < 3 or key2 < 4;
|
|
select * from t0 where key1 < 3 or key2 < 4;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
1 1 1 1 1 1 1 123
|
|
2 2 2 2 2 2 2 123
|
|
3 3 3 3 3 3 3 123
|
|
delete from t0 where key1 < 3 or key2 < 4;
|
|
select * from t0 where key1 < 3 or key2 < 4;
|
|
key1 key2 key3 key4 key5 key6 key7 key8
|
|
select count(*) from t0;
|
|
count(*)
|
|
1021
|
|
drop table t4;
|
|
create table t4 (a int);
|
|
insert into t4 values (1),(4),(3);
|
|
set @save_join_buffer_size=@@join_buffer_size;
|
|
set join_buffer_size= 4096;
|
|
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) as exp
|
|
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
|
|
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 1010 Using sort_union(i1,i2); Using where
|
|
1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1010 Using sort_union(i1,i2); Using where; Using join buffer (flat, BNL join)
|
|
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) as exp
|
|
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
|
|
where (A.key1 < 500000 or A.key2 < 3)
|
|
and (B.key1 < 500000 or B.key2 < 3);
|
|
exp
|
|
10240
|
|
update t0 set key1=1;
|
|
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) as exp
|
|
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
|
|
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 1021 Using union(i1,i2); Using where
|
|
1 SIMPLE B index_merge i1,i2 i1,i2 4,4 NULL 1021 Using union(i1,i2); Using where; Using join buffer (flat, BNL join)
|
|
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) as exp
|
|
from t0 as A force index(i1,i2), t0 as B force index (i1,i2)
|
|
where (A.key1 = 1 or A.key2 = 1)
|
|
and (B.key1 = 1 or B.key2 = 1);
|
|
exp
|
|
8194
|
|
alter table t0 add filler1 char(200), add filler2 char(200), add filler3 char(200);
|
|
update t0 set key2=1, key3=1, key4=1, key5=1,key6=1,key7=1 where key7 < 500;
|
|
explain select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) as exp
|
|
from t0 as A straight_join 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)
|
|
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 (flat, BNL join)
|
|
select max(A.key1 + B.key1 + A.key2 + B.key2 + A.key3 + B.key3 + A.key4 + B.key4 + A.key5 + B.key5) as exp
|
|
from t0 as A straight_join 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)
|
|
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);
|
|
exp
|
|
8186
|
|
set join_buffer_size= @save_join_buffer_size;
|
|
drop table t0, t1, t2, t3, t4;
|
|
CREATE TABLE t1 (
|
|
cola char(3) not null, colb char(3) not null, filler char(200),
|
|
key(cola), key(colb)
|
|
);
|
|
INSERT INTO t1 VALUES ('foo','bar', 'ZZ'),('fuz','baz', 'ZZ');
|
|
OPTIMIZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 optimize status OK
|
|
select count(*) from t1;
|
|
count(*)
|
|
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
|
|
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
|
|
drop table t1;
|
|
create table t1 (
|
|
a int, b int,
|
|
filler1 char(200), filler2 char(200),
|
|
key(a),key(b)
|
|
);
|
|
insert into t1 select @v:= seq % 10, @v, 't1', 'filler2' from seq_1_to_1000;
|
|
create table t2 like t1;
|
|
create table t3 (
|
|
a int, b int,
|
|
filler1 char(200), filler2 char(200),
|
|
key(a),key(b)
|
|
) engine=merge union=(t1,t2);
|
|
explain select * from t1 where a=1 and b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where
|
|
explain select * from t3 where a=1 and b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t3 index_merge a,b a,b 5,5 NULL # Using intersect(a,b); Using where
|
|
drop table t1, t2, t3;
|
|
CREATE TABLE t1(a INT);
|
|
INSERT INTO t1 VALUES(1);
|
|
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
|
|
INSERT INTO t2(a,b) VALUES
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
|
|
(1,2);
|
|
LOCK TABLES t1 WRITE, t2 WRITE;
|
|
INSERT INTO t2(a,b) VALUES(1,2);
|
|
SELECT t2.a FROM t1,t2 WHERE t2.b=2 AND t2.a=1;
|
|
a
|
|
1
|
|
1
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`filler` char(200) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
KEY `a` (`a`),
|
|
KEY `b` (`b`)
|
|
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
|
|
insert into t1 values
|
|
(0, 'filler', 0), (1, 'filler', 1), (2, 'filler', 2), (3, 'filler', 3),
|
|
(4, 'filler', 4), (5, 'filler', 5), (6, 'filler', 6), (7, 'filler', 7),
|
|
(8, 'filler', 8), (9, 'filler', 9), (0, 'filler', 0), (1, 'filler', 1),
|
|
(2, 'filler', 2), (3, 'filler', 3), (4, 'filler', 4), (5, 'filler', 5),
|
|
(6, 'filler', 6), (7, 'filler', 7), (8, 'filler', 8), (9, 'filler', 9),
|
|
(10, 'filler', 10), (11, 'filler', 11), (12, 'filler', 12), (13, 'filler', 13),
|
|
(14, 'filler', 14), (15, 'filler', 15), (16, 'filler', 16), (17, 'filler', 17),
|
|
(18, 'filler', 18), (19, 'filler', 19), (4, '5 ', 0), (5, '4 ', 0),
|
|
(4, '4 ', 0), (4, 'qq ', 5), (5, 'qq ', 4), (4, 'zz ', 4);
|
|
create table t2(
|
|
`a` int(11) DEFAULT NULL,
|
|
`filler` char(200) DEFAULT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
KEY USING BTREE (`a`),
|
|
KEY USING BTREE (`b`)
|
|
) ENGINE=MEMORY DEFAULT CHARSET=latin1;
|
|
insert into t2 select * from t1;
|
|
must use sort-union rather than union:
|
|
explain select * from t1 where a=4 or b=4;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL # Using sort_union(a,b); Using where
|
|
select * from t1 where a=4 or b=4;
|
|
a filler b
|
|
4 4 0
|
|
4 5 0
|
|
4 filler 4
|
|
4 filler 4
|
|
4 qq 5
|
|
4 zz 4
|
|
5 qq 4
|
|
select * from t1 ignore index(a,b) where a=4 or b=4;
|
|
a filler b
|
|
4 4 0
|
|
4 5 0
|
|
4 filler 4
|
|
4 filler 4
|
|
4 qq 5
|
|
4 zz 4
|
|
5 qq 4
|
|
must use union, not sort-union:
|
|
explain select * from t2 where a=2 or b=2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 index_merge a,b a,b 5,5 NULL # Using union(a,b); Using where
|
|
select * from t2 where a=2 or b=2;
|
|
a filler b
|
|
2 filler 2
|
|
2 filler 2
|
|
drop table t1, t2;
|
|
CREATE TABLE t1 (a varchar(8), b set('a','b','c','d','e','f','g','h'),
|
|
KEY b(b), KEY a(a));
|
|
INSERT INTO t1 VALUES ('y',''), ('z','');
|
|
SELECT b,a from t1 WHERE (b!='c' AND b!='f' && b!='h') OR
|
|
(a='pure-S') OR (a='DE80337a') OR (a='DE80799');
|
|
b a
|
|
y
|
|
z
|
|
DROP TABLE t1;
|
|
#
|
|
# BUG#40974: Incorrect query results when using clause evaluated using range check
|
|
#
|
|
create table t1 (a int);
|
|
insert into t1 values (1),(2);
|
|
create table t2(a int, b int);
|
|
insert into t2 values (1,1), (2, 1000);
|
|
create table t3 (a int, b int, filler char(100), key(a), key(b));
|
|
insert into t3 select 1000, 1000,'filler' from seq_1_to_1000;
|
|
insert into t3 values (1,1,'data');
|
|
insert into t3 values (1,1,'data');
|
|
The plan should be ALL/ALL/ALL(Range checked for each record (index map: 0x3)
|
|
explain 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));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 ALL NULL NULL NULL NULL 2
|
|
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
|
|
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 2
|
|
2 MATERIALIZED t3 ALL a,b NULL NULL NULL 1002 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));
|
|
a
|
|
1
|
|
2
|
|
drop table t1, t2, t3;
|
|
#
|
|
# BUG#44810: index merge and order by with low sort_buffer_size
|
|
# crashes server!
|
|
#
|
|
CREATE TABLE t1(a VARCHAR(128),b VARCHAR(128),KEY(A),KEY(B));
|
|
INSERT INTO t1 SELECT REPEAT('a',128),REPEAT('b',128) FROM seq_1_to_64;
|
|
SET SESSION sort_buffer_size=1024*8;
|
|
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
|
|
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;
|
|
DROP TABLE t1;
|
|
End of 5.0 tests
|
|
#---------------- ROR-index_merge tests -----------------------
|
|
create table t1
|
|
(
|
|
/* Field names reflect value(rowid) distribution, st=STairs, swt= SaWTooth */
|
|
st_a int not null default 0,
|
|
swt1a int not null default 0,
|
|
swt2a int not null default 0,
|
|
st_b int not null default 0,
|
|
swt1b int not null default 0,
|
|
swt2b int not null default 0,
|
|
/* fields/keys for row retrieval tests */
|
|
key1 int,
|
|
key2 int,
|
|
key3 int,
|
|
key4 int,
|
|
/* make rows much bigger then keys */
|
|
filler1 char (200),
|
|
filler2 char (200),
|
|
filler3 char (200),
|
|
filler4 char (200),
|
|
filler5 char (200),
|
|
filler6 char (200),
|
|
/* order of keys is important */
|
|
key sta_swt12a(st_a,swt1a,swt2a),
|
|
key sta_swt1a(st_a,swt1a),
|
|
key sta_swt2a(st_a,swt2a),
|
|
key sta_swt21a(st_a,swt2a,swt1a),
|
|
key st_a(st_a),
|
|
key stb_swt1a_2b(st_b,swt1b,swt2a),
|
|
key stb_swt1b(st_b,swt1b),
|
|
key st_b(st_b),
|
|
key(key1),
|
|
key(key2),
|
|
key(key3),
|
|
key(key4)
|
|
) ;
|
|
create table t0 as select * from t1;
|
|
# Printing of many insert into t0 values (....) disabled.
|
|
alter table t1 disable keys;
|
|
# Printing of many insert into t1 select .... from t0 disabled.
|
|
# Printing of many insert into t1 (...) values (....) disabled.
|
|
alter table t1 enable keys;
|
|
select count(*) from t1;
|
|
count(*)
|
|
64801
|
|
explain select key1,key2 from t1 where key1=100 and key2=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 Using intersect(key1,key2); Using where; Using index
|
|
select key1,key2 from t1 where key1=100 and key2=100;
|
|
key1 key2
|
|
100 100
|
|
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 100 100 key1-key2-key3-key4
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, -1, -1, 'key1-key2');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 100, 100, 'key4-key3');
|
|
explain select key1,key2,filler1 from t1 where key1=100 and key2=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL 77 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
|
|
select key1,key2 from t1 where key1=100 and key2=100;
|
|
key1 key2
|
|
100 100
|
|
100 100
|
|
explain select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
|
|
select key1,key2,key3,key4 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4
|
|
100 100 100 100
|
|
100 100 -1 -1
|
|
-1 -1 100 100
|
|
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 100 100 key1-key2-key3-key4
|
|
100 100 -1 -1 key1-key2
|
|
-1 -1 100 100 key4-key3
|
|
explain select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 2 Using intersect(key1,key2,key3); Using where; Using index
|
|
select key1,key2,key3 from t1 where key1=100 and key2=100 and key3=100;
|
|
key1 key2 key3
|
|
100 100 100
|
|
insert into t1 (key1,key2,key3,key4,filler1) values (101,101,101,101, 'key1234-101');
|
|
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3 key1,key2,key3 5,5,5 NULL 83 Using union(intersect(key1,key2),key3); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=101;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 100 100 key1-key2-key3-key4
|
|
100 100 -1 -1 key1-key2
|
|
101 101 101 101 key1234-101
|
|
select key1,key2, filler1 from t1 where key1=100 and key2=100;
|
|
key1 key2 filler1
|
|
100 100 key1-key2-key3-key4
|
|
100 100 key1-key2
|
|
update t1 set filler1='to be deleted' where key1=100 and key2=100;
|
|
update t1 set key1=200,key2=200 where key1=100 and key2=100;
|
|
delete from t1 where key1=200 and key2=200;
|
|
select key1,key2,filler1 from t1 where key2=100 and key2=200;
|
|
key1 key2 filler1
|
|
explain select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key1,key2,key3,key4 5,5,5,5 NULL 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
-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 154 Using union(intersect(key1,key2),intersect(key3,key4)); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key1=100 and key2=100 or key3=100 and key4=100;
|
|
key1 key2 key3 key4 filler1
|
|
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
|
|
select key1,key2 from t1 where key1=100 and key2=100;
|
|
key1 key2
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-1');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-2');
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (100, 100, 200, 200,'key1-key2-key3-key4-3');
|
|
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 137 Using union(key3,intersect(key1,key2),key4); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 200 200 key1-key2-key3-key4-3
|
|
100 100 200 200 key1-key2-key3-key4-2
|
|
100 100 200 200 key1-key2-key3-key4-1
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, -1, 200,'key4');
|
|
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 148 Using union(key3,intersect(key1,key2),key4); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 200 200 key1-key2-key3-key4-3
|
|
100 100 200 200 key1-key2-key3-key4-2
|
|
100 100 200 200 key1-key2-key3-key4-1
|
|
-1 -1 -1 200 key4
|
|
insert into t1 (key1, key2, key3, key4, filler1) values (-1, -1, 200, -1,'key3');
|
|
explain select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2,key3,key4 key3,key1,key2,key4 5,5,5,5 NULL 159 Using union(key3,intersect(key1,key2),key4); Using where
|
|
select key1,key2,key3,key4,filler1 from t1 where key3=200 or (key1=100 and key2=100) or key4=200;
|
|
key1 key2 key3 key4 filler1
|
|
100 100 200 200 key1-key2-key3-key4-3
|
|
100 100 200 200 key1-key2-key3-key4-2
|
|
100 100 200 200 key1-key2-key3-key4-1
|
|
-1 -1 -1 200 key4
|
|
-1 -1 200 -1 key3
|
|
explain select * from t1 where st_a=1 and st_b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b st_a,st_b 4,4 NULL 3515 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
|
|
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 15094 Using where
|
|
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a sta_swt21a 12 const,const,const 971
|
|
explain select * from t1 where st_b=1 and swt1b=1 and swt2b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref stb_swt1a_2b,stb_swt1b,st_b stb_swt1a_2b 8 const,const 3879 Using where
|
|
explain select * from t1 where st_a=1 and swt1a=1 and swt2a=1 and st_b=1 and swt1b=1 and swt2b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge sta_swt12a,sta_swt1a,sta_swt2a,sta_swt21a,st_a,stb_swt1a_2b,stb_swt1b,st_b sta_swt12a,stb_swt1a_2b 12,12 NULL 58 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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
drop table t0,t1;
|
|
create table t2 (
|
|
a char(10),
|
|
b char(10),
|
|
filler1 char(255),
|
|
filler2 char(255),
|
|
key(a(5)),
|
|
key(b(5))
|
|
);
|
|
select count(a) from t2 where a='BBBBBBBB';
|
|
count(a)
|
|
4
|
|
select count(a) from t2 where b='BBBBBBBB';
|
|
count(a)
|
|
4
|
|
expla_or_bin select count(a_or_b) from t2 where a_or_b='AAAAAAAA' a_or_bnd a_or_b='AAAAAAAA';
|
|
id select_type ta_or_ba_or_ble type possia_or_ble_keys key key_len ref rows Extra_or_b
|
|
1 SIMPLE t2 ref a_or_b,a_or_b a_or_b 6 const 4 Using where
|
|
select count(a) from t2 where a='AAAAAAAA' and b='AAAAAAAA';
|
|
count(a)
|
|
4
|
|
select count(a) from t2 ignore index(a,b) where a='AAAAAAAA' and b='AAAAAAAA';
|
|
count(a)
|
|
4
|
|
insert into t2 values ('ab', 'ab', 'uh', 'oh');
|
|
explain select a from t2 where a='ab';
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t2 ref a a 6 const 1 Using where
|
|
drop table t2;
|
|
CREATE TABLE t1(c1 INT, c2 INT DEFAULT 0, c3 CHAR(255) DEFAULT '',
|
|
KEY(c1), KEY(c2), KEY(c3));
|
|
INSERT INTO t1(c1) VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),
|
|
(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);
|
|
INSERT INTO t1 VALUES(0,0,0);
|
|
CREATE TABLE t2(c1 int);
|
|
INSERT INTO t2 VALUES(1);
|
|
DELETE t1 FROM t1,t2 WHERE t1.c1=0 AND t1.c2=0;
|
|
SELECT * FROM t1;
|
|
c1 c2 c3
|
|
DROP TABLE t1,t2;
|
|
#---------------- Index merge test 2 -------------------------------------------
|
|
create table t1
|
|
(
|
|
key1 int not null,
|
|
key2 int not null,
|
|
INDEX i1(key1),
|
|
INDEX i2(key2)
|
|
);
|
|
INSERT INTO t1 SELECT seq,200-seq FROM seq_0_to_200;
|
|
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 sort_union(i1,i2); 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 sort_union(i1,i2); Using where
|
|
select * from t1 where key1 < 3 or key2 > 195;
|
|
key1 key2
|
|
0 200
|
|
1 199
|
|
2 198
|
|
3 197
|
|
4 196
|
|
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 sort_union(i1,i2); 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 sort_union(i1,i2); 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
|
|
3 197 aaa 0 bbb 197-1_A
|
|
4 196 aaa 0 bbb 196-2_a
|
|
drop table t1;
|
|
create table t1 (
|
|
pk integer not null auto_increment primary key,
|
|
key1 integer,
|
|
key2 integer not null,
|
|
filler char (200),
|
|
index (key1),
|
|
index (key2)
|
|
);
|
|
show warnings;
|
|
Level Code Message
|
|
INSERT INTO t1 (key1, key2, filler)
|
|
SELECT seq/4, seq/8, 'filler-data' FROM seq_30_to_0;
|
|
explain select pk from t1 where key1 = 1 and key2 = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1,key2 key1 5 const 4 Using where
|
|
select pk from t1 where key2 = 1 and key1 = 1;
|
|
pk
|
|
26
|
|
27
|
|
select pk from t1 ignore index(key1,key2) where key2 = 1 and key1 = 1;
|
|
pk
|
|
26
|
|
27
|
|
drop table t1;
|
|
create table t1 (
|
|
pk int primary key auto_increment,
|
|
key1a int,
|
|
key2a int,
|
|
key1b int,
|
|
key2b int,
|
|
dummy1 int,
|
|
dummy2 int,
|
|
dummy3 int,
|
|
dummy4 int,
|
|
key3a int,
|
|
key3b int,
|
|
filler1 char (200),
|
|
index i1(key1a, key1b),
|
|
index i2(key2a, key2b),
|
|
index i3(key3a, key3b)
|
|
);
|
|
create table t2 (a int);
|
|
insert into t2 values (0),(1),(2),(3),(4),(NULL);
|
|
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
|
|
select A.a, B.a, C.a, D.a, C.a, D.a from t2 A,t2 B,t2 C, t2 D;
|
|
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
|
|
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
|
|
insert into t1 (key1a, key1b, key2a, key2b, key3a, key3b)
|
|
select key1a, key1b, key2a, key2b, key3a, key3b from t1;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
select count(*) from t1;
|
|
count(*)
|
|
5184
|
|
explain select count(*) from t1 where
|
|
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge i1,i2 i1,i2 10,10 NULL REF Using intersect(i1,i2); Using where; Using index
|
|
select count(*) from t1 where
|
|
key1a = 2 and key1b is null and key2a = 2 and key2b is null;
|
|
count(*)
|
|
4
|
|
explain select count(*) from t1 where
|
|
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge i1,i3 i1,i3 10,10 NULL REF Using intersect(i1,i3); Using where; Using index
|
|
select count(*) from t1 where
|
|
key1a = 2 and key1b is null and key3a = 2 and key3b is null;
|
|
count(*)
|
|
4
|
|
drop table t1,t2;
|
|
create table t1 (
|
|
id1 int,
|
|
id2 date ,
|
|
index idx2 (id1,id2),
|
|
index idx1 (id2)
|
|
);
|
|
insert into t1 values(1,'20040101'), (2,'20040102');
|
|
select * from t1 where id1 = 1 and id2= '20040101';
|
|
id1 id2
|
|
1 2004-01-01
|
|
drop table t1;
|
|
drop view if exists v1;
|
|
CREATE TABLE t1 (
|
|
`oid` int(11) unsigned NOT NULL auto_increment,
|
|
`fk_bbk_niederlassung` int(11) unsigned NOT NULL,
|
|
`fk_wochentag` int(11) unsigned NOT NULL,
|
|
`uhrzeit_von` time NOT NULL COMMENT 'HH:MM',
|
|
`uhrzeit_bis` time NOT NULL COMMENT 'HH:MM',
|
|
`geloescht` tinyint(4) NOT NULL,
|
|
`version` int(5) NOT NULL,
|
|
PRIMARY KEY (`oid`),
|
|
KEY `fk_bbk_niederlassung` (`fk_bbk_niederlassung`),
|
|
KEY `fk_wochentag` (`fk_wochentag`),
|
|
KEY `ix_version` (`version`)
|
|
) DEFAULT CHARSET=latin1;
|
|
insert into t1 values
|
|
(1, 38, 1, '08:00:00', '13:00:00', 0, 1),
|
|
(2, 38, 2, '08:00:00', '13:00:00', 0, 1),
|
|
(3, 38, 3, '08:00:00', '13:00:00', 0, 1),
|
|
(4, 38, 4, '08:00:00', '13:00:00', 0, 1),
|
|
(5, 38, 5, '08:00:00', '13:00:00', 0, 1),
|
|
(6, 38, 5, '08:00:00', '13:00:00', 1, 2),
|
|
(7, 38, 3, '08:00:00', '13:00:00', 1, 2),
|
|
(8, 38, 1, '08:00:00', '13:00:00', 1, 2),
|
|
(9, 38, 2, '08:00:00', '13:00:00', 1, 2),
|
|
(10, 38, 4, '08:00:00', '13:00:00', 1, 2),
|
|
(11, 38, 1, '08:00:00', '13:00:00', 0, 3),
|
|
(12, 38, 2, '08:00:00', '13:00:00', 0, 3),
|
|
(13, 38, 3, '08:00:00', '13:00:00', 0, 3),
|
|
(14, 38, 4, '08:00:00', '13:00:00', 0, 3),
|
|
(15, 38, 5, '08:00:00', '13:00:00', 0, 3),
|
|
(16, 38, 4, '08:00:00', '13:00:00', 0, 4),
|
|
(17, 38, 5, '08:00:00', '13:00:00', 0, 4),
|
|
(18, 38, 1, '08:00:00', '13:00:00', 0, 4),
|
|
(19, 38, 2, '08:00:00', '13:00:00', 0, 4),
|
|
(20, 38, 3, '08:00:00', '13:00:00', 0, 4),
|
|
(21, 7, 1, '08:00:00', '13:00:00', 0, 1),
|
|
(22, 7, 2, '08:00:00', '13:00:00', 0, 1),
|
|
(23, 7, 3, '08:00:00', '13:00:00', 0, 1),
|
|
(24, 7, 4, '08:00:00', '13:00:00', 0, 1),
|
|
(25, 7, 5, '08:00:00', '13:00:00', 0, 1);
|
|
create view v1 as
|
|
select
|
|
zeit1.oid AS oid,
|
|
zeit1.fk_bbk_niederlassung AS fk_bbk_niederlassung,
|
|
zeit1.fk_wochentag AS fk_wochentag,
|
|
zeit1.uhrzeit_von AS uhrzeit_von,
|
|
zeit1.uhrzeit_bis AS uhrzeit_bis,
|
|
zeit1.geloescht AS geloescht,
|
|
zeit1.version AS version
|
|
from
|
|
t1 zeit1
|
|
where
|
|
(zeit1.version =
|
|
(select max(zeit2.version) AS `max(version)`
|
|
from t1 zeit2
|
|
where
|
|
((zeit1.fk_bbk_niederlassung = zeit2.fk_bbk_niederlassung) and
|
|
(zeit1.fk_wochentag = zeit2.fk_wochentag) and
|
|
(zeit1.uhrzeit_von = zeit2.uhrzeit_von) and
|
|
(zeit1.uhrzeit_bis = zeit2.uhrzeit_bis)
|
|
)
|
|
)
|
|
)
|
|
and (zeit1.geloescht = 0);
|
|
select * from v1 where oid = 21;
|
|
oid fk_bbk_niederlassung fk_wochentag uhrzeit_von uhrzeit_bis geloescht version
|
|
21 7 1 08:00:00 13:00:00 0 1
|
|
drop view v1;
|
|
drop table t1;
|
|
CREATE TABLE t1(
|
|
t_cpac varchar(2) NOT NULL,
|
|
t_vers varchar(4) NOT NULL,
|
|
t_rele varchar(2) NOT NULL,
|
|
t_cust varchar(4) NOT NULL,
|
|
filler1 char(250) default NULL,
|
|
filler2 char(250) default NULL,
|
|
PRIMARY KEY (t_cpac,t_vers,t_rele,t_cust),
|
|
UNIQUE KEY IX_4 (t_cust,t_cpac,t_vers,t_rele),
|
|
KEY IX_5 (t_vers,t_rele,t_cust)
|
|
);
|
|
insert into t1 values
|
|
('tm','2.5 ','a ',' ','',''), ('tm','2.5U','a ','stnd','',''),
|
|
('da','3.3 ','b ',' ','',''), ('da','3.3U','b ','stnd','',''),
|
|
('tl','7.6 ','a ',' ','',''), ('tt','7.6 ','a ',' ','',''),
|
|
('bc','B61 ','a ',' ','',''), ('bp','B61 ','a ',' ','',''),
|
|
('ca','B61 ','a ',' ','',''), ('ci','B61 ','a ',' ','',''),
|
|
('cp','B61 ','a ',' ','',''), ('dm','B61 ','a ',' ','',''),
|
|
('ec','B61 ','a ',' ','',''), ('ed','B61 ','a ',' ','',''),
|
|
('fm','B61 ','a ',' ','',''), ('nt','B61 ','a ',' ','',''),
|
|
('qm','B61 ','a ',' ','',''), ('tc','B61 ','a ',' ','',''),
|
|
('td','B61 ','a ',' ','',''), ('tf','B61 ','a ',' ','',''),
|
|
('tg','B61 ','a ',' ','',''), ('ti','B61 ','a ',' ','',''),
|
|
('tp','B61 ','a ',' ','',''), ('ts','B61 ','a ',' ','',''),
|
|
('wh','B61 ','a ',' ','',''), ('bc','B61U','a ','stnd','',''),
|
|
('bp','B61U','a ','stnd','',''), ('ca','B61U','a ','stnd','',''),
|
|
('ci','B61U','a ','stnd','',''), ('cp','B61U','a ','stnd','',''),
|
|
('dm','B61U','a ','stnd','',''), ('ec','B61U','a ','stnd','',''),
|
|
('fm','B61U','a ','stnd','',''), ('nt','B61U','a ','stnd','',''),
|
|
('qm','B61U','a ','stnd','',''), ('tc','B61U','a ','stnd','',''),
|
|
('td','B61U','a ','stnd','',''), ('tf','B61U','a ','stnd','',''),
|
|
('tg','B61U','a ','stnd','',''), ('ti','B61U','a ','stnd','',''),
|
|
('tp','B61U','a ','stnd','',''), ('ts','B61U','a ','stnd','',''),
|
|
('wh','B61U','a ','stnd','','');
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`t_cpac` varchar(2) NOT NULL,
|
|
`t_vers` varchar(4) NOT NULL,
|
|
`t_rele` varchar(2) NOT NULL,
|
|
`t_cust` varchar(4) NOT NULL,
|
|
`filler1` char(250) DEFAULT NULL,
|
|
`filler2` char(250) DEFAULT NULL,
|
|
PRIMARY KEY (`t_cpac`,`t_vers`,`t_rele`,`t_cust`),
|
|
UNIQUE KEY `IX_4` (`t_cust`,`t_cpac`,`t_vers`,`t_rele`),
|
|
KEY `IX_5` (`t_vers`,`t_rele`,`t_cust`)
|
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6';
|
|
t_vers t_rele t_cust filler1
|
|
7.6 a
|
|
7.6 a
|
|
select t_vers,t_rele,t_cust,filler1 from t1 where t_vers = '7.6'
|
|
and t_rele='a' and t_cust = ' ';
|
|
t_vers t_rele t_cust filler1
|
|
7.6 a
|
|
7.6 a
|
|
drop table t1;
|
|
create table t1 (
|
|
pk int(11) not null auto_increment,
|
|
a int(11) not null default '0',
|
|
b int(11) not null default '0',
|
|
c int(11) not null default '0',
|
|
filler1 datetime, filler2 varchar(15),
|
|
filler3 longtext,
|
|
kp1 varchar(4), kp2 varchar(7),
|
|
kp3 varchar(2), kp4 varchar(4),
|
|
kp5 varchar(7),
|
|
filler4 char(1),
|
|
primary key (pk),
|
|
key idx1(a,b,c),
|
|
key idx2(c),
|
|
key idx3(kp1,kp2,kp3,kp4,kp5)
|
|
) default charset=latin1;
|
|
set @fill=NULL;
|
|
SELECT COUNT(*) FROM t1 WHERE b = 0 AND a = 0 AND c = 13286427 AND
|
|
kp1='279' AND kp2='ELM0678' AND kp3='6' AND kp4='10' AND kp5 = 'R ';
|
|
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) select seq from seq_1_to_1024;
|
|
alter table t1 add index i2(key2);
|
|
alter table t1 add index i3(key3);
|
|
update t1 set key2=key1,key3=key1;
|
|
insert into t1 select 10000+key1, 10000+key2,10000+key3 from t1;
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
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 REF 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
|
|
#
|
|
CREATE TABLE t1 (
|
|
a INT,
|
|
b INT,
|
|
c INT,
|
|
d INT,
|
|
PRIMARY KEY (a),
|
|
KEY (c),
|
|
KEY bd (b,d)
|
|
);
|
|
INSERT INTO t1 VALUES
|
|
(1, 0, 1, 0),
|
|
(2, 1, 1, 1),
|
|
(3, 1, 1, 1),
|
|
(4, 0, 1, 1);
|
|
EXPLAIN
|
|
SELECT a
|
|
FROM t1
|
|
WHERE c = 1 AND b = 1 AND d = 1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref c,bd bd 10 const,const 2 Using where
|
|
CREATE TABLE t2 ( a INT )
|
|
SELECT a
|
|
FROM t1
|
|
WHERE c = 1 AND b = 1 AND d = 1;
|
|
SELECT * FROM t2;
|
|
a
|
|
2
|
|
3
|
|
DROP TABLE t1, t2;
|
|
CREATE TABLE t1( a INT, b INT, KEY(a), KEY(b) );
|
|
INSERT INTO t1 VALUES (1, 2), (1, 2), (1, 2), (1, 2);
|
|
SELECT * FROM t1 FORCE INDEX(a, b) WHERE a = 1 AND b = 2;
|
|
a b
|
|
1 2
|
|
1 2
|
|
1 2
|
|
1 2
|
|
DROP TABLE t1;
|
|
# Code coverage of fix.
|
|
CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
|
|
INSERT INTO t1 (b) VALUES (1);
|
|
UPDATE t1 SET b = 2 WHERE a = 1;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 2
|
|
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b VARCHAR(1) );
|
|
INSERT INTO t2 (b) VALUES ('a');
|
|
UPDATE t2 SET b = 'b' WHERE a = 1;
|
|
SELECT * FROM t2;
|
|
a b
|
|
1 b
|
|
DROP TABLE t1, t2;
|
|
#---------------- 2-sweeps read Index merge test 2 -------------------------------
|
|
create table t1 (
|
|
pk int primary key,
|
|
key1 int,
|
|
key2 int,
|
|
filler char(200),
|
|
filler2 char(200),
|
|
index(key1),
|
|
index(key2)
|
|
);
|
|
insert into t1 select seq, seq, seq, 'filler-data', 'filler-data-2'
|
|
from seq_1000_to_1;
|
|
select * from t1 where (key1 >= 2 and key1 <= 10) or (pk >= 4 and pk <=8 );
|
|
pk key1 key2 filler filler2
|
|
10 10 10 filler-data filler-data-2
|
|
9 9 9 filler-data filler-data-2
|
|
8 8 8 filler-data filler-data-2
|
|
7 7 7 filler-data filler-data-2
|
|
6 6 6 filler-data filler-data-2
|
|
5 5 5 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
set @maxv=1000;
|
|
select * from t1 where
|
|
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
|
|
or key1=18 or key1=60;
|
|
pk key1 key2 filler filler2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
60 60 60 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
18 18 18 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
1 1 1 filler-data filler-data-2
|
|
select * from t1 where
|
|
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
|
|
or key1 < 3 or key1 > @maxv-11;
|
|
pk key1 key2 filler filler2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
990 990 990 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
1 1 1 filler-data filler-data-2
|
|
select * from t1 where
|
|
(pk < 5) or (pk > 10 and pk < 15) or (pk >= 50 and pk < 55 ) or (pk > @maxv-10)
|
|
or
|
|
(key1 < 5) or (key1 > 10 and key1 < 15) or (key1 >= 50 and key1 < 55 ) or (key1 > @maxv-10);
|
|
pk key1 key2 filler filler2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
1 1 1 filler-data filler-data-2
|
|
select * from t1 where
|
|
(pk > 10 and pk < 15) or (pk >= 50 and pk < 55 )
|
|
or
|
|
(key1 < 5) or (key1 > @maxv-10);
|
|
pk key1 key2 filler filler2
|
|
1000 1000 1000 filler-data filler-data-2
|
|
999 999 999 filler-data filler-data-2
|
|
998 998 998 filler-data filler-data-2
|
|
997 997 997 filler-data filler-data-2
|
|
996 996 996 filler-data filler-data-2
|
|
995 995 995 filler-data filler-data-2
|
|
994 994 994 filler-data filler-data-2
|
|
993 993 993 filler-data filler-data-2
|
|
992 992 992 filler-data filler-data-2
|
|
991 991 991 filler-data filler-data-2
|
|
54 54 54 filler-data filler-data-2
|
|
53 53 53 filler-data filler-data-2
|
|
52 52 52 filler-data filler-data-2
|
|
51 51 51 filler-data filler-data-2
|
|
50 50 50 filler-data filler-data-2
|
|
14 14 14 filler-data filler-data-2
|
|
13 13 13 filler-data filler-data-2
|
|
12 12 12 filler-data filler-data-2
|
|
11 11 11 filler-data filler-data-2
|
|
4 4 4 filler-data filler-data-2
|
|
3 3 3 filler-data filler-data-2
|
|
2 2 2 filler-data filler-data-2
|
|
1 1 1 filler-data filler-data-2
|
|
drop table t1;
|
|
#---------------- Clustered PK ROR-index_merge tests -----------------------------
|
|
create table t1
|
|
(
|
|
pk1 int not null,
|
|
pk2 int not null,
|
|
key1 int not null,
|
|
key2 int not null,
|
|
pktail1ok int not null,
|
|
pktail2ok int not null,
|
|
pktail3bad int not null,
|
|
pktail4bad int not null,
|
|
pktail5bad int not null,
|
|
pk2copy int not null,
|
|
badkey int not null,
|
|
filler1 char (200),
|
|
filler2 char (200),
|
|
key (key1),
|
|
key (key2),
|
|
/* keys with tails from CPK members */
|
|
key (pktail1ok, pk1),
|
|
key (pktail2ok, pk1, pk2),
|
|
key (pktail3bad, pk2, pk1),
|
|
key (pktail4bad, pk1, pk2copy),
|
|
key (pktail5bad, pk1, pk2, pk2copy),
|
|
primary key (pk1, pk2)
|
|
);
|
|
explain select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 range PRIMARY,key1 PRIMARY 8 NULL 7 Using index condition; Using where
|
|
select * from t1 where pk1 = 1 and pk2 < 80 and key1=0;
|
|
pk1 pk2 key1 key2 pktail1ok pktail2ok pktail3bad pktail4bad pktail5bad pk2copy badkey filler1 filler2
|
|
1 10 0 0 0 0 0 0 0 10 0 filler-data-10 filler2
|
|
1 11 0 0 0 0 0 0 0 11 0 filler-data-11 filler2
|
|
1 12 0 0 0 0 0 0 0 12 0 filler-data-12 filler2
|
|
1 13 0 0 0 0 0 0 0 13 0 filler-data-13 filler2
|
|
1 14 0 0 0 0 0 0 0 14 0 filler-data-14 filler2
|
|
1 15 0 0 0 0 0 0 0 15 0 filler-data-15 filler2
|
|
1 16 0 0 0 0 0 0 0 16 0 filler-data-16 filler2
|
|
1 17 0 0 0 0 0 0 0 17 0 filler-data-17 filler2
|
|
1 18 0 0 0 0 0 0 0 18 0 filler-data-18 filler2
|
|
1 19 0 0 0 0 0 0 0 19 0 filler-data-19 filler2
|
|
explain select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
|
|
select pk1,pk2 from t1 where key1 = 10 and key2=10 and 2*pk1+1 < 2*96+1;
|
|
pk1 pk2
|
|
95 59
|
|
95 58
|
|
95 57
|
|
95 56
|
|
95 55
|
|
95 54
|
|
95 53
|
|
95 52
|
|
95 51
|
|
95 50
|
|
explain select * from t1 where badkey=1 and key1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1 key1 4 const 91 Using where
|
|
set @tmp_index_merge_ror_cpk=@@optimizer_switch;
|
|
set optimizer_switch='extended_keys=off';
|
|
explain select * from t1 where pk1 < 7500 and key1 = 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref PRIMARY,key1 key1 4 const ROWS Using where
|
|
set optimizer_switch=@tmp_index_merge_ror_cpk;
|
|
explain select * from t1 where pktail1ok=1 and key1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1,pktail1ok pktail1ok 4 const 76 Using where
|
|
explain select * from t1 where pktail2ok=1 and key1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1,pktail2ok pktail2ok 4 const 82 Using where
|
|
explain select * from t1 where (pktail2ok=1 and pk1< 50000) or key1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge PRIMARY,key1,pktail2ok pktail2ok,key1 8,4 NULL 173 Using sort_union(pktail2ok,key1); Using where
|
|
explain select * from t1 where pktail3bad=1 and key1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1,pktail3bad pktail3bad 4 const 73 Using where
|
|
explain select * from t1 where pktail4bad=1 and key1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1,pktail4bad pktail4bad 4 const 82 Using where
|
|
explain select * from t1 where pktail5bad=1 and key1=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref key1,pktail5bad pktail5bad 4 const 69 Using where
|
|
explain select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge key1,key2 key1,key2 4,4 NULL 1 Using intersect(key1,key2); Using where
|
|
select pk1,pk2,key1,key2 from t1 where key1 = 10 and key2=10 limit 10;
|
|
pk1 pk2 key1 key2
|
|
104 49 10 10
|
|
104 48 10 10
|
|
104 47 10 10
|
|
104 46 10 10
|
|
104 45 10 10
|
|
104 44 10 10
|
|
104 43 10 10
|
|
104 42 10 10
|
|
104 41 10 10
|
|
104 40 10 10
|
|
drop table t1;
|
|
create table t1
|
|
(
|
|
RUNID varchar(22),
|
|
SUBMITNR varchar(5),
|
|
ORDERNR char(1),
|
|
PROGRAMM varchar(8),
|
|
TESTID varchar(4),
|
|
UCCHECK char(1),
|
|
ETEXT varchar(80),
|
|
ETEXT_TYPE char(1),
|
|
INFO char(1),
|
|
SEVERITY tinyint(3),
|
|
TADIRFLAG char(1),
|
|
PRIMARY KEY (RUNID,SUBMITNR,ORDERNR,PROGRAMM,TESTID,UCCHECK),
|
|
KEY `TVERM~KEY` (PROGRAMM,TESTID,UCCHECK)
|
|
) DEFAULT CHARSET=latin1;
|
|
update t1 set `ETEXT` = '', `ETEXT_TYPE`='', `INFO`='', `SEVERITY`='', `TADIRFLAG`=''
|
|
WHERE
|
|
`RUNID`= '' AND `SUBMITNR`= '' AND `ORDERNR`='' AND `PROGRAMM`='' AND
|
|
`TESTID`='' AND `UCCHECK`='';
|
|
drop table t1;
|
|
#
|
|
# Bug#50402 Optimizer producing wrong results when using Index Merge on InnoDB
|
|
#
|
|
CREATE TABLE t1 (f1 INT, PRIMARY KEY (f1));
|
|
INSERT INTO t1 VALUES (2);
|
|
CREATE TABLE t2 (f1 INT, f2 INT, f3 char(1),
|
|
PRIMARY KEY (f1), KEY (f2), KEY (f3) );
|
|
INSERT INTO t2 VALUES (1, 1, 'h'), (2, 3, 'h'), (3, 2, ''), (4, 2, '');
|
|
SELECT t1.f1 FROM t1
|
|
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
|
|
f1
|
|
2
|
|
EXPLAIN SELECT t1.f1 FROM t1
|
|
WHERE (SELECT COUNT(*) FROM t2 WHERE t2.f3 = 'h' AND t2.f2 = t1.f1) = 0 AND t1.f1 = 2;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY t1 system PRIMARY NULL NULL NULL 1
|
|
2 SUBQUERY t2 ref f2,f3 f3 2 const 2 Using index condition; Using where
|
|
DROP TABLE t1,t2;
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (a int, b int, c int, filler char(100),
|
|
key(a), key(b), key(c));
|
|
insert into t1 select
|
|
A.a * B.a*10 + C.a*100,
|
|
A.a * B.a*10 + C.a*100,
|
|
A.a,
|
|
'filler'
|
|
from t0 A, t0 B, t0 C;
|
|
This should use union:
|
|
explain select * from t1 where a=1 or b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using union(a,b); Using where
|
|
This should use ALL:
|
|
set optimizer_switch='default,index_merge=off,rowid_filter=off';
|
|
explain select * from t1 where a=1 or b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where
|
|
This should use sort-union:
|
|
set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
|
|
explain select * from t1 where a=1 or b=1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 2 Using sort_union(a,b); Using where
|
|
This will use sort-union:
|
|
set optimizer_switch=default;
|
|
explain select * from t1 where a<1 or b <1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where
|
|
This should use ALL:
|
|
set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
|
|
explain select * from t1 where a<1 or b <1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where
|
|
This should use ALL:
|
|
set optimizer_switch='default,index_merge=off,rowid_filter=off';
|
|
explain select * from t1 where a<1 or b <1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL a,b NULL NULL NULL 1000 Using where
|
|
This will use sort-union:
|
|
set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
|
|
explain select * from t1 where a<1 or b <1;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 38 Using sort_union(a,b); Using where
|
|
alter table t1 add d int, add key(d);
|
|
update t1 set d=a;
|
|
This will use sort_union:
|
|
set optimizer_switch=default;
|
|
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b,c,d a,b 5,5 NULL 3 Using sort_union(a,b); Using where
|
|
And if we disable sort_union, union:
|
|
set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
|
|
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b,c,d c,d 5,5 NULL 100 Using union(c,d); Using where
|
|
drop table t1;
|
|
create table t1 (
|
|
a int, b int, c int,
|
|
filler1 char(200), filler2 char(200),
|
|
key(a),key(b),key(c)
|
|
);
|
|
insert into t1
|
|
select A.a+10*B.a, A.a+10*B.a, A.a+10*B.a+100*C.a, 'foo', 'bar'
|
|
from t0 A, t0 B, t0 C, t0 D where D.a<5;
|
|
This should be intersect:
|
|
set optimizer_switch=default;
|
|
explain select * from t1 where a=10 and b=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
|
|
No intersect when index_merge is disabled:
|
|
set optimizer_switch='default,index_merge=off,rowid_filter=off';
|
|
explain select * from t1 where a=10 and b=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 49 Using where
|
|
No intersect if it is disabled:
|
|
set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off,rowid_filter=off';
|
|
explain select * from t1 where a=10 and b=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ref a,b a 5 const 49 Using where
|
|
Do intersect when union was disabled
|
|
set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
|
|
explain select * from t1 where a=10 and b=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
|
|
Do intersect when sort_union was disabled
|
|
set optimizer_switch='default,index_merge_sort_union=off,rowid_filter=off';
|
|
explain select * from t1 where a=10 and b=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 1 Using intersect(a,b); Using where
|
|
This will use intersection inside a union:
|
|
set optimizer_switch=default;
|
|
explain select * from t1 where a=10 and b=10 or c=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b,c a,b,c 5,5,5 NULL 6 Using union(intersect(a,b),c); Using where
|
|
Should be only union left:
|
|
set optimizer_switch='default,index_merge_intersection=off,rowid_filter=off';
|
|
explain select * from t1 where a=10 and b=10 or c=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using union(a,c); Using where
|
|
This will switch to sort-union (intersection will be gone, too,
|
|
that's a known limitation:
|
|
set optimizer_switch='default,index_merge_union=off,rowid_filter=off';
|
|
explain select * from t1 where a=10 and b=10 or c=10;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge a,b,c a,c 5,5 NULL 54 Using sort_union(a,c); Using where
|
|
set optimizer_switch=default;
|
|
drop table t0, t1;
|
|
#
|
|
# BUG#834514 Assertion `!table || (!table->read_set || bitmap_is_set(...' with aggregates
|
|
#
|
|
CREATE TABLE t1 ( a int , b int, c int, KEY (b), PRIMARY KEY (a)) ;
|
|
INSERT INTO t1 VALUES (1,4,0),(5,0,0),(6,7,0),(7,7,0),(8,1,0),(9,7,0),(10,1,0);
|
|
CREATE TABLE t2 ( b int, c int, KEY (c,b)) ;
|
|
INSERT INTO t2 VALUES (7,0),(1,0),(7,0),(1,0);
|
|
CREATE TABLE t3 ( a int ) ;
|
|
SELECT COUNT(DISTINCT t2.b), CONCAT(t1.c)
|
|
FROM t1, t2
|
|
WHERE (t2.c = t1.c)
|
|
AND (
|
|
t1.b IN ( 4 )
|
|
OR t1.a = 137
|
|
AND EXISTS ( SELECT a FROM t3 )
|
|
)
|
|
GROUP BY 2;
|
|
COUNT(DISTINCT t2.b) CONCAT(t1.c)
|
|
2 0
|
|
DROP TABLE t1,t2,t3;
|
|
#
|
|
# MDEV-4556 Server crashes in SEL_ARG::rb_insert with index_merge+index_merge_sort_union, FORCE INDEX
|
|
#
|
|
CREATE TABLE t1 (
|
|
pk int,
|
|
code char(2),
|
|
population_rate int,
|
|
area_rate int,
|
|
primary key (pk),
|
|
index (code),
|
|
key (population_rate),
|
|
key (area_rate)
|
|
);
|
|
INSERT INTO t1 VALUES (1,'WI',20, 23), (2, 'WA', 13, 18);
|
|
EXPLAIN
|
|
SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code )
|
|
WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL )
|
|
AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX');
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge PRIMARY,code,population_rate,area_rate PRIMARY,population_rate,area_rate,code 4,5,5,3 NULL 2 Using sort_union(PRIMARY,population_rate,area_rate,code); Using where
|
|
SELECT * FROM t1 FORCE INDEX ( PRIMARY, population_rate, area_rate, code )
|
|
WHERE pk = 1 OR population_rate = 1 OR ( area_rate IN ( 1,2 ) OR area_rate IS NULL )
|
|
AND (population_rate = 25 OR area_rate BETWEEN 2 AND 25 OR code BETWEEN 'MA' AND 'TX');
|
|
pk code population_rate area_rate
|
|
1 WI 20 23
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-5069: Server crashes in SEL_ARG::increment_use_count with index_merge+index_merge_sort_union, FORCE INDEX
|
|
#
|
|
CREATE TABLE t1 (
|
|
c1 varchar(64),
|
|
i int,
|
|
pk integer auto_increment,
|
|
c2 varchar(64),
|
|
index (c1),
|
|
index (i),
|
|
primary key (pk),
|
|
key (c2)) ENGINE=myisam;
|
|
ALTER TABLE t1 DISABLE KEYS;
|
|
INSERT INTO t1 VALUES
|
|
('West Virginia', 6121, NULL, 'California'),('Georgia', 60177, NULL, 'Arkansas'),
|
|
('Delaware', 90, NULL, 'Oregon'),('Wyoming', 7, NULL, 'Missouri'),(
|
|
'Delaware', 2, NULL, 'Utah'),('Wisconsin', 0, NULL, 'Iowa'),
|
|
('Kansas', 0, NULL, 'Florida'),('Ohio', 34358, NULL, 'Colorado'),
|
|
('Maine', 118, NULL, 'Texas'),('Mississippi', 0, NULL, 'Georgia'),
|
|
('Tennessee', 4, NULL, 'N/A'),('Georgia', 0, NULL, 'New Hampshire'),
|
|
('Wyoming', 2, NULL, 'N/A'),('Florida', 0, NULL, 'Arizona'),
|
|
('Rhode Island', -24002, NULL, 'Michigan'),('Alabama', 142, NULL, 'Indiana'),
|
|
('Colorado', 0, NULL, 'Louisiana'),('Michigan', 21194, NULL, 'Louisiana'),
|
|
('Oklahoma', 31475, NULL, 'Alabama'),('Pennsylvania', 0, NULL, 'Oklahoma'),
|
|
('Texas', 0, NULL, 'Texas'),('West Virginia', 5, NULL, 'Utah'),
|
|
('Florida', 49653, NULL, 'Kentucky'),('Tennessee', 19075, NULL, 'Oregon'),
|
|
('Maine', 3, NULL, 'Kansas, Kentucky, Iowa'),('Iowa', 1, NULL, 'South Dakota'),
|
|
('Kansas', -4037, NULL, 'Virginia'),('Delaware', 22550, NULL, 'Utah'),
|
|
('Illinois', 14634, NULL, 'South Carolina, Colorado'),
|
|
('Kansas', 6, NULL, 'South Dakota'),('Delaware', 9, NULL, ''),
|
|
('', 0, NULL, 'Utah, Delaware, Florida, Georgia, Nevada'),
|
|
('Colorado', 8, NULL, 'Montana'),('Maryland', 2689, NULL, 'Hawaii'),
|
|
('Florida', -12306, NULL, 'Delaware'),
|
|
('Indiana', 38567, NULL, 'Iowa, Minnesota, Maine'),
|
|
('Oklahoma', 9, NULL, 'Delaware, Kansas, Oregon, Nebraska, Maryland, Minnesota'),
|
|
('Tennessee', 12460, NULL, NULL),('Kentucky', 0, NULL, 'Ohio'),
|
|
('Nevada', 7, NULL, 'Vermont, Oregon, Oklahoma, Montana'),
|
|
('Nebraska', 61966, NULL, 'Nevada'),('Alaska', 131, NULL, 'Louisiana, Maine'),
|
|
('Wisconsin', 4, NULL, 'Nevada'),('South Carolina', 0, NULL, 'Washington'),
|
|
('West Virginia', 51314, NULL, 'Ohio'),('Louisiana', 0, NULL, ''),
|
|
('Pennsylvania', 0, NULL, 'Iowa, Idaho'),('Arkansas', 14010, NULL, 'Indiana'),
|
|
('Wyoming', -15514, NULL, 'Maine'),('Georgia', 0, NULL, 'N/A'),
|
|
('Kentucky', 1, NULL, 'Idaho'),('Wyoming', 60249, NULL, 'Indiana, Iowa'),
|
|
('Pennsylvania', 69, NULL, 'W'),
|
|
('New Mexico', 11480, NULL, 'Florida, Georgia, Hawaii'),
|
|
('South Carolina', 9, NULL, 'Iowa'),('Virginia', 0, NULL, 'Connecticut'),
|
|
('Mississippi', 19749, NULL, 'Rhode Island'),('Illinois', 5, NULL, 'Virginia'),
|
|
('Texas', -1749, NULL, 'Tennessee'),('Arizona', 28, NULL, 'California'),
|
|
('Florida', 62151, NULL, 'Kansas'),('California', 172, NULL, 'SC'),
|
|
('New Jersey', 0, NULL, 'North Carolina'),('Wyoming', 4, NULL, 'I'),
|
|
('Kansas', 10683, NULL, 'California'),('Arkansas', -14275, NULL, 'K'),
|
|
('Arizona', 5, NULL, 'California, Delaware, Rhode Island, Maryland'),
|
|
('Florida', 0, NULL, 'T'),('Alaska', 241, NULL, 'Virginia');
|
|
ALTER TABLE t1 ENABLE KEYS;
|
|
EXPLAIN
|
|
SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2)
|
|
WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1
|
|
NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 ALL PRIMARY,c1,i,c2 NULL NULL NULL 69 Using where
|
|
DROP TABLE t1;
|
|
set optimizer_switch= @optimizer_switch_save;
|
|
#
|
|
# MDEV-21932: ROR union with index_merge_sort_union=off
|
|
#
|
|
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
insert into t0 select a+10 from t0;
|
|
insert into t0 select a+20 from t0;
|
|
insert into t0 select a+40 from t0;
|
|
insert into t0 select a+80 from t0;
|
|
insert into t0 select a+160 from t0;
|
|
delete from t0 where a > 300;
|
|
create table t1 (
|
|
f1 int, f2 int, f3 int, f4 int,
|
|
primary key (f1), key (f3), key(f4)
|
|
) engine=myisam;
|
|
insert into t1 select a+100, a+100, a+100, a+100 from t0;
|
|
insert into t1 VALUES (9,0,2,6), (9930,0,0,NULL);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
set optimizer_switch='index_merge_sort_union=off';
|
|
set optimizer_switch='index_merge_union=on';
|
|
explain select * from t1
|
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 3 Using union(f3,PRIMARY,f3); Using where
|
|
select * from t1
|
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
|
f1 f2 f3 f4
|
|
9 0 2 6
|
|
insert into t1 values (52,0,1,0),(53,0,1,0);
|
|
insert into t1 values (50,0,1,0),(51,0,1,0);
|
|
insert into t1 values (48,0,1,0),(49,0,1,0);
|
|
insert into t1 values (46,0,1,0),(47,0,1,0);
|
|
insert into t1 values (44,0,1,0),(45,0,1,0);
|
|
analyze table t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status Engine-independent statistics collected
|
|
test.t1 analyze status OK
|
|
explain select * from t1
|
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 SIMPLE t1 index_merge PRIMARY,f3,f4 f3,PRIMARY,f3 5,4,5 NULL 13 Using union(f3,PRIMARY,f3); Using where
|
|
select * from t1
|
|
where (( f3 = 1 or f1 = 7 ) and f1 < 10) or
|
|
(f3 between 2 and 2 and ( f3 = 1 or f4 < 7 ));
|
|
f1 f2 f3 f4
|
|
9 0 2 6
|
|
drop table t0,t1;
|
|
set optimizer_switch= @optimizer_switch_save;
|
|
#
|
|
# MDEV-16695: Estimate for rows of derived tables is very high when we are using index_merge union
|
|
#
|
|
create table t0
|
|
(
|
|
key1 int not null,
|
|
INDEX i1(key1)
|
|
);
|
|
insert into t0 select * from seq_1_to_1024;
|
|
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 key8 int not null, add index i8(key8);
|
|
update t0 set key2=key1,key3=key1,key8=1024-key1;
|
|
analyze table t0;
|
|
Table Op Msg_type Msg_text
|
|
test.t0 analyze status Engine-independent statistics collected
|
|
test.t0 analyze status OK
|
|
set @optimizer_switch_save=@@optimizer_switch;
|
|
set optimizer_switch='derived_merge=off,derived_with_keys=off';
|
|
explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
|
|
2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
|
|
select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
|
|
key1 key2 key3 key8
|
|
3 3 3 1021
|
|
set optimizer_use_condition_selectivity=2;
|
|
explain select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
|
|
id select_type table type possible_keys key key_len ref rows Extra
|
|
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 Using where
|
|
2 DERIVED t0 index_merge i1,i2,i8 i1,i2 4,4 NULL 2 Using union(i1,i2); Using where
|
|
select * from (select * from t0 where key1 = 3 or key2 =3) as Z where Z.key8 > 5;
|
|
key1 key2 key3 key8
|
|
3 3 3 1021
|
|
set @@optimizer_switch= @optimizer_switch_save;
|
|
drop table t0;
|
|
# End of 10.1 tests
|