QUERY: explain extended select * from cities where id in (select phonecode from countries group by name)
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"transformations_to_subquery": [
"removed_grouping"
]
},
{
"expanded_query": "/* select#2 */ select `countries`.`phonecode` from `countries`"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"expanded_query": "/* select#1 */ select `cities`.`id` AS `id`,`cities`.`name` AS `name`,`cities`.`state_id` AS `state_id` from `cities` where `cities`.`id` in (/* select#2 */ select `countries`.`phonecode` from `countries`)"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"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 `cities`.`id` AS `id`,`cities`.`name` AS `name`,`cities`.`state_id` AS `state_id` from `cities` semi join (`countries`) where (1 and (`cities`.`id` = `countries`.`phonecode`))"
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(1 and (`cities`.`id` = `countries`.`phonecode`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and multiple equal(`cities`.`id`, `countries`.`phonecode`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and multiple equal(`cities`.`id`, `countries`.`phonecode`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(`cities`.`id`, `countries`.`phonecode`)"
}
]
}
},
{
"table_dependencies": [
{
"table": "`cities`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`countries`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`cities`",
"field": "id",
"equals": "`countries`.`phonecode`",
"null_rejecting": false
}
]
},
{
"pulled_out_semijoin_tables": [
]
},
{
"rows_estimation": [
{
"table": "`cities`",
"table_scan": {
"rows": 47478,
"cost": 161
}
},
{
"table": "`countries`",
"table_scan": {
"rows": 246,
"cost": 1
}
}
]
},
{
"execution_plan_for_potential_materialization": {
"steps": [
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`countries`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 246,
"cost": 50.2,
"chosen": true
}
]
},
"cost_for_plan": 50.2,
"rows_for_plan": 246,
"chosen": true
}
]
}
]
}
},
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`countries`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 246,
"cost": 50.2,
"chosen": true
}
]
},
"cost_for_plan": 50.2,
"rows_for_plan": 246,
"semijoin_strategy_choice": [
{
"strategy": "MaterializeScan",
"choice": "deferred"
}
],
"rest_of_plan": [
{
"plan_prefix": [
"`countries`"
],
"table": "`cities`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 246.2,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 35609,
"cost": 1.75e6,
"chosen": false
}
]
},
"cost_for_plan": 345.4,
"rows_for_plan": 246,
"semijoin_strategy_choice": [
{
"strategy": "MaterializeScan",
"recalculate_access_paths_and_cost": {
"tables": [
{
"table": "`cities`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 246.2,
"chosen": true
},
{
"access_type": "scan",
"using_join_cache": true,
"rows": 35609,
"cost": 1.75e6,
"chosen": false
}
]
}
}
]
},
"cost": 445.8,
"rows": 1,
"duplicate_tables_left": true,
"chosen": true
},
{
"strategy": "DuplicatesWeedout",
"cost": 396.8,
"rows": 1,
"duplicate_tables_left": false,
"chosen": true
}
],
"chosen": true
}
]
},
{
"plan_prefix": [
],
"table": "`cities`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"rows": 47478,
"cost": 9656.6,
"chosen": true
}
]
},
"cost_for_plan": 9656.6,
"rows_for_plan": 47478,
"semijoin_strategy_choice": [
],
"pruned_by_cost": true
},
{
"final_semijoin_strategy": "DuplicateWeedout"
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`cities`.`id` = `countries`.`phonecode`)",
"attached_conditions_computation": [
],
"attached_conditions_summary": [
{
"table": "`countries`",
"attached": null
},
{
"table": "`cities`",
"attached": null
}
]
}
},
{
"refine_plan": [
{
"creating_tmp_table": {
"tmp_table_info": {
"row_length": 6,
"key_length": 4,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 2796202
}
}
},
{
"table": "`countries`",
"access_type": "table_scan"
},
{
"table": "`cities`"
}
]
}
]
}
},
{
"join_explain": {
"select#": 1,
"steps": [
]
}
}
]
}