Edit File: greedy_search.result
# # 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 type possible_keys key key_len ref rows Extra 1 SIMPLE vehicles ALL NULL NULL NULL NULL 80 Using where 1 SIMPLE subtypes eq_ref PRIMARY PRIMARY 4 test.vehicles.subtype_id 1 NULL 1 SIMPLE heating eq_ref PRIMARY PRIMARY 4 test.vehicles.heating_id 1 NULL 1 SIMPLE windows eq_ref PRIMARY PRIMARY 4 test.vehicles.window_id 1 NULL 1 SIMPLE fuels eq_ref PRIMARY PRIMARY 4 test.vehicles.fuel_id 1 NULL 1 SIMPLE transmissions eq_ref PRIMARY PRIMARY 4 test.vehicles.transmission_id 1 NULL 1 SIMPLE steerings eq_ref PRIMARY PRIMARY 4 test.vehicles.steering_id 1 NULL 1 SIMPLE drives eq_ref PRIMARY PRIMARY 4 test.vehicles.drive_id 1 NULL 1 SIMPLE wheels eq_ref PRIMARY PRIMARY 4 test.vehicles.wheels_id 1 NULL 1 SIMPLE engine eq_ref PRIMARY PRIMARY 4 test.vehicles.engine_id 1 NULL 1 SIMPLE price_ranges eq_ref PRIMARY PRIMARY 4 test.vehicles.price_range_id 1 NULL 1 SIMPLE colors eq_ref PRIMARY PRIMARY 4 test.vehicles.color_id 1 NULL 1 SIMPLE interiors eq_ref PRIMARY PRIMARY 4 test.vehicles.interior_id 1 NULL 1 SIMPLE countries eq_ref PRIMARY PRIMARY 4 test.vehicles.assembled_in 1 NULL 1 SIMPLE models eq_ref PRIMARY,brand_id PRIMARY 4 test.vehicles.model_id 1 Using where 1 SIMPLE brands eq_ref PRIMARY PRIMARY 4 test.models.brand_id 1 NULL ### Partial_plans: 51102 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 type possible_keys key key_len ref rows Extra 1 SIMPLE brands ALL NULL NULL NULL NULL 7 Using where 1 SIMPLE models ref brand_id brand_id 5 test.brands.id_nokey 4 NULL 1 SIMPLE heating ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop) 1 SIMPLE transmissions ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop) 1 SIMPLE wheels ALL NULL NULL NULL NULL 10 Using join buffer (Block Nested Loop) 1 SIMPLE vehicles ALL NULL NULL NULL NULL 80 Using where; Using join buffer (Block Nested Loop) 1 SIMPLE windows eq_ref PRIMARY PRIMARY 4 test.vehicles.window_id 1 NULL 1 SIMPLE fuels eq_ref PRIMARY PRIMARY 4 test.vehicles.fuel_id 1 NULL 1 SIMPLE steerings eq_ref PRIMARY PRIMARY 4 test.vehicles.steering_id 1 NULL 1 SIMPLE drives eq_ref PRIMARY PRIMARY 4 test.vehicles.drive_id 1 NULL 1 SIMPLE engine eq_ref PRIMARY PRIMARY 4 test.vehicles.engine_id 1 NULL 1 SIMPLE price_ranges eq_ref PRIMARY PRIMARY 4 test.vehicles.price_range_id 1 NULL 1 SIMPLE interiors eq_ref PRIMARY PRIMARY 4 test.vehicles.interior_id 1 NULL 1 SIMPLE countries eq_ref PRIMARY PRIMARY 4 test.vehicles.assembled_in 1 NULL 1 SIMPLE subtypes eq_ref PRIMARY PRIMARY 4 test.vehicles.subtype_id 1 NULL 1 SIMPLE colors eq_ref PRIMARY PRIMARY 4 test.vehicles.color_id 1 NULL ### Partial_plans: 483463 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 type possible_keys key key_len ref rows Extra 1 SIMPLE t10_1 ALL colidx NULL NULL NULL 10 Using where 1 SIMPLE t100_1 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_1.colidx 1 Using where 1 SIMPLE t10_2 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_1.colidx 1 Using where 1 SIMPLE t100_2 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_2.colidx 1 Using where 1 SIMPLE t10_3 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_2.colidx 1 Using where 1 SIMPLE t100_3 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_3.colidx 1 Using where 1 SIMPLE t10_4 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_3.colidx 1 Using where 1 SIMPLE t100_4 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_4.colidx 1 Using where 1 SIMPLE t10_5 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_4.colidx 1 Using where 1 SIMPLE t100_5 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_5.colidx 1 Using where 1 SIMPLE t10_6 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_5.colidx 1 Using where 1 SIMPLE t100_6 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_6.colidx 1 Using where 1 SIMPLE t10_7 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_6.colidx 1 Using where 1 SIMPLE t100_7 eq_ref PRIMARY,colidx PRIMARY 4 test.t10_7.colidx 1 Using where 1 SIMPLE t10_8 eq_ref PRIMARY PRIMARY 4 test.t100_7.colidx 1 NULL ### Partial_plans: 5478 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 type possible_keys key key_len ref rows Extra 1 SIMPLE t10_1 ALL NULL NULL NULL NULL 10 Using where 1 SIMPLE t100_1 ref colidx colidx 5 test.t10_1.col 10 Using where 1 SIMPLE t10_2 eq_ref PRIMARY PRIMARY 4 test.t100_1.col 1 Using where 1 SIMPLE t100_2 ref colidx colidx 5 test.t10_2.col 10 Using where 1 SIMPLE t10_3 eq_ref PRIMARY PRIMARY 4 test.t100_2.col 1 Using where 1 SIMPLE t100_3 ref colidx colidx 5 test.t10_3.col 10 Using where 1 SIMPLE t10_4 eq_ref PRIMARY PRIMARY 4 test.t100_3.col 1 Using where 1 SIMPLE t100_4 ref colidx colidx 5 test.t10_4.col 10 Using where 1 SIMPLE t10_5 eq_ref PRIMARY PRIMARY 4 test.t100_4.col 1 Using where 1 SIMPLE t100_5 ref colidx colidx 5 test.t10_5.col 10 Using where 1 SIMPLE t10_6 eq_ref PRIMARY PRIMARY 4 test.t100_5.col 1 Using where 1 SIMPLE t100_6 ref colidx colidx 5 test.t10_6.col 10 Using where 1 SIMPLE t10_7 eq_ref PRIMARY PRIMARY 4 test.t100_6.col 1 Using where 1 SIMPLE t100_7 ref colidx colidx 5 test.t10_7.col 10 Using where 1 SIMPLE t10_8 eq_ref PRIMARY PRIMARY 4 test.t100_7.col 1 NULL ### Partial_plans: 753518 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 type possible_keys key key_len ref rows Extra 1 SIMPLE t10_8 ALL NULL NULL NULL NULL 10 Using where 1 SIMPLE t100_7 eq_ref PRIMARY PRIMARY 4 test.t10_8.col 1 Using where 1 SIMPLE t10_7 ref colidx colidx 5 test.t100_7.col 1 Using where 1 SIMPLE t100_6 eq_ref PRIMARY PRIMARY 4 test.t10_7.col 1 Using where 1 SIMPLE t10_6 ref colidx colidx 5 test.t100_6.col 1 Using where 1 SIMPLE t100_5 eq_ref PRIMARY PRIMARY 4 test.t10_6.col 1 Using where 1 SIMPLE t10_5 ref colidx colidx 5 test.t100_5.col 1 Using where 1 SIMPLE t100_4 eq_ref PRIMARY PRIMARY 4 test.t10_5.col 1 Using where 1 SIMPLE t10_4 ref colidx colidx 5 test.t100_4.col 1 Using where 1 SIMPLE t100_3 eq_ref PRIMARY PRIMARY 4 test.t10_4.col 1 Using where 1 SIMPLE t10_3 ref colidx colidx 5 test.t100_3.col 1 Using where 1 SIMPLE t100_2 eq_ref PRIMARY PRIMARY 4 test.t10_3.col 1 Using where 1 SIMPLE t10_2 ref colidx colidx 5 test.t100_2.col 1 Using where 1 SIMPLE t100_1 eq_ref PRIMARY PRIMARY 4 test.t10_2.col 1 Using where 1 SIMPLE t10_1 ref colidx colidx 5 test.t100_1.col 1 NULL ### Partial_plans: 17720 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 type possible_keys key key_len ref rows Extra 1 SIMPLE t10_1 ALL colidx NULL NULL NULL 10 Using where 1 SIMPLE t100_1 eq_ref PRIMARY PRIMARY 4 test.t10_1.colidx 1 Using where 1 SIMPLE t10_2 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_1.col 1 Using where 1 SIMPLE t100_2 eq_ref PRIMARY PRIMARY 4 test.t10_2.colidx 1 Using where 1 SIMPLE t10_3 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_2.col 1 Using where 1 SIMPLE t100_3 eq_ref PRIMARY PRIMARY 4 test.t10_3.colidx 1 Using where 1 SIMPLE t10_4 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_3.col 1 Using where 1 SIMPLE t100_4 eq_ref PRIMARY PRIMARY 4 test.t10_4.colidx 1 Using where 1 SIMPLE t10_5 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_4.col 1 Using where 1 SIMPLE t100_5 eq_ref PRIMARY PRIMARY 4 test.t10_5.colidx 1 Using where 1 SIMPLE t10_6 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_5.col 1 Using where 1 SIMPLE t100_6 eq_ref PRIMARY PRIMARY 4 test.t10_6.colidx 1 Using where 1 SIMPLE t10_7 eq_ref PRIMARY,colidx PRIMARY 4 test.t100_6.col 1 Using where 1 SIMPLE t100_7 eq_ref PRIMARY PRIMARY 4 test.t10_7.colidx 1 Using where 1 SIMPLE t10_8 eq_ref PRIMARY PRIMARY 4 test.t100_7.col 1 NULL ### Partial_plans: 2296 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;