/usr/lib/mysql-testsuite/r/greedy_search.result is in percona-server-test-5.6 5.6.22-rel71.0-0ubuntu4.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 | #
# 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;
|