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
|
UPDATE mysql.engine_cost
SET cost_value = 1.0
WHERE cost_name = 'memory_block_read_cost';
FLUSH OPTIMIZER_COSTS;
#
# TEST 1
# Greedy search iteration test for 16-way join: star schema
#
# Creation of 16 tables hidden
#
SET SESSION optimizer_search_depth = 25;
FLUSH STATUS;
#
# 16-way join - all 15 fact tables joined on column with key
#
EXPLAIN SELECT *
FROM vehicles
JOIN models ON vehicles.model_id = models.id_pk
JOIN subtypes ON vehicles.subtype_id = subtypes.id_pk
JOIN colors ON vehicles.color_id = colors.id_pk
JOIN heating ON vehicles.heating_id = heating.id_pk
JOIN windows ON vehicles.window_id = windows.id_pk
JOIN fuels ON vehicles.fuel_id = fuels.id_pk
JOIN transmissions ON vehicles.transmission_id = transmissions.id_pk
JOIN steerings ON vehicles.steering_id = steerings.id_pk
JOIN interiors ON vehicles.interior_id = interiors.id_pk
JOIN drives ON vehicles.drive_id = drives.id_pk
JOIN wheels ON vehicles.wheels_id = wheels.id_pk
JOIN engine ON vehicles.engine_id = engine.id_pk
JOIN price_ranges ON vehicles.price_range_id = price_ranges.id_pk
JOIN countries ON vehicles.assembled_in = countries.id_pk
JOIN brands ON models.brand_id = brands.id_pk;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE subtypes NULL ALL PRIMARY NULL NULL NULL 10 100.00 NULL
1 SIMPLE vehicles NULL ALL NULL NULL NULL NULL 80 10.00 Using where; Using join buffer (hash join)
1 SIMPLE heating NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.heating_id 1 100.00 NULL
1 SIMPLE windows NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.window_id 1 100.00 NULL
1 SIMPLE fuels NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.fuel_id 1 100.00 NULL
1 SIMPLE transmissions NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.transmission_id 1 100.00 NULL
1 SIMPLE steerings NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.steering_id 1 100.00 NULL
1 SIMPLE drives NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.drive_id 1 100.00 NULL
1 SIMPLE wheels NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.wheels_id 1 100.00 NULL
1 SIMPLE engine NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.engine_id 1 100.00 NULL
1 SIMPLE price_ranges NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.price_range_id 1 100.00 NULL
1 SIMPLE colors NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.color_id 1 100.00 NULL
1 SIMPLE interiors NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.interior_id 1 100.00 NULL
1 SIMPLE countries NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.assembled_in 1 100.00 NULL
1 SIMPLE models NULL eq_ref PRIMARY,brand_id PRIMARY 4 test.vehicles.model_id 1 100.00 Using where
1 SIMPLE brands NULL eq_ref PRIMARY PRIMARY 4 test.models.brand_id 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`vehicles`.`id` AS `id`,`test`.`vehicles`.`model_id` AS `model_id`,`test`.`vehicles`.`subtype_id` AS `subtype_id`,`test`.`vehicles`.`color_id` AS `color_id`,`test`.`vehicles`.`heating_id` AS `heating_id`,`test`.`vehicles`.`window_id` AS `window_id`,`test`.`vehicles`.`fuel_id` AS `fuel_id`,`test`.`vehicles`.`transmission_id` AS `transmission_id`,`test`.`vehicles`.`steering_id` AS `steering_id`,`test`.`vehicles`.`interior_id` AS `interior_id`,`test`.`vehicles`.`drive_id` AS `drive_id`,`test`.`vehicles`.`price_range_id` AS `price_range_id`,`test`.`vehicles`.`assembled_in` AS `assembled_in`,`test`.`vehicles`.`engine_id` AS `engine_id`,`test`.`vehicles`.`wheels_id` AS `wheels_id`,`test`.`models`.`id_pk` AS `id_pk`,`test`.`models`.`id_nokey` AS `id_nokey`,`test`.`models`.`brand_id` AS `brand_id`,`test`.`models`.`name` AS `name`,`test`.`subtypes`.`id_pk` AS `id_pk`,`test`.`subtypes`.`id_nokey` AS `id_nokey`,`test`.`subtypes`.`name` AS `name`,`test`.`colors`.`id_pk` AS `id_pk`,`test`.`colors`.`id_nokey` AS `id_nokey`,`test`.`colors`.`name` AS `name`,`test`.`heating`.`id_pk` AS `id_pk`,`test`.`heating`.`id_nokey` AS `id_nokey`,`test`.`heating`.`name` AS `name`,`test`.`windows`.`id_pk` AS `id_pk`,`test`.`windows`.`id_nokey` AS `id_nokey`,`test`.`windows`.`name` AS `name`,`test`.`fuels`.`id_pk` AS `id_pk`,`test`.`fuels`.`id_nokey` AS `id_nokey`,`test`.`fuels`.`name` AS `name`,`test`.`transmissions`.`id_pk` AS `id_pk`,`test`.`transmissions`.`id_nokey` AS `id_nokey`,`test`.`transmissions`.`name` AS `name`,`test`.`steerings`.`id_pk` AS `id_pk`,`test`.`steerings`.`id_nokey` AS `id_nokey`,`test`.`steerings`.`name` AS `name`,`test`.`interiors`.`id_pk` AS `id_pk`,`test`.`interiors`.`id_nokey` AS `id_nokey`,`test`.`interiors`.`name` AS `name`,`test`.`drives`.`id_pk` AS `id_pk`,`test`.`drives`.`id_nokey` AS `id_nokey`,`test`.`drives`.`name` AS `name`,`test`.`wheels`.`id_pk` AS `id_pk`,`test`.`wheels`.`id_nokey` AS `id_nokey`,`test`.`wheels`.`name` AS `name`,`test`.`engine`.`id_pk` AS `id_pk`,`test`.`engine`.`id_nokey` AS `id_nokey`,`test`.`engine`.`name` AS `name`,`test`.`price_ranges`.`id_pk` AS `id_pk`,`test`.`price_ranges`.`id_nokey` AS `id_nokey`,`test`.`price_ranges`.`name` AS `name`,`test`.`countries`.`id_pk` AS `id_pk`,`test`.`countries`.`id_nokey` AS `id_nokey`,`test`.`countries`.`name` AS `name`,`test`.`brands`.`id_pk` AS `id_pk`,`test`.`brands`.`id_nokey` AS `id_nokey`,`test`.`brands`.`name` AS `name` from `test`.`vehicles` join `test`.`models` join `test`.`subtypes` join `test`.`colors` join `test`.`heating` join `test`.`windows` join `test`.`fuels` join `test`.`transmissions` join `test`.`steerings` join `test`.`interiors` join `test`.`drives` join `test`.`wheels` join `test`.`engine` join `test`.`price_ranges` join `test`.`countries` join `test`.`brands` where ((`test`.`models`.`id_pk` = `test`.`vehicles`.`model_id`) and (`test`.`vehicles`.`subtype_id` = `test`.`subtypes`.`id_pk`) and (`test`.`colors`.`id_pk` = `test`.`vehicles`.`color_id`) and (`test`.`heating`.`id_pk` = `test`.`vehicles`.`heating_id`) and (`test`.`windows`.`id_pk` = `test`.`vehicles`.`window_id`) and (`test`.`fuels`.`id_pk` = `test`.`vehicles`.`fuel_id`) and (`test`.`transmissions`.`id_pk` = `test`.`vehicles`.`transmission_id`) and (`test`.`steerings`.`id_pk` = `test`.`vehicles`.`steering_id`) and (`test`.`interiors`.`id_pk` = `test`.`vehicles`.`interior_id`) and (`test`.`drives`.`id_pk` = `test`.`vehicles`.`drive_id`) and (`test`.`wheels`.`id_pk` = `test`.`vehicles`.`wheels_id`) and (`test`.`engine`.`id_pk` = `test`.`vehicles`.`engine_id`) and (`test`.`price_ranges`.`id_pk` = `test`.`vehicles`.`price_range_id`) and (`test`.`countries`.`id_pk` = `test`.`vehicles`.`assembled_in`) and (`test`.`brands`.`id_pk` = `test`.`models`.`brand_id`))
### Partial_plans: 338288
FLUSH STATUS;
#
# 16-way join - 10 fact tables joined on column with key and
# 5 fact tables joined on column without key
#
EXPLAIN SELECT *
FROM vehicles
JOIN models ON vehicles.model_id = models.id_nokey
JOIN subtypes ON vehicles.subtype_id = subtypes.id_pk
JOIN colors ON vehicles.color_id = colors.id_pk
JOIN heating ON vehicles.heating_id = heating.id_nokey
JOIN windows ON vehicles.window_id = windows.id_pk
JOIN fuels ON vehicles.fuel_id = fuels.id_pk
JOIN transmissions ON vehicles.transmission_id = transmissions.id_nokey
JOIN steerings ON vehicles.steering_id = steerings.id_pk
JOIN interiors ON vehicles.interior_id = interiors.id_pk
JOIN drives ON vehicles.drive_id = drives.id_pk
JOIN wheels ON vehicles.wheels_id = wheels.id_nokey
JOIN engine ON vehicles.engine_id = engine.id_pk
JOIN price_ranges ON vehicles.price_range_id = price_ranges.id_pk
JOIN countries ON vehicles.assembled_in = countries.id_pk
JOIN brands ON models.brand_id = brands.id_nokey;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE brands NULL ALL NULL NULL NULL NULL 7 100.00 Using where
1 SIMPLE models NULL ref brand_id brand_id 5 test.brands.id_nokey 5 100.00 NULL
1 SIMPLE vehicles NULL ALL NULL NULL NULL NULL 80 10.00 Using where; Using join buffer (hash join)
1 SIMPLE heating NULL ALL NULL NULL NULL NULL 10 10.00 Using where; Using join buffer (hash join)
1 SIMPLE windows NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.window_id 1 100.00 NULL
1 SIMPLE fuels NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.fuel_id 1 100.00 NULL
1 SIMPLE steerings NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.steering_id 1 100.00 NULL
1 SIMPLE drives NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.drive_id 1 100.00 NULL
1 SIMPLE engine NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.engine_id 1 100.00 NULL
1 SIMPLE price_ranges NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.price_range_id 1 100.00 NULL
1 SIMPLE interiors NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.interior_id 1 100.00 NULL
1 SIMPLE countries NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.assembled_in 1 100.00 NULL
1 SIMPLE subtypes NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.subtype_id 1 100.00 NULL
1 SIMPLE colors NULL eq_ref PRIMARY PRIMARY 4 test.vehicles.color_id 1 100.00 NULL
1 SIMPLE transmissions NULL ALL NULL NULL NULL NULL 10 10.00 Using where; Using join buffer (hash join)
1 SIMPLE wheels NULL ALL NULL NULL NULL NULL 10 10.00 Using where; Using join buffer (hash join)
Warnings:
Note 1003 /* select#1 */ select `test`.`vehicles`.`id` AS `id`,`test`.`vehicles`.`model_id` AS `model_id`,`test`.`vehicles`.`subtype_id` AS `subtype_id`,`test`.`vehicles`.`color_id` AS `color_id`,`test`.`vehicles`.`heating_id` AS `heating_id`,`test`.`vehicles`.`window_id` AS `window_id`,`test`.`vehicles`.`fuel_id` AS `fuel_id`,`test`.`vehicles`.`transmission_id` AS `transmission_id`,`test`.`vehicles`.`steering_id` AS `steering_id`,`test`.`vehicles`.`interior_id` AS `interior_id`,`test`.`vehicles`.`drive_id` AS `drive_id`,`test`.`vehicles`.`price_range_id` AS `price_range_id`,`test`.`vehicles`.`assembled_in` AS `assembled_in`,`test`.`vehicles`.`engine_id` AS `engine_id`,`test`.`vehicles`.`wheels_id` AS `wheels_id`,`test`.`models`.`id_pk` AS `id_pk`,`test`.`models`.`id_nokey` AS `id_nokey`,`test`.`models`.`brand_id` AS `brand_id`,`test`.`models`.`name` AS `name`,`test`.`subtypes`.`id_pk` AS `id_pk`,`test`.`subtypes`.`id_nokey` AS `id_nokey`,`test`.`subtypes`.`name` AS `name`,`test`.`colors`.`id_pk` AS `id_pk`,`test`.`colors`.`id_nokey` AS `id_nokey`,`test`.`colors`.`name` AS `name`,`test`.`heating`.`id_pk` AS `id_pk`,`test`.`heating`.`id_nokey` AS `id_nokey`,`test`.`heating`.`name` AS `name`,`test`.`windows`.`id_pk` AS `id_pk`,`test`.`windows`.`id_nokey` AS `id_nokey`,`test`.`windows`.`name` AS `name`,`test`.`fuels`.`id_pk` AS `id_pk`,`test`.`fuels`.`id_nokey` AS `id_nokey`,`test`.`fuels`.`name` AS `name`,`test`.`transmissions`.`id_pk` AS `id_pk`,`test`.`transmissions`.`id_nokey` AS `id_nokey`,`test`.`transmissions`.`name` AS `name`,`test`.`steerings`.`id_pk` AS `id_pk`,`test`.`steerings`.`id_nokey` AS `id_nokey`,`test`.`steerings`.`name` AS `name`,`test`.`interiors`.`id_pk` AS `id_pk`,`test`.`interiors`.`id_nokey` AS `id_nokey`,`test`.`interiors`.`name` AS `name`,`test`.`drives`.`id_pk` AS `id_pk`,`test`.`drives`.`id_nokey` AS `id_nokey`,`test`.`drives`.`name` AS `name`,`test`.`wheels`.`id_pk` AS `id_pk`,`test`.`wheels`.`id_nokey` AS `id_nokey`,`test`.`wheels`.`name` AS `name`,`test`.`engine`.`id_pk` AS `id_pk`,`test`.`engine`.`id_nokey` AS `id_nokey`,`test`.`engine`.`name` AS `name`,`test`.`price_ranges`.`id_pk` AS `id_pk`,`test`.`price_ranges`.`id_nokey` AS `id_nokey`,`test`.`price_ranges`.`name` AS `name`,`test`.`countries`.`id_pk` AS `id_pk`,`test`.`countries`.`id_nokey` AS `id_nokey`,`test`.`countries`.`name` AS `name`,`test`.`brands`.`id_pk` AS `id_pk`,`test`.`brands`.`id_nokey` AS `id_nokey`,`test`.`brands`.`name` AS `name` from `test`.`vehicles` join `test`.`models` join `test`.`subtypes` join `test`.`colors` join `test`.`heating` join `test`.`windows` join `test`.`fuels` join `test`.`transmissions` join `test`.`steerings` join `test`.`interiors` join `test`.`drives` join `test`.`wheels` join `test`.`engine` join `test`.`price_ranges` join `test`.`countries` join `test`.`brands` where ((`test`.`vehicles`.`model_id` = `test`.`models`.`id_nokey`) and (`test`.`subtypes`.`id_pk` = `test`.`vehicles`.`subtype_id`) and (`test`.`colors`.`id_pk` = `test`.`vehicles`.`color_id`) and (`test`.`heating`.`id_nokey` = `test`.`vehicles`.`heating_id`) and (`test`.`windows`.`id_pk` = `test`.`vehicles`.`window_id`) and (`test`.`fuels`.`id_pk` = `test`.`vehicles`.`fuel_id`) and (`test`.`transmissions`.`id_nokey` = `test`.`vehicles`.`transmission_id`) and (`test`.`steerings`.`id_pk` = `test`.`vehicles`.`steering_id`) and (`test`.`interiors`.`id_pk` = `test`.`vehicles`.`interior_id`) and (`test`.`drives`.`id_pk` = `test`.`vehicles`.`drive_id`) and (`test`.`wheels`.`id_nokey` = `test`.`vehicles`.`wheels_id`) and (`test`.`engine`.`id_pk` = `test`.`vehicles`.`engine_id`) and (`test`.`price_ranges`.`id_pk` = `test`.`vehicles`.`price_range_id`) and (`test`.`countries`.`id_pk` = `test`.`vehicles`.`assembled_in`) and (`test`.`models`.`brand_id` = `test`.`brands`.`id_nokey`))
### Partial_plans: 3296
FLUSH STATUS;
select @@optimizer_search_depth;
@@optimizer_search_depth
25
select @@optimizer_prune_level;
@@optimizer_prune_level
1
DROP TABLE vehicles, models, subtypes, colors, heating, windows,
fuels, transmissions, steerings, interiors, drives,
price_ranges, countries, brands, wheels, engine;
#
# TEST 2
# Greedy search iteration test for chain of tables
#
#
# Chain test a: colidx):(pk,colidx):(pk,colidx)
#
EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.pk JOIN t10_2 ON t100_1.colidx = t10_2.pk JOIN t100_2 ON t10_2.colidx = t100_2.pk JOIN t10_3 ON t100_2.colidx = t10_3.pk JOIN t100_3 ON t10_3.colidx = t100_3.pk JOIN t10_4 ON t100_3.colidx = t10_4.pk JOIN t100_4 ON t10_4.colidx = t100_4.pk JOIN t10_5 ON t100_4.colidx = t10_5.pk JOIN t100_5 ON t10_5.colidx = t100_5.pk JOIN t10_6 ON t100_5.colidx = t10_6.pk JOIN t100_6 ON t10_6.colidx = t100_6.pk JOIN t10_7 ON t100_6.colidx = t10_7.pk JOIN t100_7 ON t10_7.colidx = t100_7.pk JOIN t10_8 ON t100_7.colidx = t10_8.pk;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t10_1 NULL ALL colidx NULL NULL NULL 10 100.00 Using where
1 SIMPLE t10_2 NULL ALL PRIMARY,colidx NULL NULL NULL 10 100.00 Using where; Using join buffer (hash join)
1 SIMPLE t100_1 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t10_1.colidx 1 10.00 Using where
1 SIMPLE t100_2 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t10_2.colidx 1 100.00 Using where
1 SIMPLE t10_3 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_2.colidx 1 100.00 Using where
1 SIMPLE t100_3 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t10_3.colidx 1 100.00 Using where
1 SIMPLE t10_4 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_3.colidx 1 100.00 Using where
1 SIMPLE t100_4 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t10_4.colidx 1 100.00 Using where
1 SIMPLE t10_5 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_4.colidx 1 100.00 Using where
1 SIMPLE t100_5 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t10_5.colidx 1 100.00 Using where
1 SIMPLE t10_6 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_5.colidx 1 100.00 Using where
1 SIMPLE t100_6 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t10_6.colidx 1 100.00 Using where
1 SIMPLE t10_7 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_6.colidx 1 100.00 Using where
1 SIMPLE t100_7 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t10_7.colidx 1 100.00 Using where
1 SIMPLE t10_8 NULL eq_ref PRIMARY PRIMARY 4 test.t100_7.colidx 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`t10_1`.`pk` AS `pk`,`test`.`t10_1`.`colidx` AS `colidx`,`test`.`t10_1`.`col` AS `col`,`test`.`t100_1`.`pk` AS `pk`,`test`.`t100_1`.`colidx` AS `colidx`,`test`.`t100_1`.`col` AS `col`,`test`.`t10_2`.`pk` AS `pk`,`test`.`t10_2`.`colidx` AS `colidx`,`test`.`t10_2`.`col` AS `col`,`test`.`t100_2`.`pk` AS `pk`,`test`.`t100_2`.`colidx` AS `colidx`,`test`.`t100_2`.`col` AS `col`,`test`.`t10_3`.`pk` AS `pk`,`test`.`t10_3`.`colidx` AS `colidx`,`test`.`t10_3`.`col` AS `col`,`test`.`t100_3`.`pk` AS `pk`,`test`.`t100_3`.`colidx` AS `colidx`,`test`.`t100_3`.`col` AS `col`,`test`.`t10_4`.`pk` AS `pk`,`test`.`t10_4`.`colidx` AS `colidx`,`test`.`t10_4`.`col` AS `col`,`test`.`t100_4`.`pk` AS `pk`,`test`.`t100_4`.`colidx` AS `colidx`,`test`.`t100_4`.`col` AS `col`,`test`.`t10_5`.`pk` AS `pk`,`test`.`t10_5`.`colidx` AS `colidx`,`test`.`t10_5`.`col` AS `col`,`test`.`t100_5`.`pk` AS `pk`,`test`.`t100_5`.`colidx` AS `colidx`,`test`.`t100_5`.`col` AS `col`,`test`.`t10_6`.`pk` AS `pk`,`test`.`t10_6`.`colidx` AS `colidx`,`test`.`t10_6`.`col` AS `col`,`test`.`t100_6`.`pk` AS `pk`,`test`.`t100_6`.`colidx` AS `colidx`,`test`.`t100_6`.`col` AS `col`,`test`.`t10_7`.`pk` AS `pk`,`test`.`t10_7`.`colidx` AS `colidx`,`test`.`t10_7`.`col` AS `col`,`test`.`t100_7`.`pk` AS `pk`,`test`.`t100_7`.`colidx` AS `colidx`,`test`.`t100_7`.`col` AS `col`,`test`.`t10_8`.`pk` AS `pk`,`test`.`t10_8`.`colidx` AS `colidx`,`test`.`t10_8`.`col` AS `col` from `test`.`t10_1` join `test`.`t100_1` join `test`.`t10_2` join `test`.`t100_2` join `test`.`t10_3` join `test`.`t100_3` join `test`.`t10_4` join `test`.`t100_4` join `test`.`t10_5` join `test`.`t100_5` join `test`.`t10_6` join `test`.`t100_6` join `test`.`t10_7` join `test`.`t100_7` join `test`.`t10_8` where ((`test`.`t100_1`.`pk` = `test`.`t10_1`.`colidx`) and (`test`.`t100_1`.`colidx` = `test`.`t10_2`.`pk`) and (`test`.`t100_2`.`pk` = `test`.`t10_2`.`colidx`) and (`test`.`t10_3`.`pk` = `test`.`t100_2`.`colidx`) and (`test`.`t100_3`.`pk` = `test`.`t10_3`.`colidx`) and (`test`.`t10_4`.`pk` = `test`.`t100_3`.`colidx`) and (`test`.`t100_4`.`pk` = `test`.`t10_4`.`colidx`) and (`test`.`t10_5`.`pk` = `test`.`t100_4`.`colidx`) and (`test`.`t100_5`.`pk` = `test`.`t10_5`.`colidx`) and (`test`.`t10_6`.`pk` = `test`.`t100_5`.`colidx`) and (`test`.`t100_6`.`pk` = `test`.`t10_6`.`colidx`) and (`test`.`t10_7`.`pk` = `test`.`t100_6`.`colidx`) and (`test`.`t100_7`.`pk` = `test`.`t10_7`.`colidx`) and (`test`.`t10_8`.`pk` = `test`.`t100_7`.`colidx`))
### Partial_plans: 9123
FLUSH STATUS;
#
# Chain test b: (...,col):(colidx, col):(pk,col):(colidx,col):(pk,...)
#
EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.col = t100_1.colidx JOIN t10_2 ON t100_1.col = t10_2.pk JOIN t100_2 ON t10_2.col = t100_2.colidx JOIN t10_3 ON t100_2.col = t10_3.pk JOIN t100_3 ON t10_3.col = t100_3.colidx JOIN t10_4 ON t100_3.col = t10_4.pk JOIN t100_4 ON t10_4.col = t100_4.colidx JOIN t10_5 ON t100_4.col = t10_5.pk JOIN t100_5 ON t10_5.col = t100_5.colidx JOIN t10_6 ON t100_5.col = t10_6.pk JOIN t100_6 ON t10_6.col = t100_6.colidx JOIN t10_7 ON t100_6.col = t10_7.pk JOIN t100_7 ON t10_7.col = t100_7.colidx JOIN t10_8 ON t100_7.col = t10_8.pk;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t10_1 NULL ALL NULL NULL NULL NULL 10 100.00 Using where
1 SIMPLE t100_1 NULL ref colidx colidx 5 test.t10_1.col 10 100.00 NULL
1 SIMPLE t10_2 NULL ALL PRIMARY NULL NULL NULL 10 10.00 Using where; Using join buffer (hash join)
1 SIMPLE t100_2 NULL ref colidx colidx 5 test.t10_2.col 10 100.00 Using where
1 SIMPLE t10_3 NULL eq_ref PRIMARY PRIMARY 4 test.t100_2.col 1 100.00 Using where
1 SIMPLE t10_4 NULL ALL PRIMARY NULL NULL NULL 10 100.00 Using where; Using join buffer (hash join)
1 SIMPLE t100_3 NULL ref colidx colidx 5 test.t10_3.col 10 10.00 Using where
1 SIMPLE t100_4 NULL ref colidx colidx 5 test.t10_4.col 10 100.00 Using where
1 SIMPLE t10_5 NULL eq_ref PRIMARY PRIMARY 4 test.t100_4.col 1 100.00 Using where
1 SIMPLE t100_5 NULL ref colidx colidx 5 test.t10_5.col 10 100.00 Using where
1 SIMPLE t10_6 NULL eq_ref PRIMARY PRIMARY 4 test.t100_5.col 1 100.00 Using where
1 SIMPLE t100_6 NULL ref colidx colidx 5 test.t10_6.col 10 100.00 Using where
1 SIMPLE t10_7 NULL eq_ref PRIMARY PRIMARY 4 test.t100_6.col 1 100.00 Using where
1 SIMPLE t100_7 NULL ref colidx colidx 5 test.t10_7.col 10 100.00 Using where
1 SIMPLE t10_8 NULL eq_ref PRIMARY PRIMARY 4 test.t100_7.col 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`t10_1`.`pk` AS `pk`,`test`.`t10_1`.`colidx` AS `colidx`,`test`.`t10_1`.`col` AS `col`,`test`.`t100_1`.`pk` AS `pk`,`test`.`t100_1`.`colidx` AS `colidx`,`test`.`t100_1`.`col` AS `col`,`test`.`t10_2`.`pk` AS `pk`,`test`.`t10_2`.`colidx` AS `colidx`,`test`.`t10_2`.`col` AS `col`,`test`.`t100_2`.`pk` AS `pk`,`test`.`t100_2`.`colidx` AS `colidx`,`test`.`t100_2`.`col` AS `col`,`test`.`t10_3`.`pk` AS `pk`,`test`.`t10_3`.`colidx` AS `colidx`,`test`.`t10_3`.`col` AS `col`,`test`.`t100_3`.`pk` AS `pk`,`test`.`t100_3`.`colidx` AS `colidx`,`test`.`t100_3`.`col` AS `col`,`test`.`t10_4`.`pk` AS `pk`,`test`.`t10_4`.`colidx` AS `colidx`,`test`.`t10_4`.`col` AS `col`,`test`.`t100_4`.`pk` AS `pk`,`test`.`t100_4`.`colidx` AS `colidx`,`test`.`t100_4`.`col` AS `col`,`test`.`t10_5`.`pk` AS `pk`,`test`.`t10_5`.`colidx` AS `colidx`,`test`.`t10_5`.`col` AS `col`,`test`.`t100_5`.`pk` AS `pk`,`test`.`t100_5`.`colidx` AS `colidx`,`test`.`t100_5`.`col` AS `col`,`test`.`t10_6`.`pk` AS `pk`,`test`.`t10_6`.`colidx` AS `colidx`,`test`.`t10_6`.`col` AS `col`,`test`.`t100_6`.`pk` AS `pk`,`test`.`t100_6`.`colidx` AS `colidx`,`test`.`t100_6`.`col` AS `col`,`test`.`t10_7`.`pk` AS `pk`,`test`.`t10_7`.`colidx` AS `colidx`,`test`.`t10_7`.`col` AS `col`,`test`.`t100_7`.`pk` AS `pk`,`test`.`t100_7`.`colidx` AS `colidx`,`test`.`t100_7`.`col` AS `col`,`test`.`t10_8`.`pk` AS `pk`,`test`.`t10_8`.`colidx` AS `colidx`,`test`.`t10_8`.`col` AS `col` from `test`.`t10_1` join `test`.`t100_1` join `test`.`t10_2` join `test`.`t100_2` join `test`.`t10_3` join `test`.`t100_3` join `test`.`t10_4` join `test`.`t100_4` join `test`.`t10_5` join `test`.`t100_5` join `test`.`t10_6` join `test`.`t100_6` join `test`.`t10_7` join `test`.`t100_7` join `test`.`t10_8` where ((`test`.`t100_1`.`colidx` = `test`.`t10_1`.`col`) and (`test`.`t10_2`.`pk` = `test`.`t100_1`.`col`) and (`test`.`t100_2`.`colidx` = `test`.`t10_2`.`col`) and (`test`.`t10_3`.`pk` = `test`.`t100_2`.`col`) and (`test`.`t100_3`.`colidx` = `test`.`t10_3`.`col`) and (`test`.`t100_3`.`col` = `test`.`t10_4`.`pk`) and (`test`.`t100_4`.`colidx` = `test`.`t10_4`.`col`) and (`test`.`t10_5`.`pk` = `test`.`t100_4`.`col`) and (`test`.`t100_5`.`colidx` = `test`.`t10_5`.`col`) and (`test`.`t10_6`.`pk` = `test`.`t100_5`.`col`) and (`test`.`t100_6`.`colidx` = `test`.`t10_6`.`col`) and (`test`.`t10_7`.`pk` = `test`.`t100_6`.`col`) and (`test`.`t100_7`.`colidx` = `test`.`t10_7`.`col`) and (`test`.`t10_8`.`pk` = `test`.`t100_7`.`col`))
### Partial_plans: 5799059
FLUSH STATUS;
#
# Chain test c: (...,colidx):(col, pk):(col,colidx):(col,...)
#
EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.col JOIN t10_2 ON t100_1.pk = t10_2.col JOIN t100_2 ON t10_2.colidx = t100_2.col JOIN t10_3 ON t100_2.pk = t10_3.col JOIN t100_3 ON t10_3.colidx = t100_3.col JOIN t10_4 ON t100_3.pk = t10_4.col JOIN t100_4 ON t10_4.colidx = t100_4.col JOIN t10_5 ON t100_4.pk = t10_5.col JOIN t100_5 ON t10_5.colidx = t100_5.col JOIN t10_6 ON t100_5.pk = t10_6.col JOIN t100_6 ON t10_6.colidx = t100_6.col JOIN t10_7 ON t100_6.pk = t10_7.col JOIN t100_7 ON t10_7.colidx = t100_7.col JOIN t10_8 ON t100_7.pk = t10_8.col;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t10_7 NULL ALL colidx NULL NULL NULL 10 100.00 Using where
1 SIMPLE t10_8 NULL ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (hash join)
1 SIMPLE t100_6 NULL eq_ref PRIMARY PRIMARY 4 test.t10_7.col 1 100.00 Using where
1 SIMPLE t100_7 NULL eq_ref PRIMARY PRIMARY 4 test.t10_8.col 1 10.00 Using where
1 SIMPLE t10_6 NULL ref colidx colidx 5 test.t100_6.col 1 100.00 Using where
1 SIMPLE t100_5 NULL eq_ref PRIMARY PRIMARY 4 test.t10_6.col 1 100.00 Using where
1 SIMPLE t10_5 NULL ref colidx colidx 5 test.t100_5.col 1 100.00 Using where
1 SIMPLE t100_4 NULL eq_ref PRIMARY PRIMARY 4 test.t10_5.col 1 100.00 Using where
1 SIMPLE t10_4 NULL ref colidx colidx 5 test.t100_4.col 1 100.00 Using where
1 SIMPLE t100_3 NULL eq_ref PRIMARY PRIMARY 4 test.t10_4.col 1 100.00 Using where
1 SIMPLE t10_3 NULL ref colidx colidx 5 test.t100_3.col 1 100.00 Using where
1 SIMPLE t100_2 NULL eq_ref PRIMARY PRIMARY 4 test.t10_3.col 1 100.00 Using where
1 SIMPLE t10_2 NULL ref colidx colidx 5 test.t100_2.col 1 100.00 Using where
1 SIMPLE t100_1 NULL eq_ref PRIMARY PRIMARY 4 test.t10_2.col 1 100.00 Using where
1 SIMPLE t10_1 NULL ref colidx colidx 5 test.t100_1.col 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`t10_1`.`pk` AS `pk`,`test`.`t10_1`.`colidx` AS `colidx`,`test`.`t10_1`.`col` AS `col`,`test`.`t100_1`.`pk` AS `pk`,`test`.`t100_1`.`colidx` AS `colidx`,`test`.`t100_1`.`col` AS `col`,`test`.`t10_2`.`pk` AS `pk`,`test`.`t10_2`.`colidx` AS `colidx`,`test`.`t10_2`.`col` AS `col`,`test`.`t100_2`.`pk` AS `pk`,`test`.`t100_2`.`colidx` AS `colidx`,`test`.`t100_2`.`col` AS `col`,`test`.`t10_3`.`pk` AS `pk`,`test`.`t10_3`.`colidx` AS `colidx`,`test`.`t10_3`.`col` AS `col`,`test`.`t100_3`.`pk` AS `pk`,`test`.`t100_3`.`colidx` AS `colidx`,`test`.`t100_3`.`col` AS `col`,`test`.`t10_4`.`pk` AS `pk`,`test`.`t10_4`.`colidx` AS `colidx`,`test`.`t10_4`.`col` AS `col`,`test`.`t100_4`.`pk` AS `pk`,`test`.`t100_4`.`colidx` AS `colidx`,`test`.`t100_4`.`col` AS `col`,`test`.`t10_5`.`pk` AS `pk`,`test`.`t10_5`.`colidx` AS `colidx`,`test`.`t10_5`.`col` AS `col`,`test`.`t100_5`.`pk` AS `pk`,`test`.`t100_5`.`colidx` AS `colidx`,`test`.`t100_5`.`col` AS `col`,`test`.`t10_6`.`pk` AS `pk`,`test`.`t10_6`.`colidx` AS `colidx`,`test`.`t10_6`.`col` AS `col`,`test`.`t100_6`.`pk` AS `pk`,`test`.`t100_6`.`colidx` AS `colidx`,`test`.`t100_6`.`col` AS `col`,`test`.`t10_7`.`pk` AS `pk`,`test`.`t10_7`.`colidx` AS `colidx`,`test`.`t10_7`.`col` AS `col`,`test`.`t100_7`.`pk` AS `pk`,`test`.`t100_7`.`colidx` AS `colidx`,`test`.`t100_7`.`col` AS `col`,`test`.`t10_8`.`pk` AS `pk`,`test`.`t10_8`.`colidx` AS `colidx`,`test`.`t10_8`.`col` AS `col` from `test`.`t10_1` join `test`.`t100_1` join `test`.`t10_2` join `test`.`t100_2` join `test`.`t10_3` join `test`.`t100_3` join `test`.`t10_4` join `test`.`t100_4` join `test`.`t10_5` join `test`.`t100_5` join `test`.`t10_6` join `test`.`t100_6` join `test`.`t10_7` join `test`.`t100_7` join `test`.`t10_8` where ((`test`.`t10_1`.`colidx` = `test`.`t100_1`.`col`) and (`test`.`t100_1`.`pk` = `test`.`t10_2`.`col`) and (`test`.`t10_2`.`colidx` = `test`.`t100_2`.`col`) and (`test`.`t100_2`.`pk` = `test`.`t10_3`.`col`) and (`test`.`t10_3`.`colidx` = `test`.`t100_3`.`col`) and (`test`.`t100_3`.`pk` = `test`.`t10_4`.`col`) and (`test`.`t10_4`.`colidx` = `test`.`t100_4`.`col`) and (`test`.`t100_4`.`pk` = `test`.`t10_5`.`col`) and (`test`.`t10_5`.`colidx` = `test`.`t100_5`.`col`) and (`test`.`t100_5`.`pk` = `test`.`t10_6`.`col`) and (`test`.`t10_6`.`colidx` = `test`.`t100_6`.`col`) and (`test`.`t100_6`.`pk` = `test`.`t10_7`.`col`) and (`test`.`t100_7`.`col` = `test`.`t10_7`.`colidx`) and (`test`.`t100_7`.`pk` = `test`.`t10_8`.`col`))
### Partial_plans: 13248
FLUSH STATUS;
#
# Chain test d: (...,colidx):(pk, col):(pk,colidx):(pk,col):(pk,...)
#
EXPLAIN SELECT * FROM t10_1 JOIN t100_1 ON t10_1.colidx = t100_1.pk JOIN t10_2 ON t100_1.col = t10_2.pk JOIN t100_2 ON t10_2.colidx = t100_2.pk JOIN t10_3 ON t100_2.col = t10_3.pk JOIN t100_3 ON t10_3.colidx = t100_3.pk JOIN t10_4 ON t100_3.col = t10_4.pk JOIN t100_4 ON t10_4.colidx = t100_4.pk JOIN t10_5 ON t100_4.col = t10_5.pk JOIN t100_5 ON t10_5.colidx = t100_5.pk JOIN t10_6 ON t100_5.col = t10_6.pk JOIN t100_6 ON t10_6.colidx = t100_6.pk JOIN t10_7 ON t100_6.col = t10_7.pk JOIN t100_7 ON t10_7.colidx = t100_7.pk JOIN t10_8 ON t100_7.col = t10_8.pk;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t10_2 NULL ALL PRIMARY,colidx NULL NULL NULL 10 100.00 Using where
1 SIMPLE t10_1 NULL ALL colidx NULL NULL NULL 10 100.00 Using where; Using join buffer (hash join)
1 SIMPLE t100_1 NULL eq_ref PRIMARY PRIMARY 4 test.t10_1.colidx 1 10.00 Using where
1 SIMPLE t100_2 NULL eq_ref PRIMARY PRIMARY 4 test.t10_2.colidx 1 100.00 Using where
1 SIMPLE t10_3 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_2.col 1 100.00 Using where
1 SIMPLE t100_3 NULL eq_ref PRIMARY PRIMARY 4 test.t10_3.colidx 1 100.00 Using where
1 SIMPLE t10_4 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_3.col 1 100.00 Using where
1 SIMPLE t100_4 NULL eq_ref PRIMARY PRIMARY 4 test.t10_4.colidx 1 100.00 Using where
1 SIMPLE t10_5 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_4.col 1 100.00 Using where
1 SIMPLE t100_5 NULL eq_ref PRIMARY PRIMARY 4 test.t10_5.colidx 1 100.00 Using where
1 SIMPLE t10_6 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_5.col 1 100.00 Using where
1 SIMPLE t100_6 NULL eq_ref PRIMARY PRIMARY 4 test.t10_6.colidx 1 100.00 Using where
1 SIMPLE t10_7 NULL eq_ref PRIMARY,colidx PRIMARY 4 test.t100_6.col 1 100.00 Using where
1 SIMPLE t100_7 NULL eq_ref PRIMARY PRIMARY 4 test.t10_7.colidx 1 100.00 Using where
1 SIMPLE t10_8 NULL eq_ref PRIMARY PRIMARY 4 test.t100_7.col 1 100.00 NULL
Warnings:
Note 1003 /* select#1 */ select `test`.`t10_1`.`pk` AS `pk`,`test`.`t10_1`.`colidx` AS `colidx`,`test`.`t10_1`.`col` AS `col`,`test`.`t100_1`.`pk` AS `pk`,`test`.`t100_1`.`colidx` AS `colidx`,`test`.`t100_1`.`col` AS `col`,`test`.`t10_2`.`pk` AS `pk`,`test`.`t10_2`.`colidx` AS `colidx`,`test`.`t10_2`.`col` AS `col`,`test`.`t100_2`.`pk` AS `pk`,`test`.`t100_2`.`colidx` AS `colidx`,`test`.`t100_2`.`col` AS `col`,`test`.`t10_3`.`pk` AS `pk`,`test`.`t10_3`.`colidx` AS `colidx`,`test`.`t10_3`.`col` AS `col`,`test`.`t100_3`.`pk` AS `pk`,`test`.`t100_3`.`colidx` AS `colidx`,`test`.`t100_3`.`col` AS `col`,`test`.`t10_4`.`pk` AS `pk`,`test`.`t10_4`.`colidx` AS `colidx`,`test`.`t10_4`.`col` AS `col`,`test`.`t100_4`.`pk` AS `pk`,`test`.`t100_4`.`colidx` AS `colidx`,`test`.`t100_4`.`col` AS `col`,`test`.`t10_5`.`pk` AS `pk`,`test`.`t10_5`.`colidx` AS `colidx`,`test`.`t10_5`.`col` AS `col`,`test`.`t100_5`.`pk` AS `pk`,`test`.`t100_5`.`colidx` AS `colidx`,`test`.`t100_5`.`col` AS `col`,`test`.`t10_6`.`pk` AS `pk`,`test`.`t10_6`.`colidx` AS `colidx`,`test`.`t10_6`.`col` AS `col`,`test`.`t100_6`.`pk` AS `pk`,`test`.`t100_6`.`colidx` AS `colidx`,`test`.`t100_6`.`col` AS `col`,`test`.`t10_7`.`pk` AS `pk`,`test`.`t10_7`.`colidx` AS `colidx`,`test`.`t10_7`.`col` AS `col`,`test`.`t100_7`.`pk` AS `pk`,`test`.`t100_7`.`colidx` AS `colidx`,`test`.`t100_7`.`col` AS `col`,`test`.`t10_8`.`pk` AS `pk`,`test`.`t10_8`.`colidx` AS `colidx`,`test`.`t10_8`.`col` AS `col` from `test`.`t10_1` join `test`.`t100_1` join `test`.`t10_2` join `test`.`t100_2` join `test`.`t10_3` join `test`.`t100_3` join `test`.`t10_4` join `test`.`t100_4` join `test`.`t10_5` join `test`.`t100_5` join `test`.`t10_6` join `test`.`t100_6` join `test`.`t10_7` join `test`.`t100_7` join `test`.`t10_8` where ((`test`.`t100_1`.`pk` = `test`.`t10_1`.`colidx`) and (`test`.`t100_1`.`col` = `test`.`t10_2`.`pk`) and (`test`.`t100_2`.`pk` = `test`.`t10_2`.`colidx`) and (`test`.`t10_3`.`pk` = `test`.`t100_2`.`col`) and (`test`.`t100_3`.`pk` = `test`.`t10_3`.`colidx`) and (`test`.`t10_4`.`pk` = `test`.`t100_3`.`col`) and (`test`.`t100_4`.`pk` = `test`.`t10_4`.`colidx`) and (`test`.`t10_5`.`pk` = `test`.`t100_4`.`col`) and (`test`.`t100_5`.`pk` = `test`.`t10_5`.`colidx`) and (`test`.`t10_6`.`pk` = `test`.`t100_5`.`col`) and (`test`.`t100_6`.`pk` = `test`.`t10_6`.`colidx`) and (`test`.`t10_7`.`pk` = `test`.`t100_6`.`col`) and (`test`.`t100_7`.`pk` = `test`.`t10_7`.`colidx`) and (`test`.`t10_8`.`pk` = `test`.`t100_7`.`col`))
### Partial_plans: 1795
FLUSH STATUS;
#
# Cleanup after TEST 2
#
DROP TABLE tbl10, tbl100;
DROP TABLE t10_1,t10_2,t10_3,t10_4,t10_5,t10_6,t10_7,t10_8,t10_9;
DROP TABLE t100_1,t100_2,t100_3,t100_4,t100_5,t100_6,t100_7,t100_8,t100_9;
UPDATE mysql.engine_cost
SET cost_value = DEFAULT
WHERE cost_name = 'memory_block_read_cost';
FLUSH OPTIMIZER_COSTS;
|
