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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
#
# Test of optimizer_costs
#
--source include/have_innodb.inc
--source include/have_sequence.inc
select table_name,engine from information_schema.tables where table_name="optimizer_costs";
show create table information_schema.optimizer_costs;
let $start_engines=`select count(*) from information_schema.optimizer_costs`;
--vertical_results
select * from information_schema.optimizer_costs where engine in
("memory","innodb","aria","default") order by engine;
--horizontal_results
show variables like "optimizer%cost";
show variables like "optimizer_disk_read_ratio";
--echo #
--echo # Test change some 'default' variables
--echo #
SELECT @@optimizer_disk_read_ratio,@@optimizer_index_block_copy_cost;
SET global optimizer_disk_read_ratio=0.8;
SET global optimizer_index_block_copy_cost=0.1;
SELECT @@optimizer_disk_read_ratio,@@optimizer_index_block_copy_cost;
select optimizer_disk_read_ratio,optimizer_index_block_copy_cost from information_schema.optimizer_costs where engine='default';
SET global optimizer_disk_read_ratio=default;
SET global optimizer_index_block_copy_cost=default;
SELECT @@optimizer_disk_read_ratio,@@optimizer_index_block_copy_cost;
--echo #
--echo # Test change some 'engine' variables
--echo #
select @@MEMORY.optimizer_row_lookup_cost;
set @tmp=@@MEMORY.optimizer_row_lookup_cost;
set @@global.MEMORY.optimizer_row_lookup_cost=1;
select @@MEMORY.optimizer_row_lookup_cost;
set @@global.MEMORY.optimizer_row_lookup_cost=default;
select @@MEMORY.optimizer_row_lookup_cost;
set @@global.MEMORY.optimizer_row_lookup_cost=@tmp;
select @@MEMORY.optimizer_row_lookup_cost;
--echo #
--echo # Print variables with different syntaxes
--echo #
SHOW VARIABLES like "optimizer_row_lookup_cost";
SELECT @@optimizer_row_lookup_cost;
SELECT @@global.default.optimizer_row_lookup_cost;
SELECT @@global.default.`optimizer_row_lookup_cost`;
SELECT @@MEMORY.optimizer_row_lookup_cost;
SELECT @@memory.optimizer_row_lookup_cost;
SELECT @@InnoDB.optimizer_row_lookup_cost;
--echo #
--echo # Accessing not existing cost
--echo #
SELECT @@not_existing.optimizer_row_lookup_cost;
SELECT @@NOT_existing.optimizer_row_lookup_cost;
select engine from information_schema.optimizer_costs where engine like '%existing';
--echo #
--echo # Creating a new cost structure
--echo #
SET global new_engine.optimizer_disk_read_cost=100;
select * from information_schema.optimizer_costs where engine like 'new_engine';
select @@new_engine.optimizer_disk_read_cost, @@new_engine.optimizer_row_copy_cost;
--echo #
--echo # Errors
--echo #
--error ER_PARSE_ERROR
SELECT @@default.optimizer_disk_read_cost;
--error ER_WRONG_TYPE_FOR_VAR
set global Aria.optimizer_disk_read_cost=NULL;
set @tmp=@@Aria.optimizer_disk_read_cost;
SET global Aria.optimizer_disk_read_cost=-1;
select @@Aria.optimizer_disk_read_cost;
SET global Aria.optimizer_disk_read_cost=200000;
select @@Aria.optimizer_disk_read_cost;
set global Aria.optimizer_disk_read_cost=@tmp;
select @@Aria.optimizer_disk_read_cost;
--echo #
--echo # Test of cost of ref compared to table scan + join_cache
--echo #
create or replace table t1 (p int primary key, a char(10)) engine=myisam;
create or replace table t2 (p int primary key, i int, a char(10), key k2(a)) engine=myisam;
insert into t2 select seq,seq,'a' from seq_1_to_512;
insert into t1 select seq,'a' from seq_1_to_4;
explain select count(*) from t1, t2 where t1.p = t2.i;
insert into t1 select seq,'a' from seq_5_to_10;
explain select count(*) from t1, t2 where t1.p = t2.i;
drop table t1,t2;
--echo #
--echo # Test of optimizer_scan_setup_cost
--echo #
create table t1 (p int primary key, a char(10)) engine=myisam;
create table t2 (p int primary key, i int, a char(10), key k1(i), key k2(a)) engine=myisam;
insert into t1 values (2, 'qqqq'), (11, 'yyyy');
insert into t2 values (1, 2, 'qqqq'), (2, 2, 'pppp'),
(3, 2, 'yyyy'), (4, 3, 'zzzz');
set @org_myisam_disk_read_ratio=@@myisam.optimizer_disk_read_ratio;
set @@optimizer_scan_setup_cost=10,@@global.myisam.optimizer_disk_read_ratio=0.2;
flush tables;
explain select sum(t2.p+length(t1.a)) from t1, t2 where t1.p = t2.i;
set @@optimizer_scan_setup_cost=0.0, @@global.myisam.optimizer_disk_read_ratio=0.0;
flush tables;
explain select sum(t2.p+length(t1.a)) from t1, t2 where t1.p = t2.i;
set @@optimizer_scan_setup_cost=default,@@global.myisam.optimizer_disk_read_ratio=@org_myisam_disk_read_ratio;
flush tables;
drop table t1,t2;
--echo #
--echo # Test of group by optimization
--echo #
set @@optimizer_scan_setup_cost=0;
CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) engine=myisam;
INSERT INTO t1 values (1,'2001-01-01'),(1,'2001-01-02'),
(1,'2001-01-03'),(1,'2001-01-04'),
(2,'2001-01-01'),(2,'2001-01-02'),
(2,'2001-01-03'),(2,'2001-01-04'),
(3,'2001-01-01'),(3,'2001-01-02'),
(3,'2001-01-03'),(3,'2001-01-04'),
(4,'2001-01-01'),(4,'2001-01-02'),
(4,'2001-01-03'),(4,'2001-01-04');
analyze table t1;
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
insert into t1 values (3,'2001-01-03'),(3,'2001-01-04');
analyze table t1;
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104e0 GROUP BY id;
drop table t1;
set @@optimizer_scan_setup_cost=default;
--echo #
--echo # Test of straight join costs
--echo #
create table t1 (l_orderkey int(11) NOT NULL,
l_partkey int(11) DEFAULT NULL,
l_suppkey int(11) DEFAULT NULL,
PRIMARY KEY (l_orderkey)) engine=aria;
insert into t1 select seq,seq,seq from seq_1_to_1000;
explain select straight_join count(*) from seq_1_to_10000,t1 where seq=l_orderkey;
show status like "last_query_cost";
set @org_cost=@@aria.optimizer_key_next_find_cost;
# Set cost for t1 high so that we cannot use it for index scans
set global aria.optimizer_key_next_find_cost=1000;
flush tables;
explain select count(*) from seq_1_to_10000,t1 where seq=l_orderkey;
show status like "last_query_cost";
set global aria.optimizer_key_next_find_cost=@org_cost;
drop table t1;
--echo #
--echo # Testing distinct group optimization
--echo #
create table t1 (a int, b int, key(a,b));
insert into t1 select seq,seq from seq_1_to_1000;
explain select count(distinct a,b) from t1;
explain select count(distinct a,b) from t1 where a>100;
explain select count(distinct a,b) from t1 where a>800;
update t1 set a=mod(a,10);
analyze table t1;
explain select count(distinct a,b) from t1;
explain select count(distinct a,b) from t1 where a>1;
explain select count(distinct a,b) from t1 where a>8;
update t1 set b=mod(b,2);
analyze table t1;
explain select count(distinct a,b) from t1;
explain select count(distinct a,b) from t1 where a>1;
explain select count(distinct a,b) from t1 where a>8;
drop table t1;
--echo #
--echo # cleanup
--echo #
let $end_engines=`select count(*) from information_schema.optimizer_costs`;
--echo Start_engines: $start_engines End_engines: $end_engines
|
