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
|
#
# Bug 20219846: EXPLAIN FOR LIMIT QUERY SHOWS FILESORT BUT EXECUTION
# IS DONE WITH INDEX ONLY
#
CREATE TABLE t0 (
i0 INTEGER NOT NULL
);
INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1 (
pk INTEGER PRIMARY KEY,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
INDEX k1 (i1)
) ENGINE=InnoDB;
INSERT INTO t1
SELECT a0.i0 + 10*a1.i0 + 100*a2.i0,
(a0.i0 + 10*a1.i0 + 100*a2.i0) % 50,
a0.i0 + 10*a1.i0 + 100*a2.i0
FROM t0 AS a0, t0 AS a1, t0 AS a2;
CREATE TABLE t2 (
pk INTEGER PRIMARY KEY,
i1 INTEGER NOT NULL,
i2 INTEGER NOT NULL,
INDEX k1 (i1)
) ENGINE=InnoDB;
INSERT INTO t2
SELECT a0.i0 + 10*a1.i0 + 100*a2.i0,
(a0.i0 + 10*a1.i0 + 100*a2.i0) % 500,
a0.i0 + 10*a1.i0 + 100*a2.i0
FROM t0 AS a0, t0 AS a1, t0 AS a2;
ANALYZE TABLE t1,t2;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
# Explain should show "Using filesort"
EXPLAIN SELECT *
FROM t1 JOIN t2 ON t1.i1=t2.i1
WHERE t2.i2 > 3
ORDER BY t1.i1 LIMIT 20;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 NULL ALL k1 NULL NULL NULL 1000 100.00 Using filesort
1 SIMPLE t2 NULL ref k1 k1 4 test.t1.i1 2 33.33 Using where
Warnings:
Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`i1` AS `i1`,`test`.`t1`.`i2` AS `i2`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`i1` AS `i1`,`test`.`t2`.`i2` AS `i2` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`i1` = `test`.`t1`.`i1`) and (`test`.`t2`.`i2` > 3)) order by `test`.`t1`.`i1` limit 20
FLUSH STATUS;
SELECT *
FROM t1 JOIN t2 ON t1.i1=t2.i1
WHERE t2.i2 > 3
ORDER BY t1.i1 LIMIT 20;
pk i1 i2 pk i1 i2
0 0 0 500 0 500
50 0 50 500 0 500
100 0 100 500 0 500
150 0 150 500 0 500
200 0 200 500 0 500
250 0 250 500 0 500
300 0 300 500 0 500
350 0 350 500 0 500
400 0 400 500 0 500
450 0 450 500 0 500
500 0 500 500 0 500
550 0 550 500 0 500
600 0 600 500 0 500
650 0 650 500 0 500
700 0 700 500 0 500
750 0 750 500 0 500
800 0 800 500 0 500
850 0 850 500 0 500
900 0 900 500 0 500
950 0 950 500 0 500
# Status from execution should show that filesort was used
SHOW STATUS LIKE 'Sort%';
Variable_name Value
Sort_merge_passes 0
Sort_range 0
Sort_rows 1000
Sort_scan 1
DROP TABLE t0, t1, t2;
#
# Bug#30321546: OPTIMIZER TRACE SHOWS DIFFERENT ACTIONS FOR
# A QUERY IF ANOTHER QUERY RUN BEFORE
#
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
INSERT INTO t1 VALUES (1),(2),(3),(4);
INSERT INTO t2 VALUES (1),(2);
ANALYZE TABLE t1, t2;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
SELECT t1.a, t2.a FROM t1 JOIN t2
ON t1.a+t2.a = (SELECT COUNT(*) FROM t1);
a a
2 2
3 1
SET optimizer_trace="enabled=on";
SELECT a + (SELECT SUM(a) + (SELECT COUNT(a) FROM t1) FROM t1) AS cnt
FROM t2;
cnt
15
16
SELECT TRACE NOT RLIKE '"final_filtering_effect": 1' AS OK
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
OK
1
SET optimizer_trace="enabled=off";
DROP TABLE t1, t2;
#
# Bug#36918913 INCORRECT QUERY RESULT FOR INDEX_SUBQUERY TYPE IN NULL
# SUBQUERY
#
SET @save_optimizer_switch= @@optimizer_switch;
SET @@optimizer_switch= 'semijoin=off';
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE TABLE t2 (b INT);
INSERT INTO t2 VALUES (1), (2), (3);
ANALYZE TABLE t1, t2;
Table Op Msg_type Msg_text
test.t1 analyze status OK
test.t2 analyze status OK
SELECT * FROM t1 WHERE a IN (SELECT * FROM (
SELECT b FROM t2 ORDER BY b LIMIT 2) x where b > 1);
a
2
EXPLAIN FORMAT=TREE SELECT * FROM t1 WHERE a IN (SELECT * FROM (
SELECT b FROM t2 ORDER BY b LIMIT 2) x where b > 1);
EXPLAIN
-> Filter: <in_optimizer>(t1.a,<exists>(select #2)) (rows=3)
-> Table scan on t1 (rows=3)
-> Select #2 (subquery in condition; dependent)
-> Limit: 1 row(s) (rows=1)
-> Filter: (x.b > 1) (rows=2)
-> Covering index lookup on x using <auto_key0> (b=<cache>(t1.a)) (rows=2)
-> Materialize (rows=2)
-> Limit: 2 row(s) (rows=2)
-> Sort: t2.b, limit input to 2 row(s) per chunk (rows=3)
-> Table scan on t2 (rows=3)
DROP TABLE t1,t2;
SET @@optimizer_switch= @save_optimizer_switch;
|
