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
|
#
# Tests for "ANALYZE $statement" feature (PostgreSQL's analog is called EXPLAIN ANALYZE)
#
--disable_warnings
drop table if exists t0,t1,t2,t3;
--enable_warnings
create table t0 (a int) engine=myisam;
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t1 (a int) engine=myisam;
INSERT INTO t1 select * from t0;
--echo # Try a few basic selects to see that r_rows and r_filtered columns work
analyze select * from t1;
analyze select * from t1 where a<5;
analyze select * from t1 where a>100;
--echo # ANALYZE DELETE will delete rows:
analyze delete from t1 where a in (2,3,4);
select * from t1;
drop table t1;
--echo # ANALYZE UPDATE will make updates:
create table t1(a int, b int);
insert into t1 select a,a from t0;
analyze update t1 set b=100+b where a in (6,7,8);
select * from t1;
drop table t1;
--echo # Check that UNION works
create table t1(a int, b int);
insert into t1 select a,a from t0;
analyze (select * from t1 A where a<5) union (select * from t1 B where a in (5,6));
analyze (select * from t1 A where a<5) union (select * from t1 B where a in (1,2));
drop table t1;
drop table t0;
--echo #
--echo # Try a subquery.
--echo #
create table t0 (a int, b int);
insert into t0 values
(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
create table t1 (a int, b int);
insert into t1 values (1,1),(2,2),(3,3);
#
# t1 t0
# a=1 (0,1) 2 rows
# a=2 (0,1,2) 3 rows
# a=3 (0,1,2,3) 4 rows
#
# TOTAL TOTAL= 9 rows. 3 executions, avg=3 rows.
# WHERE is satisfied for 1 row per query, which gives filtered=33.3
--echo # See .test file for the right values of r_rows and r_filtered.
analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1;
--echo # Try a subquery that is never executed
analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1 where t1.a > 5;
drop table t0, t1;
--echo #
--echo # Tests for join buffering
--echo #
create table t0 (a int, b int);
insert into t0 values
(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
create table t1 like t0;
insert into t1 select * from t0;
explain select * from t0, t1 where t0.a<5 and t1.a<5;
--echo # These should have filtered=50
analyze select * from t0, t1 where t0.a<5 and t1.a<5;
explain select * from t0, t1 where t0.a<5 and t1.b=t0.b;
--echo # Now, t1 should have filtered=10
analyze select * from t0, t1 where t0.a<5 and t1.b=t0.b;
explain select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b;
--echo # Now, t1 should have filtered=10
analyze select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b;
--echo # TODO: Check what is counted for "range checked for each record".
--echo #
--echo # Test for joins
--echo #
create table t2 (key1 int, key2x int, col1 int, key(key1), key(key2x));
insert into t2 select A.a + 10 *B.a +100 * C.a,
(A.a + 10 *B.a +100 * C.a)*2,
A.a + 10 *B.a +100 * C.a
from t0 A, t0 B, t0 C;
--echo # This always has matches, filtered=100%.
analyze select * from t1,t2 where t2.key1=t1.a;
--echo # This shows r_rows=0. It is actually 0.5 (should r_rows be changed to double?)
analyze select * from t1,t2 where t2.key2x=t1.a;
select * from t1,t2 where t2.key2x=t1.a;
--echo # This has t2.filtered=40% (there are 5 values: {0,1,2,3,4}. two of them have mod=0)
analyze select * from t1,t2 where t2.key2x=t1.a and mod(t2.col1,4)=0;
drop table t0,t1,t2;
--echo #
--echo # Check non-merged derived tables
--echo #
create table t0 (a int, b int);
insert into t0 values
(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
update t0 set b=b/3;
analyze select * from (select count(*),max(a),b from t0 group by b) T;
drop table t0;
--echo #
--echo # Check ORDER/GROUP BY
--echo #
create table t0 (a int, b int);
insert into t0 values
(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
analyze select count(*),max(a),b from t0 where a<7 group by b;
drop table t0;
--echo #
--echo # Check multi-table UPDATE/DELETE.
--echo #
create table t0 (a int, b int);
create table t1 (a int, b int);
insert into t0 values (0,0),(2,2),(4,4), (8,8);
insert into t1 values (0,0),(2,2), (6,6);
analyze select * from t0,t1 where t0.a=t1.a;
analyze update t0,t1 set t1.b=5555 where t0.a=t1.a;
select * from t1;
analyze delete t1 from t1, t0 where t0.a=t1.a;
select * from t1;
drop table t0, t1;
|
