1
drop table if exists t1,t2,t3,t4,t5,t6,t7;
3
c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer,
7
c21 integer,c22 integer,c23 integer,c24 integer,c25 integer,c26 integer
10
c31 integer,c32 integer,c33 integer,c34 integer,c35 integer,c36 integer,
14
c41 integer,c42 integer,c43 integer,c44 integer,c45 integer,c46 integer
17
c51 integer,c52 integer,c53 integer,c54 integer,c55 integer,c56 integer,
21
c61 integer,c62 integer,c63 integer,c64 integer,c65 integer,c66 integer
24
c71 integer,c72 integer,c73 integer,c74 integer,c75 integer,c76 integer,
27
insert into t1 values (1,2,3,4,5,6);
28
insert into t1 values (2,2,3,4,5,6);
29
insert into t1 values (3,2,3,4,5,6);
30
insert into t2 values (1,2,3,4,5,6);
31
insert into t2 values (2,2,3,4,5,6);
32
insert into t2 values (3,2,3,4,5,6);
33
insert into t2 values (4,2,3,4,5,6);
34
insert into t2 values (5,2,3,4,5,6);
35
insert into t2 values (6,2,3,4,5,6);
36
insert into t3 values (1,2,3,4,5,6);
37
insert into t3 values (2,2,3,4,5,6);
38
insert into t3 values (3,2,3,4,5,6);
39
insert into t3 values (4,2,3,4,5,6);
40
insert into t3 values (5,2,3,4,5,6);
41
insert into t3 values (6,2,3,4,5,6);
42
insert into t3 values (7,2,3,4,5,6);
43
insert into t3 values (8,2,3,4,5,6);
44
insert into t3 values (9,2,3,4,5,6);
45
insert into t4 values (1,2,3,4,5,6);
46
insert into t4 values (2,2,3,4,5,6);
47
insert into t4 values (3,2,3,4,5,6);
48
insert into t4 values (4,2,3,4,5,6);
49
insert into t4 values (5,2,3,4,5,6);
50
insert into t4 values (6,2,3,4,5,6);
51
insert into t4 values (7,2,3,4,5,6);
52
insert into t4 values (8,2,3,4,5,6);
53
insert into t4 values (9,2,3,4,5,6);
54
insert into t4 values (10,2,3,4,5,6);
55
insert into t4 values (11,2,3,4,5,6);
56
insert into t4 values (12,2,3,4,5,6);
57
insert into t5 values (1,2,3,4,5,6);
58
insert into t5 values (2,2,3,4,5,6);
59
insert into t5 values (3,2,3,4,5,6);
60
insert into t5 values (4,2,3,4,5,6);
61
insert into t5 values (5,2,3,4,5,6);
62
insert into t5 values (6,2,3,4,5,6);
63
insert into t5 values (7,2,3,4,5,6);
64
insert into t5 values (8,2,3,4,5,6);
65
insert into t5 values (9,2,3,4,5,6);
66
insert into t5 values (10,2,3,4,5,6);
67
insert into t5 values (11,2,3,4,5,6);
68
insert into t5 values (12,2,3,4,5,6);
69
insert into t5 values (13,2,3,4,5,6);
70
insert into t5 values (14,2,3,4,5,6);
71
insert into t5 values (15,2,3,4,5,6);
72
insert into t6 values (1,2,3,4,5,6);
73
insert into t6 values (2,2,3,4,5,6);
74
insert into t6 values (3,2,3,4,5,6);
75
insert into t6 values (4,2,3,4,5,6);
76
insert into t6 values (5,2,3,4,5,6);
77
insert into t6 values (6,2,3,4,5,6);
78
insert into t6 values (7,2,3,4,5,6);
79
insert into t6 values (8,2,3,4,5,6);
80
insert into t6 values (9,2,3,4,5,6);
81
insert into t6 values (10,2,3,4,5,6);
82
insert into t6 values (11,2,3,4,5,6);
83
insert into t6 values (12,2,3,4,5,6);
84
insert into t6 values (13,2,3,4,5,6);
85
insert into t6 values (14,2,3,4,5,6);
86
insert into t6 values (15,2,3,4,5,6);
87
insert into t6 values (16,2,3,4,5,6);
88
insert into t6 values (17,2,3,4,5,6);
89
insert into t6 values (18,2,3,4,5,6);
90
insert into t7 values (1,2,3,4,5,6);
91
insert into t7 values (2,2,3,4,5,6);
92
insert into t7 values (3,2,3,4,5,6);
93
insert into t7 values (4,2,3,4,5,6);
94
insert into t7 values (5,2,3,4,5,6);
95
insert into t7 values (6,2,3,4,5,6);
96
insert into t7 values (7,2,3,4,5,6);
97
insert into t7 values (8,2,3,4,5,6);
98
insert into t7 values (9,2,3,4,5,6);
99
insert into t7 values (10,2,3,4,5,6);
100
insert into t7 values (11,2,3,4,5,6);
101
insert into t7 values (12,2,3,4,5,6);
102
insert into t7 values (13,2,3,4,5,6);
103
insert into t7 values (14,2,3,4,5,6);
104
insert into t7 values (15,2,3,4,5,6);
105
insert into t7 values (16,2,3,4,5,6);
106
insert into t7 values (17,2,3,4,5,6);
107
insert into t7 values (18,2,3,4,5,6);
108
insert into t7 values (19,2,3,4,5,6);
109
insert into t7 values (20,2,3,4,5,6);
110
insert into t7 values (21,2,3,4,5,6);
111
select @@optimizer_search_depth;
112
@@optimizer_search_depth
114
select @@optimizer_prune_level;
115
@@optimizer_prune_level
117
set optimizer_search_depth=63;
119
Warning 1287 'optimizer-search-depth=63' is deprecated and will be removed in a future release. Please use a search depth less than 63 instead
120
select @@optimizer_search_depth;
121
@@optimizer_search_depth
123
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;
124
id select_type table type possible_keys key key_len ref rows Extra
125
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
126
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
127
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
128
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
129
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
130
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
131
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
132
show status like 'Last_query_cost';
134
Last_query_cost 821.837037
135
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;
136
id select_type table type possible_keys key key_len ref rows Extra
137
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
138
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
139
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
140
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
141
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
142
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
143
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
144
show status like 'Last_query_cost';
146
Last_query_cost 821.837037
147
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;
148
id select_type table type possible_keys key key_len ref rows Extra
149
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
150
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
151
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
152
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
153
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
154
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
155
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
156
show status like 'Last_query_cost';
158
Last_query_cost 794.837037
159
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;
160
id select_type table type possible_keys key key_len ref rows Extra
161
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
162
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
163
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
164
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
165
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
166
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
167
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
168
show status like 'Last_query_cost';
170
Last_query_cost 794.837037
171
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;
172
id select_type table type possible_keys key key_len ref rows Extra
173
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
174
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
175
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
176
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
177
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
178
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
179
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
180
show status like 'Last_query_cost';
182
Last_query_cost 794.837037
183
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;
184
id select_type table type possible_keys key key_len ref rows Extra
185
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
186
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
187
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
188
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
189
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
190
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
191
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
192
show status like 'Last_query_cost';
194
Last_query_cost 794.837037
195
set optimizer_prune_level=0;
196
select @@optimizer_prune_level;
197
@@optimizer_prune_level
199
set optimizer_search_depth=0;
200
select @@optimizer_search_depth;
201
@@optimizer_search_depth
203
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;
204
id select_type table type possible_keys key key_len ref rows Extra
205
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
206
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
207
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
208
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
209
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
210
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
211
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
212
show status like 'Last_query_cost';
214
Last_query_cost 821.837037
215
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;
216
id select_type table type possible_keys key key_len ref rows Extra
217
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
218
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
219
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
220
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
221
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
222
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
223
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
224
show status like 'Last_query_cost';
226
Last_query_cost 821.837037
227
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;
228
id select_type table type possible_keys key key_len ref rows Extra
229
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
230
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
231
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
232
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
233
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
234
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
235
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
236
show status like 'Last_query_cost';
238
Last_query_cost 289.418727
239
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;
240
id select_type table type possible_keys key key_len ref rows Extra
241
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
242
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
243
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
244
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
245
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
246
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
247
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
248
show status like 'Last_query_cost';
250
Last_query_cost 289.418727
251
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;
252
id select_type table type possible_keys key key_len ref rows Extra
253
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
254
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
255
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
256
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
257
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
258
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
259
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
260
show status like 'Last_query_cost';
262
Last_query_cost 289.418727
263
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;
264
id select_type table type possible_keys key key_len ref rows Extra
265
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
266
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
267
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
268
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
269
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
270
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
271
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
272
show status like 'Last_query_cost';
274
Last_query_cost 289.418727
275
set optimizer_search_depth=1;
276
select @@optimizer_search_depth;
277
@@optimizer_search_depth
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
id select_type table type possible_keys key key_len ref rows Extra
281
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
282
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
283
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
284
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
285
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
286
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
287
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
288
show status like 'Last_query_cost';
290
Last_query_cost 821.837037
291
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;
292
id select_type table type possible_keys key key_len ref rows Extra
293
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
294
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
295
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
296
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
297
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
298
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
299
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
300
show status like 'Last_query_cost';
302
Last_query_cost 821.837037
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
id select_type table type possible_keys key key_len ref rows Extra
305
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
306
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
307
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
308
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
309
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
310
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
311
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
312
show status like 'Last_query_cost';
314
Last_query_cost 794.837037
315
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;
316
id select_type table type possible_keys key key_len ref rows Extra
317
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
318
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
319
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
320
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
321
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
322
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
323
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
324
show status like 'Last_query_cost';
326
Last_query_cost 794.837037
327
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;
328
id select_type table type possible_keys key key_len ref rows Extra
329
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
330
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
331
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
332
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
333
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
334
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
335
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
336
show status like 'Last_query_cost';
338
Last_query_cost 794.837037
339
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;
340
id select_type table type possible_keys key key_len ref rows Extra
341
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
342
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
343
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
344
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
345
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
346
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
347
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
348
show status like 'Last_query_cost';
350
Last_query_cost 794.837037
351
set optimizer_search_depth=62;
352
select @@optimizer_search_depth;
353
@@optimizer_search_depth
355
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;
356
id select_type table type possible_keys key key_len ref rows Extra
357
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
358
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
359
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
360
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
361
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
362
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
363
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
364
show status like 'Last_query_cost';
366
Last_query_cost 821.837037
367
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;
368
id select_type table type possible_keys key key_len ref rows Extra
369
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
370
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
371
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
372
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
373
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
374
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
375
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
376
show status like 'Last_query_cost';
378
Last_query_cost 821.837037
379
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;
380
id select_type table type possible_keys key key_len ref rows Extra
381
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
382
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
383
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
384
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
385
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
386
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
387
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
388
show status like 'Last_query_cost';
390
Last_query_cost 289.418727
391
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;
392
id select_type table type possible_keys key key_len ref rows Extra
393
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
394
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using join buffer
395
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
396
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
397
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
398
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
399
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
400
show status like 'Last_query_cost';
402
Last_query_cost 289.418727
403
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;
404
id select_type table type possible_keys key key_len ref rows Extra
405
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
406
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
407
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
408
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
409
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
410
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
411
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
412
show status like 'Last_query_cost';
414
Last_query_cost 289.418727
415
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;
416
id select_type table type possible_keys key key_len ref rows Extra
417
1 SIMPLE t2 ALL NULL NULL NULL NULL 6
418
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
419
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.c21 1 Using where
420
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
421
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
422
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
423
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
424
show status like 'Last_query_cost';
426
Last_query_cost 289.418727
427
set optimizer_prune_level=1;
428
select @@optimizer_prune_level;
429
@@optimizer_prune_level
431
set optimizer_search_depth=0;
432
select @@optimizer_search_depth;
433
@@optimizer_search_depth
435
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;
436
id select_type table type possible_keys key key_len ref rows Extra
437
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
438
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
439
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
440
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
441
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
442
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
443
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
444
show status like 'Last_query_cost';
446
Last_query_cost 821.837037
447
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;
448
id select_type table type possible_keys key key_len ref rows Extra
449
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
450
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
451
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
452
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
453
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
454
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
455
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
456
show status like 'Last_query_cost';
458
Last_query_cost 821.837037
459
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;
460
id select_type table type possible_keys key key_len ref rows Extra
461
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
462
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
463
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
464
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
465
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
466
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
467
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
468
show status like 'Last_query_cost';
470
Last_query_cost 794.837037
471
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;
472
id select_type table type possible_keys key key_len ref rows Extra
473
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
474
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
475
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
476
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
477
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
478
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
479
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
480
show status like 'Last_query_cost';
482
Last_query_cost 794.837037
483
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;
484
id select_type table type possible_keys key key_len ref rows Extra
485
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
486
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
487
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
488
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
489
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
490
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
491
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
492
show status like 'Last_query_cost';
494
Last_query_cost 794.837037
495
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;
496
id select_type table type possible_keys key key_len ref rows Extra
497
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
498
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
499
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
500
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
501
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
502
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
503
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
504
show status like 'Last_query_cost';
506
Last_query_cost 794.837037
507
set optimizer_search_depth=1;
508
select @@optimizer_search_depth;
509
@@optimizer_search_depth
511
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;
512
id select_type table type possible_keys key key_len ref rows Extra
513
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
514
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
515
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
516
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
517
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
518
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
519
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
520
show status like 'Last_query_cost';
522
Last_query_cost 821.837037
523
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;
524
id select_type table type possible_keys key key_len ref rows Extra
525
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
526
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
527
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
528
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
529
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
530
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
531
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
532
show status like 'Last_query_cost';
534
Last_query_cost 821.837037
535
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;
536
id select_type table type possible_keys key key_len ref rows Extra
537
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
538
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
539
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
540
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
541
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
542
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
543
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
544
show status like 'Last_query_cost';
546
Last_query_cost 794.837037
547
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;
548
id select_type table type possible_keys key key_len ref rows Extra
549
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
550
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
551
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
552
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
553
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
554
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
555
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
556
show status like 'Last_query_cost';
558
Last_query_cost 794.837037
559
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;
560
id select_type table type possible_keys key key_len ref rows Extra
561
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
562
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
563
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
564
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
565
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
566
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
567
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
568
show status like 'Last_query_cost';
570
Last_query_cost 794.837037
571
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;
572
id select_type table type possible_keys key key_len ref rows Extra
573
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
574
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1
575
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
576
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
577
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
578
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
579
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
580
show status like 'Last_query_cost';
582
Last_query_cost 794.837037
583
set optimizer_search_depth=62;
584
select @@optimizer_search_depth;
585
@@optimizer_search_depth
587
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;
588
id select_type table type possible_keys key key_len ref rows Extra
589
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
590
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
591
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
592
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
593
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
594
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
595
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
596
show status like 'Last_query_cost';
598
Last_query_cost 821.837037
599
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;
600
id select_type table type possible_keys key key_len ref rows Extra
601
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
602
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
603
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t2.c22 1
604
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
605
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t4.c42 1
606
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
607
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t6.c62 1 Using index
608
show status like 'Last_query_cost';
610
Last_query_cost 821.837037
611
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;
612
id select_type table type possible_keys key key_len ref rows Extra
613
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
614
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
615
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
616
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
617
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
618
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
619
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
620
show status like 'Last_query_cost';
622
Last_query_cost 794.837037
623
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;
624
id select_type table type possible_keys key key_len ref rows Extra
625
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
626
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
627
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using index
628
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
629
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using index
630
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
631
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using index
632
show status like 'Last_query_cost';
634
Last_query_cost 794.837037
635
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;
636
id select_type table type possible_keys key key_len ref rows Extra
637
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
638
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
639
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
640
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
641
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
642
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
643
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
644
show status like 'Last_query_cost';
646
Last_query_cost 794.837037
647
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;
648
id select_type table type possible_keys key key_len ref rows Extra
649
1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 3
650
1 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer
651
1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 test.t1.c12 1 Using where
652
1 SIMPLE t4 ALL NULL NULL NULL NULL 12 Using where; Using join buffer
653
1 SIMPLE t5 eq_ref PRIMARY PRIMARY 4 test.t1.c14 1 Using where
654
1 SIMPLE t6 ALL NULL NULL NULL NULL 18 Using where; Using join buffer
655
1 SIMPLE t7 eq_ref PRIMARY PRIMARY 4 test.t1.c16 1 Using where
656
show status like 'Last_query_cost';
658
Last_query_cost 794.837037
659
drop table t1,t2,t3,t4,t5,t6,t7;
660
CREATE TABLE t1 (a int, b int, d int, i int);
661
INSERT INTO t1 VALUES (1,1,1,1);
662
CREATE TABLE t2 (b int, c int, j int);
663
INSERT INTO t2 VALUES (1,1,1);
664
CREATE TABLE t2_1 (j int);
665
INSERT INTO t2_1 VALUES (1);
666
CREATE TABLE t3 (c int, f int);
667
INSERT INTO t3 VALUES (1,1);
668
CREATE TABLE t3_1 (f int);
669
INSERT INTO t3_1 VALUES (1);
670
CREATE TABLE t4 (d int, e int, k int);
671
INSERT INTO t4 VALUES (1,1,1);
672
CREATE TABLE t4_1 (k int);
673
INSERT INTO t4_1 VALUES (1);
674
CREATE TABLE t5 (g int, d int, h int, l int);
675
INSERT INTO t5 VALUES (1,1,1,1);
676
CREATE TABLE t5_1 (l int);
677
INSERT INTO t5_1 VALUES (1);
678
SET optimizer_search_depth = 3;
682
t2 JOIN t3 ON t3.c = t2.c
685
t4 JOIN t5 ON t5.d = t4.d
693
t2 LEFT JOIN (t3 JOIN t3_1 ON t3.f = t3_1.f) ON t3.c = t2.c
696
t4 JOIN t5 ON t5.d = t4.d
704
(t2 JOIN t2_1 ON t2.j = t2_1.j) JOIN t3 ON t3.c = t2.c
707
t4 JOIN t5 ON t5.d = t4.d
715
t2 JOIN t3 ON t3.c = t2.c
718
(t4 JOIN t4_1 ON t4.k = t4_1.k) LEFT JOIN t5 ON t5.d = t4.d
726
t2 JOIN t3 ON t3.c = t2.c
729
t4 LEFT JOIN (t5 JOIN t5_1 ON t5.l = t5_1.l) ON t5.d = t4.d
734
SET optimizer_search_depth = DEFAULT;
735
DROP TABLE t1,t2,t2_1,t3,t3_1,t4,t4_1,t5,t5_1;