1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
# t/index_merge_myisam.test
#
# Index merge tests
#
# Last update:
# 2006-08-07 ML test refactored (MySQL 5.1)
# Main code of several index_merge tests
# -> include/index_merge*.inc
# wrapper t/index_merge_innodb.test sources now several
# include/index_merge*.inc files
#
let $engine_type= MyISAM;
# MyISAM supports Merge tables
let $merge_table_support= 1;
set @optimizer_switch_save= @@optimizer_switch;
set optimizer_switch='index_merge_sort_intersection=off';
--source include/index_merge1.inc
--source include/index_merge_ror.inc
--source include/index_merge2.inc
--source include/index_merge_2sweeps.inc
--source include/index_merge_ror_cpk.inc
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;
--echo This should use union:
explain select * from t1 where a=1 or b=1;
--echo This should use ALL:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a=1 or b=1;
--echo This should use sort-union:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=1 or b=1;
--echo This will use sort-union:
set optimizer_switch=default;
explain select * from t1 where a<1 or b <1;
--echo This should use ALL:
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where a<1 or b <1;
--echo This should use ALL:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a<1 or b <1;
--echo This will use sort-union:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a<1 or b <1;
alter table t1 add d int, add key(d);
update t1 set d=a;
--echo 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);
--echo And if we disable sort_union, union:
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where (a=3 or b in (1,2)) and (c=3 or d=4);
drop table t1;
# Now test that intersection can be disabled
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;
--echo This should be intersect:
set optimizer_switch=default;
explain select * from t1 where a=10 and b=10;
--echo No intersect when index_merge is disabled:
set optimizer_switch='default,index_merge=off';
explain select * from t1 where a=10 and b=10;
--echo No intersect if it is disabled:
set optimizer_switch='default,index_merge_sort_intersection=off,index_merge_intersection=off';
explain select * from t1 where a=10 and b=10;
--echo Do intersect when union was disabled
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=10 and b=10;
--echo Do intersect when sort_union was disabled
set optimizer_switch='default,index_merge_sort_union=off';
explain select * from t1 where a=10 and b=10;
# Now take union-of-intersection and see how we can disable parts of it
--echo This will use intersection inside a union:
set optimizer_switch=default;
explain select * from t1 where a=10 and b=10 or c=10;
--echo Should be only union left:
set optimizer_switch='default,index_merge_intersection=off';
explain select * from t1 where a=10 and b=10 or c=10;
--echo This will switch to sort-union (intersection will be gone, too,
--echo thats a known limitation:
set optimizer_switch='default,index_merge_union=off';
explain select * from t1 where a=10 and b=10 or c=10;
set optimizer_switch=default;
drop table t0, t1;
set optimizer_switch= @optimizer_switch_save;
|
