2
# A simple test of the greedy query optimization algorithm and the switches that
3
# control the optimizationprocess.
10
drop table if exists t1,t2,t3,t4,t5,t6,t7;
14
c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer,
18
c21 integer,c22 integer,c23 integer,c24 integer,c25 integer,c26 integer
21
c31 integer,c32 integer,c33 integer,c34 integer,c35 integer,c36 integer,
25
c41 integer,c42 integer,c43 integer,c44 integer,c45 integer,c46 integer
28
c51 integer,c52 integer,c53 integer,c54 integer,c55 integer,c56 integer,
32
c61 integer,c62 integer,c63 integer,c64 integer,c65 integer,c66 integer
35
c71 integer,c72 integer,c73 integer,c74 integer,c75 integer,c76 integer,
41
# cardinality(Ti) = cardinality(T(i-1)) + 3
43
insert into t1 values (1,2,3,4,5,6);
44
insert into t1 values (2,2,3,4,5,6);
45
insert into t1 values (3,2,3,4,5,6);
47
insert into t2 values (1,2,3,4,5,6);
48
insert into t2 values (2,2,3,4,5,6);
49
insert into t2 values (3,2,3,4,5,6);
50
insert into t2 values (4,2,3,4,5,6);
51
insert into t2 values (5,2,3,4,5,6);
52
insert into t2 values (6,2,3,4,5,6);
54
insert into t3 values (1,2,3,4,5,6);
55
insert into t3 values (2,2,3,4,5,6);
56
insert into t3 values (3,2,3,4,5,6);
57
insert into t3 values (4,2,3,4,5,6);
58
insert into t3 values (5,2,3,4,5,6);
59
insert into t3 values (6,2,3,4,5,6);
60
insert into t3 values (7,2,3,4,5,6);
61
insert into t3 values (8,2,3,4,5,6);
62
insert into t3 values (9,2,3,4,5,6);
64
insert into t4 values (1,2,3,4,5,6);
65
insert into t4 values (2,2,3,4,5,6);
66
insert into t4 values (3,2,3,4,5,6);
67
insert into t4 values (4,2,3,4,5,6);
68
insert into t4 values (5,2,3,4,5,6);
69
insert into t4 values (6,2,3,4,5,6);
70
insert into t4 values (7,2,3,4,5,6);
71
insert into t4 values (8,2,3,4,5,6);
72
insert into t4 values (9,2,3,4,5,6);
73
insert into t4 values (10,2,3,4,5,6);
74
insert into t4 values (11,2,3,4,5,6);
75
insert into t4 values (12,2,3,4,5,6);
77
insert into t5 values (1,2,3,4,5,6);
78
insert into t5 values (2,2,3,4,5,6);
79
insert into t5 values (3,2,3,4,5,6);
80
insert into t5 values (4,2,3,4,5,6);
81
insert into t5 values (5,2,3,4,5,6);
82
insert into t5 values (6,2,3,4,5,6);
83
insert into t5 values (7,2,3,4,5,6);
84
insert into t5 values (8,2,3,4,5,6);
85
insert into t5 values (9,2,3,4,5,6);
86
insert into t5 values (10,2,3,4,5,6);
87
insert into t5 values (11,2,3,4,5,6);
88
insert into t5 values (12,2,3,4,5,6);
89
insert into t5 values (13,2,3,4,5,6);
90
insert into t5 values (14,2,3,4,5,6);
91
insert into t5 values (15,2,3,4,5,6);
93
insert into t6 values (1,2,3,4,5,6);
94
insert into t6 values (2,2,3,4,5,6);
95
insert into t6 values (3,2,3,4,5,6);
96
insert into t6 values (4,2,3,4,5,6);
97
insert into t6 values (5,2,3,4,5,6);
98
insert into t6 values (6,2,3,4,5,6);
99
insert into t6 values (7,2,3,4,5,6);
100
insert into t6 values (8,2,3,4,5,6);
101
insert into t6 values (9,2,3,4,5,6);
102
insert into t6 values (10,2,3,4,5,6);
103
insert into t6 values (11,2,3,4,5,6);
104
insert into t6 values (12,2,3,4,5,6);
105
insert into t6 values (13,2,3,4,5,6);
106
insert into t6 values (14,2,3,4,5,6);
107
insert into t6 values (15,2,3,4,5,6);
108
insert into t6 values (16,2,3,4,5,6);
109
insert into t6 values (17,2,3,4,5,6);
110
insert into t6 values (18,2,3,4,5,6);
112
insert into t7 values (1,2,3,4,5,6);
113
insert into t7 values (2,2,3,4,5,6);
114
insert into t7 values (3,2,3,4,5,6);
115
insert into t7 values (4,2,3,4,5,6);
116
insert into t7 values (5,2,3,4,5,6);
117
insert into t7 values (6,2,3,4,5,6);
118
insert into t7 values (7,2,3,4,5,6);
119
insert into t7 values (8,2,3,4,5,6);
120
insert into t7 values (9,2,3,4,5,6);
121
insert into t7 values (10,2,3,4,5,6);
122
insert into t7 values (11,2,3,4,5,6);
123
insert into t7 values (12,2,3,4,5,6);
124
insert into t7 values (13,2,3,4,5,6);
125
insert into t7 values (14,2,3,4,5,6);
126
insert into t7 values (15,2,3,4,5,6);
127
insert into t7 values (16,2,3,4,5,6);
128
insert into t7 values (17,2,3,4,5,6);
129
insert into t7 values (18,2,3,4,5,6);
130
insert into t7 values (19,2,3,4,5,6);
131
insert into t7 values (20,2,3,4,5,6);
132
insert into t7 values (21,2,3,4,5,6);
135
# The actual test begins here
138
# Check the default values for the optimizer paramters
140
select @@optimizer_search_depth;
141
select @@optimizer_prune_level;
143
# This value swithes back to the old implementation of 'find_best()'
144
# set optimizer_search_depth=63; - old (independent of the optimizer_prune_level)
146
# These are the values for the parameters that control the greedy optimizer
147
# (total 6 combinations - 3 for optimizer_search_depth, 2 for optimizer_prune_level):
149
# set optimizer_search_depth=0; - automatic
150
# set optimizer_search_depth=1; - min
151
# set optimizer_search_depth=62; - max (default)
153
# set optimizer_prune_level=0 - exhaustive;
154
# set optimizer_prune_level=1 - heuristic; # default
158
# Compile several queries with all combinations of the query
159
# optimizer parameters. Each test query has two variants, where
160
# in the second variant the tables in the FROM clause are in
161
# inverse order to the tables in the first variant.
162
# Due to pre-sorting of tables before compilation, there should
163
# be no difference in the plans for each two such query variants.
166
# First, for reference compile the test queries with the 'old' optimization
167
# procedure 'find_best'. Notice that 'find_best' does not depend on the
168
# choice of heuristic.
170
set optimizer_search_depth=63;
171
select @@optimizer_search_depth;
173
# 6-table join, chain
174
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
175
show status like 'Last_query_cost';
176
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
177
show status like 'Last_query_cost';
179
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
180
show status like 'Last_query_cost';
181
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
182
show status like 'Last_query_cost';
183
# 6-table join, clique
184
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
185
show status like 'Last_query_cost';
186
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
187
show status like 'Last_query_cost';
190
# Test the new optimization procedures
192
set optimizer_prune_level=0;
193
select @@optimizer_prune_level;
195
set optimizer_search_depth=0;
196
select @@optimizer_search_depth;
198
# 6-table join, chain
199
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
200
show status like 'Last_query_cost';
201
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
202
show status like 'Last_query_cost';
204
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
205
show status like 'Last_query_cost';
206
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
207
show status like 'Last_query_cost';
208
# 6-table join, clique
209
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
210
show status like 'Last_query_cost';
211
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
212
show status like 'Last_query_cost';
214
set optimizer_search_depth=1;
215
select @@optimizer_search_depth;
217
# 6-table join, chain
218
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
219
show status like 'Last_query_cost';
220
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
221
show status like 'Last_query_cost';
223
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
224
show status like 'Last_query_cost';
225
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
226
show status like 'Last_query_cost';
227
# 6-table join, clique
228
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
229
show status like 'Last_query_cost';
230
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
231
show status like 'Last_query_cost';
233
set optimizer_search_depth=62;
234
select @@optimizer_search_depth;
236
# 6-table join, chain
237
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
238
show status like 'Last_query_cost';
239
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
240
show status like 'Last_query_cost';
242
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
243
show status like 'Last_query_cost';
244
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
245
show status like 'Last_query_cost';
246
# 6-table join, clique
247
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
248
show status like 'Last_query_cost';
249
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
250
show status like 'Last_query_cost';
253
set optimizer_prune_level=1;
254
select @@optimizer_prune_level;
256
set optimizer_search_depth=0;
257
select @@optimizer_search_depth;
259
# 6-table join, chain
260
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
261
show status like 'Last_query_cost';
262
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
263
show status like 'Last_query_cost';
265
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
266
show status like 'Last_query_cost';
267
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
268
show status like 'Last_query_cost';
269
# 6-table join, clique
270
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
271
show status like 'Last_query_cost';
272
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
273
show status like 'Last_query_cost';
275
set optimizer_search_depth=1;
276
select @@optimizer_search_depth;
278
# 6-table join, chain
279
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
280
show status like 'Last_query_cost';
281
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
282
show status like 'Last_query_cost';
284
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
285
show status like 'Last_query_cost';
286
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
287
show status like 'Last_query_cost';
288
# 6-table join, clique
289
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
290
show status like 'Last_query_cost';
291
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
292
show status like 'Last_query_cost';
294
set optimizer_search_depth=62;
295
select @@optimizer_search_depth;
297
# 6-table join, chain
298
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
299
show status like 'Last_query_cost';
300
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c12 = t2.c21 and t2.c22 = t3.c31 and t3.c32 = t4.c41 and t4.c42 = t5.c51 and t5.c52 = t6.c61 and t6.c62 = t7.c71;
301
show status like 'Last_query_cost';
303
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
304
show status like 'Last_query_cost';
305
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71;
306
show status like 'Last_query_cost';
307
# 6-table join, clique
308
explain select t1.c11 from t1, t2, t3, t4, t5, t6, t7 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
309
show status like 'Last_query_cost';
310
explain select t1.c11 from t7, t6, t5, t4, t3, t2, t1 where t1.c11 = t2.c21 and t1.c12 = t3.c31 and t1.c13 = t4.c41 and t1.c14 = t5.c51 and t1.c15 = t6.c61 and t1.c16 = t7.c71 and t2.c22 = t3.c32 and t2.c23 = t4.c42 and t2.c24 = t5.c52 and t2.c25 = t6.c62 and t2.c26 = t7.c72 and t3.c33 = t4.c43 and t3.c34 = t5.c53 and t3.c35 = t6.c63 and t3.c36 = t7.c73 and t4.c42 = t5.c54 and t4.c43 = t6.c64 and t4.c44 = t7.c74 and t5.c52 = t6.c65 and t5.c53 = t7.c75 and t6.c62 = t7.c76;
311
show status like 'Last_query_cost';
313
drop table t1,t2,t3,t4,t5,t6,t7;
added
removed
mysql-test/t/greedy_optimizer.test
