You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
214 lines
31 KiB
214 lines
31 KiB
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 (Block Nested Loop)
|
|
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 (Block Nested Loop)
|
|
1 SIMPLE heating NULL ALL NULL NULL NULL NULL 10 10.00 Using where; Using join buffer (Block Nested Loop)
|
|
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 (Block Nested Loop)
|
|
1 SIMPLE wheels NULL ALL NULL NULL NULL NULL 10 10.00 Using where; Using join buffer (Block Nested Loop)
|
|
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 (Block Nested Loop)
|
|
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 (Block Nested Loop)
|
|
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 (Block Nested Loop)
|
|
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 (Block Nested Loop)
|
|
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 (Block Nested Loop)
|
|
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;
|
|
|