Subqueries¶
The optimizer has a number of execution strategies available for optimizing subqueries, including rewriting the query to a join, a semi-join, and materialization. The strategy used will depend on the type and placement of the subquery.
Scalar Subquery¶
A Scalar subquery is one where the subquery returns exactly one row, and can be optimized away and cached during execution. In Example 13 we can see an example of a scalar subquery trying to find the CountryCode for the city of Toronto. It is important to identify that the optimizer sees this as two queries; with a cost of 1.00 and 4213.00, respectively.
The second query (select_id: 2) is missing an index, and it is thus performing a table scan. I can see this because the column mentioned in the attached_condition (`City`.`Name`) is not indexed. After adding the index, the query is now optimized.
Example 13: A scalar subquery
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE Code = (SELECT CountryCode FROM City WHERE name='Toronto');
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "Country",
"access_type": "ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"Code"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "264"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
],
"attached_condition": "(`world`.`Country`.`Code` = (/* select#2 */ select `world`.`City`.`CountryCode` from `world`.`City` where (`world`.`City`.`Name` = 'Toronto')))",
"attached_subqueries": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "862.60"
},
"table": {
"table_name": "City",
"access_type": "ALL",
"rows_examined_per_scan": 4188,
"rows_produced_per_join": 418,
"filtered": "10.00",
"cost_info": {
"read_cost": "778.84",
"eval_cost": "83.76",
"prefix_cost": "862.60",
"data_read_per_join": "29K"
},
"used_columns": [
"Name",
"CountryCode"
],
"attached_condition": "(`world`.`City`.`Name` = 'Toronto')"
}
}
}
]
}
}
}
Example 14: Adding an index to improve the scalar subquery
# Add an Index
ALTER TABLE City ADD INDEX n (Name);
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE Code = (SELECT CountryCode FROM City WHERE name='Toronto');
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.00"
},
"table": {
"table_name": "Country",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"Code"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "1.00",
"prefix_cost": "0.00",
"data_read_per_join": "264"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
]
},
"optimized_away_subqueries": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "2.00"
},
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"n"
],
"key": "n",
"used_key_parts": [
"Name"
],
"key_length": "35",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "1.00",
"prefix_cost": "2.00",
"data_read_per_join": "72"
},
"used_columns": [
"Name",
"CountryCode"
]
}
}
}
]
}
}
IN Subquery (Unique)¶
Example 15 shows a subquery where the row returned is the primary key, so the results are guaranteed to be distinct. Because of this, this subquery can safely be transformed to an INNER JOIN query and return the same results. By executing SHOW WARNINGS after EXPLAIN we can see that this is indeed the case:
/* select#1 */ select `world`.`City`.`ID` AS `ID`,`world`.`City`.`Name` AS `Name`,`world`.`City`.`CountryCode` AS `CountryCode`,`world`.`City`.`District` AS `District`,`world`.`City`.`Population` AS `Population` from `world`.`Country` join `world`.`City` where ((`world`.`City`.`CountryCode` = `world`.`Country`.`Code`) and (`world`.`Country`.`Continent` = 'Asia'))
1 row in set (0.00 sec)
This subquery is moderately efficient. We can see that it is accessing the Country table first (using the index only; a covering index), and then for each row that matches, a set of rows is looked up in the City table using the index c (CountryCode).
Example 15: A transformable IN subquery
EXPLAIN FORMAT=JSON
SELECT * FROM City WHERE CountryCode IN (SELECT Code FROM Country WHERE Continent = 'Asia');
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1893.30"
},
"nested_loop": [
{
"table": {
"table_name": "Country",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"c"
],
"key": "c",
"used_key_parts": [
"Continent"
],
"key_length": "1",
"ref": [
"const"
],
"rows_examined_per_scan": 51,
"rows_produced_per_join": 51,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.02",
"eval_cost": "51.00",
"prefix_cost": "52.02",
"data_read_per_join": "13K"
},
"used_columns": [
"Code",
"Continent"
]
}
},
{
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode"
],
"key": "CountryCode",
"used_key_parts": [
"CountryCode"
],
"key_length": "3",
"ref": [
"world.Country.Code"
],
"rows_examined_per_scan": 18,
"rows_produced_per_join": 920,
"filtered": "100.00",
"cost_info": {
"read_cost": "920.64",
"eval_cost": "920.64",
"prefix_cost": "1893.30",
"data_read_per_join": "64K"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
]
}
}
]
}
}
IN Subquery (Non-Unique)¶
In Example 15, the subquery was rewritten as an INNER JOIN because it already returned a distinct set of rows. When subqueries are not unique, the MySQL optimizer has to choose a different strategy.
In Example 16 the subquery is trying to find countries which have an at least one official language. Since some countries have more than one official language, the result of the subquery is not unique.
The output from OPTIMIZER_TRACE (Example 17) shows that the optimizer identified that the query could not be rewritten directly to a join, and instead required a ‘semi-join.’ The optimizer has several strategies to execute the semi-join (FirstMatch, MaterializeLookup, DuplicatesWeedout). In this case, it decided the materializing (creating a buffer to store a temporary result) was the lowest cost way to execute this query.
Example 16: A subquery that cannot be rewritten to an INNER JOIN
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE Code IN (SELECT CountryCode FROM CountryLanguage WHERE isOfficial=1);
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "407.80"
},
"nested_loop": [
{
"table": {
"table_name": "Country",
"access_type": "ALL",
"possible_keys": [
"PRIMARY"
],
"rows_examined_per_scan": 239,
"rows_produced_per_join": 239,
"filtered": "100.00",
"cost_info": {
"read_cost": "9.00",
"eval_cost": "47.80",
"prefix_cost": "56.80",
"data_read_per_join": "61K"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
],
"attached_condition": "(`world`.`Country`.`Code` is not null)"
}
},
{
"table": {
"table_name": "<subquery2>",
"access_type": "eq_ref",
"key": "<auto_key>",
"key_length": "3",
"ref": [
"world.Country.Code"
],
"rows_examined_per_scan": 1,
"materialized_from_subquery": {
"using_temporary_table": true,
"query_block": {
"table": {
"table_name": "CountryLanguage",
"access_type": "ALL",
"possible_keys": [
"PRIMARY",
"CountryCode"
],
"rows_examined_per_scan": 984,
"rows_produced_per_join": 492,
"filtered": "50.00",
"cost_info": {
"read_cost": "104.40",
"eval_cost": "98.40",
"prefix_cost": "202.80",
"data_read_per_join": "19K"
},
"used_columns": [
"CountryCode",
"IsOfficial"
],
"attached_condition": "(`world`.`CountryLanguage`.`IsOfficial` = 1)"
}
}
}
}
}
]
}
}
Example 17: Analyzing the semi-join strategy for the subquery
SET OPTIMIZER_TRACE="enabled=on";
SET optimizer_trace_max_mem_size = 1024 * 1024;
SELECT * FROM Country WHERE Code IN (SELECT CountryCode FROM CountryLanguage WHERE isOfficial=1);
SELECT * FROM information_schema.optimizer_trace;
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `CountryLanguage`.`CountryCode` from `CountryLanguage` where (`CountryLanguage`.`IsOfficial` = 1)"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"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`.`Code` in (/* select#2 */ select `CountryLanguage`.`CountryCode` from `CountryLanguage` where (`CountryLanguage`.`IsOfficial` = 1))"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformations_to_nested_joins": {
"transformations": [
"semijoin"
],
"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` semi join (`CountryLanguage`) where (1 and (`CountryLanguage`.`IsOfficial` = 1) and (`Country`.`Code` = `CountryLanguage`.`CountryCode`))"
}
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(1 and (`CountryLanguage`.`IsOfficial` = 1) and (`Country`.`Code` = `CountryLanguage`.`CountryCode`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and (`CountryLanguage`.`IsOfficial` = 1) and multiple equal(`Country`.`Code`, `CountryLanguage`.`CountryCode`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and (`CountryLanguage`.`IsOfficial` = 1) and multiple equal(`Country`.`Code`, `CountryLanguage`.`CountryCode`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`CountryLanguage`.`IsOfficial` = 1) and multiple equal(`Country`.`Code`, `CountryLanguage`.`CountryCode`))"
}
]
}
},
{
"substitute_generated_columns": {
}
},
{
"table_dependencies": [
{
"table": "`Country`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`CountryLanguage`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`Country`",
"field": "Code",
"equals": "`CountryLanguage`.`CountryCode`",
"null_rejecting": false
},
{
"table": "`CountryLanguage`",
"field": "CountryCode",
"equals": "`Country`.`Code`",
"null_rejecting": false
},
{
"table": "`CountryLanguage`",
"field": "CountryCode",
"equals": "`Country`.`Code`",
"null_rejecting": false
}
]
},
{
"pulled_out_semijoin_tables": [
]
},
{
"rows_estimation": [
{
"table": "`Country`",
"table_scan": {
"rows": 239,
"cost": 9
}
},
{
"table": "`CountryLanguage`",
"table_scan": {
"rows": 984,
"cost": 6
}
}
]
},
{
"execution_plan_for_potential_materialization": {
"steps": [
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "CountryCode",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 984,
"access_type": "scan",
"resulting_rows": 492,
"cost": 202.8,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 492,
"cost_for_plan": 202.8,
"chosen": true
}
]
}
]
}
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 239,
"access_type": "scan",
"resulting_rows": 239,
"cost": 56.8,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 239,
"cost_for_plan": 56.8,
"semijoin_strategy_choice": [
],
"rest_of_plan": [
{
"plan_prefix": [
"`Country`"
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 4.2232,
"cost": 442.83,
"chosen": true
},
{
"access_type": "ref",
"index": "CountryCode",
"rows": 4.2232,
"cost": 1211.2,
"chosen": false
},
{
"rows_to_scan": 984,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 492,
"cost": 23647,
"chosen": false
}
]
},
"condition_filtering_pct": 50,
"rows_for_plan": 504.67,
"cost_for_plan": 499.63,
"semijoin_strategy_choice": [
{
"strategy": "FirstMatch",
"recalculate_access_paths_and_cost": {
"tables": [
]
},
"cost": 499.63,
"rows": 239,
"chosen": true
},
{
"strategy": "MaterializeLookup",
"cost": 407.8, # Materialize has the lowest cost
"rows": 239,
"duplicate_tables_left": false,
"chosen": true
},
{
"strategy": "DuplicatesWeedout",
"cost": 650.36,
"rows": 239,
"duplicate_tables_left": false,
"chosen": false
}
],
"chosen": true
}
]
},
{
"plan_prefix": [
],
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "CountryCode",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 984,
"access_type": "scan",
"resulting_rows": 492,
"cost": 202.8,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 492,
"cost_for_plan": 202.8,
"semijoin_strategy_choice": [
{
"strategy": "MaterializeScan",
"choice": "deferred"
}
],
"rest_of_plan": [
{
"plan_prefix": [
"`CountryLanguage`"
],
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 590.4,
"chosen": true
},
{
"rows_to_scan": 239,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 239,
"cost": 23527,
"chosen": false
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 492,
"cost_for_plan": 793.2,
"semijoin_strategy_choice": [
{
"strategy": "LooseScan",
"recalculate_access_paths_and_cost": {
"tables": [
{
"table": "`CountryLanguage`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "CountryCode",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 984,
"access_type": "scan",
"resulting_rows": 492,
"cost": 202.8,
"chosen": true
}
]
},
"unknown_key_1": {
"searching_loose_scan_index": {
"indexes": [
{
"index": "PRIMARY",
"ref_possible": false,
"covering_scan_possible": false
},
{
"index": "CountryCode",
"ref_possible": false,
"covering_scan_possible": false
}
]
}
}
}
]
},
"chosen": false
},
{
"strategy": "MaterializeScan",
"recalculate_access_paths_and_cost": {
"tables": [
{
"table": "`Country`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 590.4,
"chosen": true
},
{
"rows_to_scan": 239,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 1,
"resulting_rows": 239,
"cost": 23527,
"chosen": false
}
]
}
}
]
},
"cost": 992,
"rows": 1,
"duplicate_tables_left": true,
"chosen": true
},
{
"strategy": "DuplicatesWeedout",
"cost": 941.4,
"rows": 239,
"duplicate_tables_left": false,
"chosen": true
}
],
"pruned_by_cost": true
}
]
},
{ # Materialize is finally chosen
"final_semijoin_strategy": "MaterializeLookup"
}
]
},
{
"creating_tmp_table": {
"tmp_table_info": {
"row_length": 4,
"key_length": 3,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 4194304
}
}
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`<subquery2>`.`CountryCode` = `Country`.`Code`) and (`CountryLanguage`.`IsOfficial` = 1))",
"attached_conditions_computation": [
{
"table": "`CountryLanguage`",
"rechecking_index_usage": {
"recheck_reason": "not_first_table",
"range_analysis": {
"table_scan": {
"rows": 984,
"cost": 204.9
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"CountryCode",
"Language"
]
},
{
"index": "CountryCode",
"usable": true,
"key_parts": [
"CountryCode",
"Language"
]
}
],
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
}
}
}
}
],
"attached_conditions_summary": [
{
"table": "`Country`",
"attached": "(`Country`.`Code` is not null)"
},
{
"table": "``.`<subquery2>`",
"attached": null
},
{
"table": "`CountryLanguage`",
"attached": "(`CountryLanguage`.`IsOfficial` = 1)"
}
]
}
},
{
"refine_plan": [
{
"table": "`Country`"
},
{
"table": "``.`<subquery2>`"
},
{
"table": "`CountryLanguage`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": [
]
}
}
]
}
NOT IN Subquery¶
A NOT IN subquery can use either a materialization or an exists strategy. To describe the difference between the two, consider the following two examples:
SELECT * FROM City WHERE CountryCode NOT IN (SELECT code FROM Country);SELECT * FROM City WHERE CountryCode NOT IN (SELECT code FROM Country WHERE continent IN ('Asia', 'Europe', 'North America'));
In the first query, the inner subquery is more or less in its most optimal form. The column code is the primary key of the table, and a unique set of values can be retrieved via an index scan. The only downside (if there is one), is index scanning the primary key may be slower than index scanning a secondary key, since the primary key is wide from having the row-values. EXPLAIN confirms that the optimizer did choose a materialization strategy for this query, but this can be overwritten with a hint:
SELECT * FROM City WHERE CountryCode NOT IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ code FROM Country);
In the second query, there is an additional predicate: continent IN ('Asia', 'Europe', 'North America')). Since each row in the City table needs to be compared to the not in query, it makes sense to materialize and cache the list of rows that match. This saves having to repeat the process of checking the predicate for each row in the City table.
Example 18: A NOT IN Subquery using materialization
EXPLAIN FORMAT=JSON
SELECT * FROM City WHERE CountryCode NOT IN (SELECT code FROM Country WHERE continent IN ('Asia', 'Europe', 'North America'));
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "862.60"
},
"table": {
"table_name": "City",
"access_type": "ALL",
"rows_examined_per_scan": 4188,
"rows_produced_per_join": 4188,
"filtered": "100.00",
"cost_info": {
"read_cost": "25.00",
"eval_cost": "837.60",
"prefix_cost": "862.60",
"data_read_per_join": "294K"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
],
"attached_condition": "(not(<in_optimizer>(`world`.`City`.`CountryCode`,`world`.`City`.`CountryCode` in ( <materialize> (/* select#2 */ select `world`.`Country`.`Code` from `world`.`Country` where (`world`.`Country`.`Continent` in ('Asia','Europe','North America')) ), <primary_index_lookup>(`world`.`City`.`CountryCode` in <temporary table> on <auto_key> where ((`world`.`City`.`CountryCode` = `materialized-subquery`.`code`)))))))",
"attached_subqueries": [
{
"table": {
"table_name": "<materialized_subquery>",
"access_type": "eq_ref",
"key": "<auto_key>",
"key_length": "3",
"rows_examined_per_scan": 1,
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "54.67"
},
"table": {
"table_name": "Country",
"access_type": "range",
"possible_keys": [
"PRIMARY",
"c",
"c_p"
],
"key": "c",
"used_key_parts": [
"Continent"
],
"key_length": "1",
"rows_examined_per_scan": 134,
"rows_produced_per_join": 134,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "27.87",
"eval_cost": "26.80",
"prefix_cost": "54.67",
"data_read_per_join": "34K"
},
"used_columns": [
"Code",
"Continent"
],
"attached_condition": "(`world`.`Country`.`Continent` in ('Asia','Europe','North America'))"
}
}
}
}
}
]
}
}
}
Derived Table¶
A subquery in the from clause does not need to be materialized. MySQL can usually ‘merge’ it back in, similar to the way a view merges its definitions with the predicates that you will use in queries. Example 19 shows a derived query being merged into the outer query.
Example 19: A derived table being ‘merged’ back in
EXPLAIN FORMAT=JSON
SELECT * FROM Country, (SELECT * FROM City WHERE CountryCode ='CAN' ) as CityTmp WHERE Country.code=CityTmp.CountryCode
AND CityTmp.name ='Toronto';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.00"
},
"nested_loop": [
{
"table": {
"table_name": "Country",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"Code"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "1.00",
"prefix_cost": "0.00",
"data_read_per_join": "264"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
]
}
},
{
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode",
"n"
],
"key": "n",
"used_key_parts": [
"Name"
],
"key_length": "35",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "5.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.05",
"prefix_cost": "2.00",
"data_read_per_join": "3"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
],
"attached_condition": "(`world`.`City`.`CountryCode` = 'CAN')"
}
}
]
}
}
The potential downside of this ‘merge’ is that it makes some queries no longer legal. If you receive a warning when upgrading, you can opt to disable the derived_merge optimization. This will result in an increase in query cost because materialization can be expensive.
Example 20: Disabling merging and triggering a materialization
SET optimizer_switch='derived_merge=off';
EXPLAIN FORMAT=JSON
SELECT * FROM Country, (SELECT * FROM City WHERE CountryCode ='CAN' ) as CityTmp WHERE Country.code=CityTmp.CountryCode
AND CityTmp.name ='Toronto';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "19.90"
},
"nested_loop": [
{
"table": {
"table_name": "CityTmp",
"access_type": "ref",
"possible_keys": [
"<auto_key0>"
],
"key": "<auto_key0>",
"used_key_parts": [
"Name"
],
"key_length": "35",
"ref": [
"const"
],
"rows_examined_per_scan": 5,
"rows_produced_per_join": 5,
"filtered": "100.00",
"cost_info": {
"read_cost": "4.90",
"eval_cost": "5.00",
"prefix_cost": "9.90",
"data_read_per_join": "360"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
],
"materialized_from_subquery": {
"using_temporary_table": true,
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "98.00"
},
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"CountryCode"
],
"key": "CountryCode",
"used_key_parts": [
"CountryCode"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 49,
"rows_produced_per_join": 49,
"filtered": "100.00",
"cost_info": {
"read_cost": "49.00",
"eval_cost": "49.00",
"prefix_cost": "98.00",
"data_read_per_join": "3K"
},
"used_columns": [
"ID",
"Name",
"CountryCode",
"District",
"Population"
]
}
}
}
}
},
{
"table": {
"table_name": "Country",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"Code"
],
"key_length": "3",
"ref": [
"CityTmp.CountryCode"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 5,
"filtered": "100.00",
"cost_info": {
"read_cost": "5.00",
"eval_cost": "5.00",
"prefix_cost": "19.90",
"data_read_per_join": "1K"
},
"used_columns": [
"Code",
"Name",
"Continent",
"Region",
"SurfaceArea",
"IndepYear",
"Population",
"LifeExpectancy",
"GNP",
"GNPOld",
"LocalName",
"GovernmentForm",
"HeadOfState",
"Capital",
"Code2"
]
}
}
]
}
}