Created
January 19, 2024 19:47
-
-
Save ronaldbradford/14f3e253342da7c7ce64756976b690f7 to your computer and use it in GitHub Desktop.
Full MySQL optimizer trace for "How to capture supporting information with your SQL statements"
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"steps": [ | |
{ | |
"join_preparation": { | |
"select#": 1, | |
"steps": [ | |
{ | |
"expanded_query": "/* select#1 */ select `a`.`country_code` AS `country_code`,`c`.`name` AS `name`,count(0) AS `cnt` from (`airport` `a` join `country` `c` on((`a`.`country_code` = `c`.`country_code`))) group by `a`.`country_code`,`c`.`name` order by count(0) desc limit 10" | |
}, | |
{ | |
"transformations_to_nested_joins": { | |
"transformations": [ | |
"JOIN_condition_to_WHERE", | |
"parenthesis_removal" | |
], | |
"expanded_query": "/* select#1 */ select `a`.`country_code` AS `country_code`,`c`.`name` AS `name`,count(0) AS `cnt` from `airport` `a` join `country` `c` where (`a`.`country_code` = `c`.`country_code`) group by `a`.`country_code`,`c`.`name` order by count(0) desc limit 10" | |
} | |
} | |
] | |
} | |
}, | |
{ | |
"join_optimization": { | |
"select#": 1, | |
"steps": [ | |
{ | |
"condition_processing": { | |
"condition": "WHERE", | |
"original_condition": "(`a`.`country_code` = `c`.`country_code`)", | |
"steps": [ | |
{ | |
"transformation": "equality_propagation", | |
"resulting_condition": "multiple equal(`a`.`country_code`, `c`.`country_code`)" | |
}, | |
{ | |
"transformation": "constant_propagation", | |
"resulting_condition": "multiple equal(`a`.`country_code`, `c`.`country_code`)" | |
}, | |
{ | |
"transformation": "trivial_condition_removal", | |
"resulting_condition": "multiple equal(`a`.`country_code`, `c`.`country_code`)" | |
} | |
] | |
} | |
}, | |
{ | |
"substitute_generated_columns": { | |
} | |
}, | |
{ | |
"table_dependencies": [ | |
{ | |
"table": "`airport` `a`", | |
"row_may_be_null": false, | |
"map_bit": 0, | |
"depends_on_map_bits": [ | |
] | |
}, | |
{ | |
"table": "`country` `c`", | |
"row_may_be_null": false, | |
"map_bit": 1, | |
"depends_on_map_bits": [ | |
] | |
} | |
] | |
}, | |
{ | |
"ref_optimizer_key_uses": [ | |
{ | |
"table": "`airport` `a`", | |
"field": "country_code", | |
"equals": "`c`.`country_code`", | |
"null_rejecting": true | |
}, | |
{ | |
"table": "`country` `c`", | |
"field": "country_code", | |
"equals": "`a`.`country_code`", | |
"null_rejecting": true | |
} | |
] | |
}, | |
{ | |
"rows_estimation": [ | |
{ | |
"table": "`airport` `a`", | |
"table_scan": { | |
"rows": 86733, | |
"cost": 296.25 | |
} | |
}, | |
{ | |
"table": "`country` `c`", | |
"table_scan": { | |
"rows": 248, | |
"cost": 1 | |
} | |
} | |
] | |
}, | |
{ | |
"considered_execution_plans": [ | |
{ | |
"plan_prefix": [ | |
], | |
"table": "`country` `c`", | |
"best_access_path": { | |
"considered_access_paths": [ | |
{ | |
"access_type": "ref", | |
"index": "PRIMARY", | |
"usable": false, | |
"chosen": false | |
}, | |
{ | |
"rows_to_scan": 248, | |
"filtering_effect": [ | |
], | |
"final_filtering_effect": 1, | |
"access_type": "scan", | |
"resulting_rows": 248, | |
"cost": 25.8, | |
"chosen": true | |
} | |
] | |
}, | |
"condition_filtering_pct": 100, | |
"rows_for_plan": 248, | |
"cost_for_plan": 25.8, | |
"rest_of_plan": [ | |
{ | |
"plan_prefix": [ | |
"`country` `c`" | |
], | |
"table": "`airport` `a`", | |
"best_access_path": { | |
"considered_access_paths": [ | |
{ | |
"access_type": "ref", | |
"index": "country_code", | |
"rows": 405.294, | |
"cost": 35179.6, | |
"chosen": true | |
}, | |
{ | |
"rows_to_scan": 86733, | |
"filtering_effect": [ | |
], | |
"final_filtering_effect": 1, | |
"access_type": "scan", | |
"using_join_cache": true, | |
"buffers_needed": 1, | |
"resulting_rows": 86733, | |
"cost": 2.15133e+06, | |
"chosen": false | |
} | |
] | |
}, | |
"condition_filtering_pct": 100, | |
"rows_for_plan": 100513, | |
"cost_for_plan": 35205.4, | |
"chosen": true | |
} | |
] | |
}, | |
{ | |
"plan_prefix": [ | |
], | |
"table": "`airport` `a`", | |
"best_access_path": { | |
"considered_access_paths": [ | |
{ | |
"access_type": "ref", | |
"index": "country_code", | |
"usable": false, | |
"chosen": false | |
}, | |
{ | |
"rows_to_scan": 86733, | |
"filtering_effect": [ | |
], | |
"final_filtering_effect": 1, | |
"access_type": "scan", | |
"resulting_rows": 86733, | |
"cost": 8969.55, | |
"chosen": true | |
} | |
] | |
}, | |
"condition_filtering_pct": 100, | |
"rows_for_plan": 86733, | |
"cost_for_plan": 8969.55, | |
"rest_of_plan": [ | |
{ | |
"plan_prefix": [ | |
"`airport` `a`" | |
], | |
"table": "`country` `c`", | |
"best_access_path": { | |
"considered_access_paths": [ | |
{ | |
"access_type": "eq_ref", | |
"index": "PRIMARY", | |
"rows": 1, | |
"cost": 30356.6, | |
"chosen": true, | |
"cause": "clustered_pk_chosen_by_heuristics" | |
}, | |
{ | |
"rows_to_scan": 248, | |
"filtering_effect": [ | |
], | |
"final_filtering_effect": 1, | |
"access_type": "scan", | |
"using_join_cache": true, | |
"buffers_needed": 136, | |
"resulting_rows": 248, | |
"cost": 2.15112e+06, | |
"chosen": false | |
} | |
] | |
}, | |
"condition_filtering_pct": 100, | |
"rows_for_plan": 86733, | |
"cost_for_plan": 39326.1, | |
"pruned_by_cost": true | |
} | |
] | |
} | |
] | |
}, | |
{ | |
"attaching_conditions_to_tables": { | |
"original_condition": "(`a`.`country_code` = `c`.`country_code`)", | |
"attached_conditions_computation": [ | |
], | |
"attached_conditions_summary": [ | |
{ | |
"table": "`country` `c`", | |
"attached": null | |
}, | |
{ | |
"table": "`airport` `a`", | |
"attached": "(`a`.`country_code` = `c`.`country_code`)" | |
} | |
] | |
} | |
}, | |
{ | |
"optimizing_distinct_group_by_order_by": { | |
"simplifying_order_by": { | |
"original_clause": "count(0) desc", | |
"items": [ | |
{ | |
"item": "count(0)" | |
} | |
], | |
"resulting_clause_is_simple": false, | |
"resulting_clause": "count(0) desc" | |
}, | |
"simplifying_group_by": { | |
"original_clause": "`a`.`country_code`,`c`.`name`", | |
"items": [ | |
{ | |
"item": "`a`.`country_code`" | |
}, | |
{ | |
"item": "`c`.`name`" | |
} | |
], | |
"resulting_clause_is_simple": false, | |
"resulting_clause": "`a`.`country_code`,`c`.`name`" | |
} | |
} | |
}, | |
{ | |
"finalizing_table_conditions": [ | |
{ | |
"table": "`airport` `a`", | |
"original_table_condition": "(`a`.`country_code` = `c`.`country_code`)", | |
"final_table_condition ": null | |
} | |
] | |
}, | |
{ | |
"refine_plan": [ | |
{ | |
"table": "`country` `c`" | |
}, | |
{ | |
"table": "`airport` `a`" | |
} | |
] | |
}, | |
{ | |
"considering_tmp_tables": [ | |
{ | |
"adding_tmp_table_in_plan_at_position": 2, | |
"write_method": "continuously_update_group_row" | |
}, | |
{ | |
"adding_sort_to_table": "" | |
} | |
] | |
} | |
] | |
} | |
}, | |
{ | |
"join_execution": { | |
"select#": 1, | |
"steps": [ | |
{ | |
"temp_table_aggregate": { | |
"select#": 1, | |
"steps": [ | |
{ | |
"creating_tmp_table": { | |
"tmp_table_info": { | |
"in_plan_at_position": 2, | |
"columns": 3, | |
"row_length": 218, | |
"key_length": 210, | |
"unique_constraint": false, | |
"makes_grouped_rows": true, | |
"cannot_insert_duplicates": false, | |
"location": "TempTable" | |
} | |
} | |
} | |
] | |
} | |
}, | |
{ | |
"sorting_table": "<temporary>", | |
"filesort_information": [ | |
{ | |
"direction": "desc", | |
"expression": "`cnt`" | |
} | |
], | |
"filesort_priority_queue_optimization": { | |
"limit": 10, | |
"chosen": true | |
}, | |
"filesort_execution": [ | |
], | |
"filesort_summary": { | |
"memory_available": 262144, | |
"key_size": 8, | |
"row_size": 226, | |
"max_rows_per_buffer": 11, | |
"num_rows_estimate": 18446744073709551615, | |
"num_rows_found": 245, | |
"num_initial_chunks_spilled_to_disk": 0, | |
"peak_memory_used": 2574, | |
"sort_algorithm": "std::sort", | |
"unpacked_addon_fields": "using_priority_queue", | |
"sort_mode": "<fixed_sort_key, additional_fields>" | |
} | |
} | |
] | |
} | |
} | |
] | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment