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
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
|
--echo #
--echo # MDEV-34720: Poor plan choice for large JOIN with ORDER BY and small LIMIT
--echo #
--source include/have_sequence.inc
# We need optimizer trace
--source include/not_embedded.inc
create table t1 (
a int,
b int,
c int,
col1 int,
col2 int,
index(a),
index(b),
index(col1)
);
insert into t1 select
mod(seq, 100),
mod(seq, 95),
seq,
seq,
seq
from
seq_1_to_10000;
create table t10 (
a int,
a_value char(10),
key(a)
);
insert into t10 select seq, seq from seq_1_to_100;
create table t11 (
b int,
b_value char(10),
key(b)
);
insert into t11 select seq, seq from seq_1_to_150;
set @tmp_os=@@optimizer_trace;
set optimizer_trace=1;
--echo #
--echo # Query 1 - basic example.
--echo #
let $query= explain
select
*
from
t1
join t10 on t1.a=t10.a
join t11 on t1.b=t11.b
order by
t1.col1
limit 10;
--echo # Table t1 is not the first, have to use temporary+filesort:
eval $query;
set optimizer_join_limit_pref_ratio=10;
--echo # t1 is first, key=col1 produces ordering, no filesort or temporary:
eval $query;
set @trace=(select trace from information_schema.optimizer_trace);
--source include/optimizer_trace_no_costs.inc
select json_detailed(json_extract(@trace, '$**.join_limit_shortcut_choice')) as JS;
--echo #
--echo # Query 2 - same as above but without a suitable index.
--echo #
let $query=
explain
select
*
from
t1
join t10 on t1.a=t10.a
join t11 on t1.b=t11.b
order by
t1.col2
limit 10;
--echo # Table t1 is not the first, have to use temporary+filesort:
set optimizer_join_limit_pref_ratio=0;
eval $query;
--echo # t1 is first but there's no suitable index,
--echo # so we use filesort but using temporary:
set optimizer_join_limit_pref_ratio=10;
eval $query;
set @trace=(select trace from information_schema.optimizer_trace);
--source include/optimizer_trace_no_costs.inc
select json_detailed(json_extract(@trace, '$**.join_limit_shortcut_choice')) as JS;
--echo #
--echo # Query 3: Counter example with large limit
--echo #
let $query= explain
select
*
from
t1
join t10 on t1.a=t10.a
join t11 on t1.b=t11.b
order by
t1.col1
limit 5000;
--echo # Table t1 is not the first, have to use temporary+filesort:
set optimizer_join_limit_pref_ratio=0;
eval $query;
--echo # Same plan as above:
--echo # Table t1 is not the first, have to use temporary+filesort:
set optimizer_join_limit_pref_ratio=10;
eval $query;
set @trace=(select trace from information_schema.optimizer_trace);
--source include/optimizer_trace_no_costs.inc
select json_detailed(json_extract(@trace, '$**.join_limit_shortcut_choice')) as JS;
--echo #
--echo # Query 4: LEFT JOIN makes it impossible to put ORDER-BY-table first,
--echo # however the optimizer still puts it as sort_by_table.
--echo #
set optimizer_join_limit_pref_ratio=10;
explain
select
*
from
t10 left join (t1 join t11 on t1.b=t11.b ) on t1.a=t10.a
order by
t1.col2
limit 10;
set @trace=(select trace from information_schema.optimizer_trace);
--echo # This will show nothing as limit shortcut code figures that
--echo # it's not possible to use t1 to construct shortcuts:
--source include/optimizer_trace_no_costs.inc
select json_detailed(json_extract(@trace, '$**.join_limit_shortcut_choice')) as JS;
--echo #
--echo # Query 5: Same as Q1 but also with a semi-join
--echo #
set optimizer_join_limit_pref_ratio=default;
let $query= explain
select
*
from
t1
join t10 on t1.a=t10.a
join t11 on t1.b=t11.b
where
t1.a in (select a from t10) and
t1.b in (select b from t11)
order by
t1.col1
limit 10;
--echo # Table t1 is not the first, have to use temporary+filesort:
eval $query;
set optimizer_join_limit_pref_ratio=10;
--echo # t1 is first, key=col1 produces ordering, no filesort or temporary:
eval $query;
set @trace=(select trace from information_schema.optimizer_trace);
--source include/optimizer_trace_no_costs.inc
select json_detailed(json_extract(@trace, '$**.join_limit_shortcut_choice')) as JS;
--echo #
--echo # Query 6: same as Query 1 but let's limit the search depth
--echo #
set @tmp_osd=@@optimizer_search_depth;
set optimizer_search_depth=1;
let $query= explain
select
*
from
t1
join t10 on t1.a=t10.a
join t11 on t1.b=t11.b
order by
t1.col1
limit 10;
set optimizer_join_limit_pref_ratio=default;
--echo # Table t1 is not the first, have to use temporary+filesort:
eval $query;
set optimizer_join_limit_pref_ratio=10;
--echo # t1 is first, key=col1 produces ordering, no filesort or temporary:
eval $query;
set @trace=(select trace from information_schema.optimizer_trace);
--source include/optimizer_trace_no_costs.inc
select json_detailed(json_extract(@trace, '$**.join_limit_shortcut_choice')) as JS;
set optimizer_search_depth=@tmp_osd;
set optimizer_trace=@tmp_os;
--echo # An extra testcase for MDEV-35164 (its main testcase is below).
alter table t1 add unique key(col2);
insert into t10 select * from t10;
insert into t10 select * from t10;
analyze table t10;
--echo # This will not crash and also show that sorting is not done when
--echo # ORDER BY only refers to const table columns:
explain
select
*
from
t1
join t10 on t1.a=t10.a
join t11 on t1.b=t11.b
where
t1.col2=3
order by
t1.col1
limit 10;
drop table t1, t10, t11;
set optimizer_join_limit_pref_ratio=default;
--echo #
--echo # MDEV-35072: Assertion failure with optimizer_join_limit_pref_ratio and 1-table select
--echo #
SET optimizer_join_limit_pref_ratio=1;
CREATE TABLE t1 (c1 INT, INDEX(c1));
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM t1 ORDER BY c1 LIMIT 1;
DROP TABLE t1;
--echo #
--echo # MDEV-35164: optimizer_join_limit_pref_ratio: assertion when the ORDER BY table becomes constant
--echo # Original testcase:
--echo #
SET optimizer_join_limit_pref_ratio=1;
CREATE TABLE t1 (a INT KEY,b INT, KEY(b)) ;
INSERT INTO t1 VALUES (2,NULL);
INSERT INTO t1 VALUES (5,NULL);
SELECT * FROM t1 NATURAL JOIN t1 AS t2 WHERE t1.b=NULL ORDER BY t1.a LIMIT 1;
DROP TABLE t1;
set optimizer_join_limit_pref_ratio=default;
--echo #
--echo # MDEV-35072: Assertion failure with optimizer_join_limit_pref_ratio and 1-table select
--echo #
SET optimizer_join_limit_pref_ratio=1;
CREATE TABLE t1 (c1 INT, INDEX(c1));
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM t1 ORDER BY c1 LIMIT 1;
DROP TABLE t1;
set optimizer_join_limit_pref_ratio=default;
|
