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
|
#
# Tests for the iteration-based FORMAT=JSON, that works with hypergraph
# optimizer
#
--source include/have_hypergraph.inc
--source include/elide_costs.inc
--echo #
--echo # Table scan, subquery, aggregates
CREATE TABLE t1 ( f1 INT PRIMARY KEY );
INSERT INTO t1 VALUES ( 1 );
INSERT INTO t1 VALUES ( 2 );
INSERT INTO t1 VALUES ( 3 );
ANALYZE TABLE t1;
EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE f1 = ( SELECT MIN(f1) FROM t1 AS i WHERE i.f1 > t1.f1 );
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE f1 = ( SELECT MIN(f1) FROM t1 AS i WHERE i.f1 > t1.f1 );
EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE f1 > ( SELECT f1 FROM t1 LIMIT 1 );
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE f1 > ( SELECT f1 FROM t1 LIMIT 1 );
drop table t1;
--echo #
--echo # Index range scan
create table t1 ( a int, b int, c int, d int, primary key(a,b));
insert into t1 values
(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4),
(1,2,5,1), (1,3,1,2), (1,4,2,3),
(2,1,3,4), (2,3,4,5), (2,4,5,1),
(3,1,1,2), (3,2,2,3), (3,4,3,4),
(4,1,4,5), (4,2,5,1), (4,3,1,2);
explain format=TREE select * from t1 where a > 2;
explain format=JSON select * from t1 where a > 2;
drop table t1;
--echo # Index lookup. Nested loop join
set @old_opt_switch=@@optimizer_switch;
set optimizer_switch='firstmatch=off,materialization=off,duplicateweedout=off,loosescan=on';
CREATE TABLE t1 ( i INTEGER, PRIMARY KEY (i) );
CREATE TABLE t2 ( i INTEGER, INDEX i1 (i) );
INSERT INTO t1 VALUES (2), (3), (4), (5);
INSERT INTO t2 VALUES (1), (2), (3), (4);
ANALYZE TABLE t1, t2;
EXPLAIN format=TREE SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i FROM t2);
EXPLAIN format=JSON SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i FROM t2);
DROP TABLE t1,t2;
set optimizer_switch=@old_opt_switch;
--echo # Index lookup. Nested loop join. Filter.
CREATE TABLE t1 (col_int INT, pk INT) ENGINE=InnoDB STATS_PERSISTENT=0;
INSERT INTO t1 VALUES (-100,1),(1,6);
CREATE TABLE t2 (
col_int_key INT,
col_varchar VARCHAR(100) NOT NULL DEFAULT "DEFAULT",
pk INT NOT NULL,
PRIMARY KEY (pk),
KEY (col_int_key)
) ENGINE=InnoDB STATS_PERSISTENT=0;
INSERT INTO t2 VALUES
(1,"GOOD",1),(100,"",2),(200,"",3),(300,"",4),(400,"",5),(500,"",8);
EXPLAIN FORMAT=TREE SELECT t1.*,t2.* FROM t1 straight_join t2
ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
EXPLAIN FORMAT=JSON SELECT t1.*,t2.* FROM t1 straight_join t2
ON t2.col_int_key = t1.col_int WHERE t2.pk < t1.pk;
DROP TABLE t1,t2;
--echo # Group aggregates, hash join, sort.
CREATE TABLE t1 (
pk int NOT NULL AUTO_INCREMENT,
col_varchar varchar(1),
col_varchar_key varchar(1),
PRIMARY KEY (pk),
KEY idx_CC_col_varchar_key (col_varchar_key)
);
INSERT INTO t1 VALUES (1,'n','X'),(2,'Y','8'),(3,'R','l');
ANALYZE TABLE t1;
EXPLAIN FORMAT=TREE SELECT
t1.col_varchar_key AS field1 FROM (t1, t1 as alias1)
WHERE NOT EXISTS( SELECT alias2.col_varchar_key FROM t1 AS alias2
WHERE alias2.col_varchar_key >= t1.col_varchar)
GROUP BY field1;
EXPLAIN FORMAT=JSON SELECT
t1.col_varchar_key AS field1 FROM (t1, t1 as alias1)
WHERE NOT EXISTS( SELECT alias2.col_varchar_key FROM t1 AS alias2
WHERE alias2.col_varchar_key >= t1.col_varchar)
GROUP BY field1;
drop table t1;
--echo # Information Schema
explain format=TREE select * from information_schema.engines e WHERE e.ENGINE="MyISAM";
explain format=JSON select * from information_schema.engines e WHERE e.ENGINE="MyISAM";
--echo # Materialize, window aggregates, Stream
CREATE TABLE t0 (i0 INTEGER);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER,
KEY(f1), KEY(f1,f2), KEY(f3));
INSERT INTO t1
SELECT i0, i0 + 10*i0,
i0 + 10*i0 + 100*i0
FROM t0 AS a0;
INSERT INTO t1
SELECT i0, i0 + 10*i0,
i0 + 10*i0 + 100*i0
FROM t0 AS a0;
INSERT INTO t1 VALUES (NULL, 1, 2);
INSERT INTO t1 VALUES (NULL, 1, 3);
ANALYZE TABLE t0, t1;
set sql_mode="";
EXPLAIN FORMAT=TREE SELECT * FROM
(SELECT f1, SUM(f2) OVER() FROM t1 GROUP BY f1) as dt
WHERE f1 > 2;
EXPLAIN FORMAT=JSON SELECT * FROM
(SELECT f1, SUM(f2) OVER() FROM t1 GROUP BY f1) as dt
WHERE f1 > 2;
--echo # Filter, nested loop
EXPLAIN FORMAT=TREE SELECT /*+ JOIN_ORDER(t0, dt) */ * FROM
(SELECT f1, f2, f3 FROM t1) as dt, t0
WHERE f1 > 3 and f2 < 50 and i0 > 3;
EXPLAIN FORMAT=JSON SELECT /*+ JOIN_ORDER(t0, dt) */ * FROM
(SELECT f1, f2, f3 FROM t1) as dt, t0
WHERE f1 > 3 and f2 < 50 and i0 > 3;
drop table t0, t1;
--echo # Explain analyze; Temporary table.
CREATE TABLE t1 (a INT NOT NULL, b CHAR(3) NOT NULL, PRIMARY KEY (a));
INSERT INTO t1 VALUES (1,'ABC'), (2,'EFG'), (3,'HIJ');
CREATE TABLE t2 (a INT NOT NULL,b CHAR(3) NOT NULL,PRIMARY KEY (a, b));
INSERT INTO t2 VALUES (1,'a'),(1,'b'),(3,'F');
ANALYZE TABLE t1, t2;
# Mask out all actual times
--replace_regex $elide_time
EXPLAIN analyze FORMAT=TREE SELECT t1.a, GROUP_CONCAT(t2.b) AS b FROM t1 LEFT JOIN t2 ON t1.a=t2.a GROUP BY t1.a ORDER BY t1.b;
EXPLAIN FORMAT=JSON SELECT t1.a, GROUP_CONCAT(t2.b) AS b FROM t1 LEFT JOIN t2 ON t1.a=t2.a GROUP BY t1.a ORDER BY t1.b;
drop table t1;
drop table t2;
--echo # Zero rows.
CREATE TABLE t1 (a INTEGER NOT NULL);
INSERT INTO t1 VALUES (1),(2),(3),(4);
ANALYZE TABLE t1;
EXPLAIN FORMAT=TREE SELECT * FROM t1 AS a LEFT JOIN t1 AS b ON FALSE
LEFT JOIN t1 AS c ON b.a=c.a;
EXPLAIN FORMAT=JSON SELECT * FROM t1 AS a LEFT JOIN t1 AS b ON FALSE
LEFT JOIN t1 AS c ON b.a=c.a;
DROP TABLE t1;
|
