Comparing PlansΒΆ
As a quick recap: the role of the optimizer is to pick the best execution plan out of frequently thousands of choices. Among those choices could be several different indexes and access methods for each. So far we have demonstrated an index on p (population) and two different execution plans:
- A range scan on
p (population) - A table scan
As the p (population) index was demonstrated to not be selective, I will now add an index on c (continent). In a typical production environment, you may want to now drop the p (population) index since it is not providing an value. However, I will leave it present since I wish to demonstrate that the optimizer can successfully evaluate many choices.
Example 7: Adding an index on continent
ALTER TABLE Country ADD INDEX c (continent);
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE population > 5000000 continent='Asia';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "28.20"
},
"table": {
"table_name": "Country",
"access_type": "ref", # The access method ref means
"possible_keys": [ # use a non-unique index
"p",
"c"
],
"key": "c", # The continent index is selected
"used_key_parts": [
"Continent"
],
"key_length": "1",
"ref": [
"const"
],
"rows_examined_per_scan": 51,
"rows_produced_per_join": 23,
"filtered": "45.19",
"cost_info": {
"read_cost": "18.00",
"eval_cost": "4.61",
"prefix_cost": "28.20",
"data_read_per_join": "5K"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
],
"attached_condition": "(`world`.`Country`.`Population` > 5000000)"
}
}
}
In Example 7, we can see that the c (Continent) index is preferred over the p (Population) index and a table scan. It performs its job as an index, which is to eliminate work. The optimizer estimates that after the index has been used, only 51 rows (rows_examined_per_scan) will need to be examined. Another way of interpreting this, is that a sufficient number of Countries are not on the continent of Asia, and thus the index is selective.
In Example 8 we can see that by modifying the query slightly, and choosing to filter on >500M versus >5M the index selection changes to be p (population). This makes sense: there are only two countries with a population >500M and thus the index is more selective.
Example 8: With a large population a range on p is preferred over c
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE continent='Asia' and population > 500000000;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "7.01"
},
"table": {
"table_name": "Country",
"access_type": "range", # Range instead of ref
"possible_keys": [
"p",
"c"
],
"key": "p", # Key used has switched from Example 7
"used_key_parts": [
"Population"
],
"key_length": "4",
"rows_examined_per_scan": 2,
"rows_produced_per_join": 0,
"filtered": "21.34",
"index_condition": "(`world`.`Country`.`Population` > 500000000)",
"cost_info": {
"read_cost": "6.58",
"eval_cost": "0.43",
"prefix_cost": "7.01",
"data_read_per_join": "112"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
],
"attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"
}
}
}
With the addition of the continent index, we now have at least four possible execution plans:
- A range scan on
p (population): "500000000 < Population" - A table scan
- Ref on
c (continent) - Range on
c (continent): "Asia <= Continent <= Asia"
In addition to these plans, we also have the ability to combine the use of index p (population) and c (continent) using index merge, and in OPTIMIZER_TRACE output in Example 9.09 we can see that a range scan on the PRIMARY index was also evaluated but rejected.
I have already described rows_examined_per_scan as showing the selectivity of the index, but there two other statistics to point out as the difference between Examples 7 and 8:
- Key_length. The data type used for continent is 1 byte (enum) versus 4 bytes for population. Provided the selectivity is equal, a shorter key length is better as you can fit more keys per page, and thus get better memory fit for the index.
- Access_type. All things considered equal, the access type of ref is less expensive than range.
Example 9: Optimizer Trace comparing indexes p and c with a large population
SET optimizer_trace="enabled=on";
SELECT * FROM Country WHERE continent='Asia' AND population > 500000000;
SELECT * FROM information_schema.optimizer_trace;
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `Country`.`Code` AS `Code`,`Country`.`Name` AS `Name`,`Country`.`Continent` AS `Continent`,`Country`.`Region` AS `Region`,`Country`.`SurfaceArea` AS `SurfaceArea`,`Country`.`IndepYear` AS `IndepYear`,`Country`.`Population` AS `Population`,`Country`.`LifeExpectancy` AS `LifeExpectancy`,`Country`.`GNP` AS `GNP`,`Country`.`GNPOld` AS `GNPOld`,`Country`.`LocalName` AS `LocalName`,`Country`.`GovernmentForm` AS `GovernmentForm`,`Country`.`HeadOfState` AS `HeadOfState`,`Country`.`Capital` AS `Capital`,`Country`.`Code2` AS `Code2` from `Country` where ((`Country`.`Continent` = 'Asia') and (`Country`.`Population` > 500000000))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`Country`.`Continent` = 'Asia') and (`Country`.`Population` > 500000000))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`Country`.`Population` > 500000000) and multiple equal('Asia', `Country`.`Continent`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`Country`.`Population` > 500000000) and multiple equal('Asia', `Country`.`Continent`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`Country`.`Population` > 500000000) and multiple equal('Asia', `Country`.`Continent`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`Country`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`Country`",
"field": "Continent",
"equals": "'Asia'",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`Country`",
"range_analysis": {
"table_scan": {
"rows": 239,
"cost": 247.1
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false, # A range scan on the primary key
"cause": "not_applicable" # is not possible
},
{
"index": "p",
"usable": true, # A range scan on p is possible
"key_parts": [ # it is evaluated under
"Population", # "analyzing_range_alternatives"
"Code"
]
},
{
"index": "c", # A range scan on c is possible
"usable": true, # it is evaluated under
"key_parts": [ # "analyzing_range_alternatives"
"Continent", # but considered expensive
"Code"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "p",
"ranges": [
"500000000 < Population"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 2,
"cost": 5.01,
"chosen": true
},
{
"index": "c",
"ranges": [
"Asia <= Continent <= Asia"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 51,
"cost": 103.01,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false, # Merging indexes is rejected
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan", # The range optimizer prefers
"index": "p", # a range on p
"rows": 2,
"ranges": [
"500000000 < Population"
]
},
"rows_for_plan": 2,
"cost_for_plan": 5.01,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`Country`", # This is a summary of best
"best_access_path": { # possible access strategies
"considered_access_paths": [ # (from ref and range optimizer)
{
"access_type": "ref", # a ref on c
"index": "c",
"rows": 51,
"cost": 69,
"chosen": true
},
{
"rows_to_scan": 2,
"access_type": "range", # a range on p
"range_details": {
"used_index": "p"
},
"resulting_rows": 2,
"cost": 7.01,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 2,
"cost_for_plan": 7.01,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`Country`.`Continent` = 'Asia') and (`Country`.`Population` > 500000000))",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`Country`",
"attached": "((`Country`.`Continent` = 'Asia') and (`Country`.`Population` > 500000000))"
}
]
}
},
{
"refine_plan": [
{
"table": "`Country`",
"pushed_index_condition": "(`Country`.`Population` > 500000000)",
"table_condition_attached": "(`Country`.`Continent` = 'Asia')"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}