1
by brian
clean slate |
1 |
#
|
2 |
# A simple test of the greedy query optimization algorithm and the switches that |
|
3 |
# control the optimizationprocess. |
|
4 |
#
|
|
5 |
||
6 |
#
|
|
7 |
# Schema |
|
8 |
#
|
|
9 |
--disable_warnings
|
|
10 |
drop table if exists t1,t2,t3,t4,t5,t6,t7; |
|
11 |
--enable_warnings
|
|
12 |
||
13 |
create table t1 ( |
|
14 |
c11 integer,c12 integer,c13 integer,c14 integer,c15 integer,c16 integer, |
|
15 |
primary key (c11) |
|
16 |
);
|
|
17 |
create table t2 ( |
|
18 |
c21 integer,c22 integer,c23 integer,c24 integer,c25 integer,c26 integer |
|
19 |
);
|
|
20 |
create table t3 ( |
|
21 |
c31 integer,c32 integer,c33 integer,c34 integer,c35 integer,c36 integer, |
|
22 |
primary key (c31) |
|
23 |
);
|
|
24 |
create table t4 ( |
|
25 |
c41 integer,c42 integer,c43 integer,c44 integer,c45 integer,c46 integer |
|
26 |
);
|
|
27 |
create table t5 ( |
|
28 |
c51 integer,c52 integer,c53 integer,c54 integer,c55 integer,c56 integer, |
|
29 |
primary key (c51) |
|
30 |
);
|
|
31 |
create table t6 ( |
|
32 |
c61 integer,c62 integer,c63 integer,c64 integer,c65 integer,c66 integer |
|
33 |
);
|
|
34 |
create table t7 ( |
|
35 |
c71 integer,c72 integer,c73 integer,c74 integer,c75 integer,c76 integer, |
|
36 |
primary key (c71) |
|
37 |
);
|
|
38 |
||
39 |
#
|
|
40 |
# Data |
|
41 |
# cardinality(Ti) = cardinality(T(i-1)) + 3 |
|
42 |
#
|
|
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); |
|
46 |
||
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); |
|
53 |
||
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); |
|
63 |
||
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); |
|
76 |
||
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); |
|
92 |
||
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); |
|
111 |
||
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); |
|
133 |
||
134 |
#
|
|
135 |
# The actual test begins here |
|
136 |
#
|
|
137 |
||
138 |
# Check the default values for the optimizer paramters |
|
139 |
||
140 |
select @@optimizer_search_depth; |
|
141 |
select @@optimizer_prune_level; |
|
142 |
||
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) |
|
145 |
#
|
|
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): |
|
148 |
# 3: |
|
149 |
# set optimizer_search_depth=0; - automatic |
|
150 |
# set optimizer_search_depth=1; - min |
|
151 |
# set optimizer_search_depth=62; - max (default) |
|
152 |
# 2: |
|
153 |
# set optimizer_prune_level=0 - exhaustive; |
|
154 |
# set optimizer_prune_level=1 - heuristic; # default |
|
155 |
||
156 |
||
157 |
#
|
|
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. |
|
164 |
#
|
|
165 |
||
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. |
|
169 |
||
170 |
set optimizer_search_depth=63; |
|
171 |
select @@optimizer_search_depth; |
|
172 |
||
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'; |
|
178 |
# 6-table join, star |
|
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'; |
|
188 |
||
189 |
||
190 |
# Test the new optimization procedures |
|
191 |
||
192 |
set optimizer_prune_level=0; |
|
193 |
select @@optimizer_prune_level; |
|
194 |
||
195 |
set optimizer_search_depth=0; |
|
196 |
select @@optimizer_search_depth; |
|
197 |
||
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'; |
|
203 |
# 6-table join, star |
|
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'; |
|
213 |
||
214 |
set optimizer_search_depth=1; |
|
215 |
select @@optimizer_search_depth; |
|
216 |
||
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'; |
|
222 |
# 6-table join, star |
|
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'; |
|
232 |
||
233 |
set optimizer_search_depth=62; |
|
234 |
select @@optimizer_search_depth; |
|
235 |
||
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'; |
|
241 |
# 6-table join, star |
|
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'; |
|
251 |
||
252 |
||
253 |
set optimizer_prune_level=1; |
|
254 |
select @@optimizer_prune_level; |
|
255 |
||
256 |
set optimizer_search_depth=0; |
|
257 |
select @@optimizer_search_depth; |
|
258 |
||
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'; |
|
264 |
# 6-table join, star |
|
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'; |
|
274 |
||
275 |
set optimizer_search_depth=1; |
|
276 |
select @@optimizer_search_depth; |
|
277 |
||
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'; |
|
283 |
# 6-table join, star |
|
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'; |
|
293 |
||
294 |
set optimizer_search_depth=62; |
|
295 |
select @@optimizer_search_depth; |
|
296 |
||
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'; |
|
302 |
# 6-table join, star |
|
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'; |
|
312 |
||
313 |
drop table t1,t2,t3,t4,t5,t6,t7; |