Created
November 2, 2020 02:03
-
-
Save mdcallag/0909abdcc5d3f021a2576960584b384d to your computer and use it in GitHub Desktop.
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
| mysql> select * from information_schema.optimizer_trace\G | |
| *************************** 1. row *************************** | |
| QUERY: SELECT * FROM cpu WHERE usage_user > 90.0 and time >= '2016-01-01 00:26:02' AND time < '2016-01-01 12:26:02' AND tags_id IN (SELECT id FROM tags WHERE hostname IN ('host_2')) | |
| TRACE: { | |
| "steps": [ | |
| { | |
| "join_preparation": { | |
| "select#": 1, | |
| "steps": [ | |
| { | |
| "join_preparation": { | |
| "select#": 2, | |
| "steps": [ | |
| { | |
| "expanded_query": "/* select#2 */ select `tags`.`id` from `tags` where (`tags`.`hostname` = 'host_2')" | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| "expanded_query": "/* select#1 */ select `cpu`.`time` AS `time`,`cpu`.`tags_id` AS `tags_id`,`cpu`.`additional_tags` AS `additional_tags`,`cpu`.`usage_user` AS `usage_user`,`cpu`.`usage_system` AS `usage_system`,`cpu`.`usage_idle` AS `usage_idle`,`cpu`.`usage_nice` AS `usage_nice`,`cpu`.`usage_iowait` AS `usage_iowait`,`cpu`.`usage_irq` AS `usage_irq`,`cpu`.`usage_softirq` AS `usage_softirq`,`cpu`.`usage_steal` AS `usage_steal`,`cpu`.`usage_guest` AS `usage_guest`,`cpu`.`usage_guest_nice` AS `usage_guest_nice` from `cpu` where ((`cpu`.`usage_user` > 90.0) and (`cpu`.`time` >= '2016-01-01 00:26:02') and (`cpu`.`time` < '2016-01-01 12:26:02') and `cpu`.`tags_id` in (/* select#2 */ select `tags`.`id` from `tags` where (`tags`.`hostname` = 'host_2')))" | |
| }, | |
| { | |
| "transformation": { | |
| "select#": 2, | |
| "from": "IN (SELECT)", | |
| "to": "semijoin", | |
| "chosen": true, | |
| "transformation_to_semi_join": { | |
| "subquery_predicate": "`cpu`.`tags_id` in (/* select#2 */ select `tags`.`id` from `tags` where (`tags`.`hostname` = 'host_2'))", | |
| "embedded in": "WHERE", | |
| "evaluating_constant_semijoin_conditions": [ | |
| ], | |
| "semi-join condition": "((`tags`.`hostname` = 'host_2') and (`cpu`.`tags_id` = `tags`.`id`))", | |
| "decorrelated_predicates": [ | |
| { | |
| "outer": "`cpu`.`tags_id`", | |
| "inner": "`tags`.`id`" | |
| } | |
| ] | |
| } | |
| } | |
| }, | |
| { | |
| "transformations_to_nested_joins": { | |
| "transformations": [ | |
| "semijoin" | |
| ], | |
| "expanded_query": "/* select#1 */ select `cpu`.`time` AS `time`,`cpu`.`tags_id` AS `tags_id`,`cpu`.`additional_tags` AS `additional_tags`,`cpu`.`usage_user` AS `usage_user`,`cpu`.`usage_system` AS `usage_system`,`cpu`.`usage_idle` AS `usage_idle`,`cpu`.`usage_nice` AS `usage_nice`,`cpu`.`usage_iowait` AS `usage_iowait`,`cpu`.`usage_irq` AS `usage_irq`,`cpu`.`usage_softirq` AS `usage_softirq`,`cpu`.`usage_steal` AS `usage_steal`,`cpu`.`usage_guest` AS `usage_guest`,`cpu`.`usage_guest_nice` AS `usage_guest_nice` from `cpu` semi join (`tags`) where ((`cpu`.`usage_user` > 90.0) and (`cpu`.`time` >= '2016-01-01 00:26:02') and (`cpu`.`time` < '2016-01-01 12:26:02') and (`tags`.`hostname` = 'host_2') and (`cpu`.`tags_id` = `tags`.`id`))" | |
| } | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| "join_optimization": { | |
| "select#": 1, | |
| "steps": [ | |
| { | |
| "condition_processing": { | |
| "condition": "WHERE", | |
| "original_condition": "((`cpu`.`usage_user` > 90.0) and (`cpu`.`time` >= '2016-01-01 00:26:02') and (`cpu`.`time` < '2016-01-01 12:26:02') and (`tags`.`hostname` = 'host_2') and (`cpu`.`tags_id` = `tags`.`id`))", | |
| "steps": [ | |
| { | |
| "transformation": "equality_propagation", | |
| "resulting_condition": "((`cpu`.`usage_user` > 90.0) and (`cpu`.`time` >= '2016-01-01 00:26:02') and (`cpu`.`time` < '2016-01-01 12:26:02') and multiple equal('host_2', `tags`.`hostname`) and multiple equal(`cpu`.`tags_id`, `tags`.`id`))" | |
| }, | |
| { | |
| "transformation": "constant_propagation", | |
| "resulting_condition": "((`cpu`.`usage_user` > 90.0) and (`cpu`.`time` >= '2016-01-01 00:26:02') and (`cpu`.`time` < '2016-01-01 12:26:02') and multiple equal('host_2', `tags`.`hostname`) and multiple equal(`cpu`.`tags_id`, `tags`.`id`))" | |
| }, | |
| { | |
| "transformation": "trivial_condition_removal", | |
| "resulting_condition": "((`cpu`.`usage_user` > 90) and (`cpu`.`time` >= TIMESTAMP'2016-01-01 00:26:02') and (`cpu`.`time` < TIMESTAMP'2016-01-01 12:26:02') and multiple equal('host_2', `tags`.`hostname`) and multiple equal(`cpu`.`tags_id`, `tags`.`id`))" | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| "substitute_generated_columns": { | |
| } | |
| }, | |
| { | |
| "table_dependencies": [ | |
| { | |
| "table": "`cpu`", | |
| "row_may_be_null": false, | |
| "map_bit": 0, | |
| "depends_on_map_bits": [ | |
| ] | |
| }, | |
| { | |
| "table": "`tags`", | |
| "row_may_be_null": false, | |
| "map_bit": 1, | |
| "depends_on_map_bits": [ | |
| ] | |
| } | |
| ] | |
| }, | |
| { | |
| "ref_optimizer_key_uses": [ | |
| { | |
| "table": "`cpu`", | |
| "field": "tags_id", | |
| "equals": "`tags`.`id`", | |
| "null_rejecting": false | |
| }, | |
| { | |
| "table": "`tags`", | |
| "field": "id", | |
| "equals": "`cpu`.`tags_id`", | |
| "null_rejecting": false | |
| }, | |
| { | |
| "table": "`tags`", | |
| "field": "hostname", | |
| "equals": "'host_2'", | |
| "null_rejecting": false | |
| }, | |
| { | |
| "table": "`tags`", | |
| "field": "hostname", | |
| "equals": "'host_2'", | |
| "null_rejecting": false | |
| }, | |
| { | |
| "table": "`tags`", | |
| "field": "id", | |
| "equals": "`cpu`.`tags_id`", | |
| "null_rejecting": false | |
| } | |
| ] | |
| }, | |
| { | |
| "pulled_out_semijoin_tables": [ | |
| { | |
| "table": "`tags`", | |
| "functionally_dependent": true | |
| } | |
| ] | |
| }, | |
| { | |
| "rows_estimation": [ | |
| { | |
| "table": "`cpu`", | |
| "range_analysis": { | |
| "table_scan": { | |
| "rows": 257280, | |
| "cost": 26323 | |
| }, | |
| "potential_range_indexes": [ | |
| { | |
| "index": "x_tags_time", | |
| "usable": false, | |
| "cause": "not_applicable" | |
| }, | |
| { | |
| "index": "x_time", | |
| "usable": true, | |
| "key_parts": [ | |
| "time" | |
| ] | |
| } | |
| ], | |
| "setup_range_conditions": [ | |
| ], | |
| "group_index_range": { | |
| "chosen": false, | |
| "cause": "not_single_table" | |
| }, | |
| "skip_scan_range": { | |
| "chosen": false, | |
| "cause": "not_single_table" | |
| }, | |
| "analyzing_range_alternatives": { | |
| "range_scan_alternatives": [ | |
| { | |
| "index": "x_time", | |
| "ranges": [ | |
| "0x5686381a <= time < 0x5686e0da" | |
| ], | |
| "index_dives_for_eq_ranges": true, | |
| "rowid_ordered": false, | |
| "using_mrr": false, | |
| "index_only": false, | |
| "rows": 89206, | |
| "cost": 35118, | |
| "chosen": false, | |
| "cause": "cost" | |
| } | |
| ], | |
| "analyzing_roworder_intersect": { | |
| "usable": false, | |
| "cause": "too_few_roworder_scans" | |
| } | |
| } | |
| } | |
| }, | |
| { | |
| "table": "`tags`", | |
| "range_analysis": { | |
| "table_scan": { | |
| "rows": 10, | |
| "cost": 4.1 | |
| }, | |
| "potential_range_indexes": [ | |
| { | |
| "index": "PRIMARY", | |
| "usable": false, | |
| "cause": "not_applicable" | |
| }, | |
| { | |
| "index": "uniq1", | |
| "usable": true, | |
| "key_parts": [ | |
| "hostname", | |
| "region", | |
| "datacenter", | |
| "rack", | |
| "os", | |
| "arch", | |
| "team", | |
| "service", | |
| "service_version", | |
| "service_environment" | |
| ] | |
| }, | |
| { | |
| "index": "xhost", | |
| "usable": true, | |
| "key_parts": [ | |
| "hostname", | |
| "id" | |
| ] | |
| } | |
| ], | |
| "best_covering_index_scan": { | |
| "index": "xhost", | |
| "cost": 1.3226, | |
| "chosen": true | |
| }, | |
| "setup_range_conditions": [ | |
| ], | |
| "group_index_range": { | |
| "chosen": false, | |
| "cause": "not_single_table" | |
| }, | |
| "skip_scan_range": { | |
| "chosen": false, | |
| "cause": "not_single_table" | |
| }, | |
| "analyzing_range_alternatives": { | |
| "range_scan_alternatives": [ | |
| { | |
| "index": "uniq1", | |
| "ranges": [ | |
| "host_2 <= hostname <= host_2" | |
| ], | |
| "index_dives_for_eq_ranges": true, | |
| "rowid_ordered": false, | |
| "using_mrr": false, | |
| "index_only": true, | |
| "rows": 1, | |
| "cost": 0.36, | |
| "chosen": true | |
| }, | |
| { | |
| "index": "xhost", | |
| "ranges": [ | |
| "host_2 <= hostname <= host_2" | |
| ], | |
| "index_dives_for_eq_ranges": true, | |
| "rowid_ordered": true, | |
| "using_mrr": false, | |
| "index_only": true, | |
| "rows": 1, | |
| "cost": 0.36, | |
| "chosen": false, | |
| "cause": "cost" | |
| } | |
| ], | |
| "analyzing_roworder_intersect": { | |
| "usable": false, | |
| "cause": "too_few_roworder_scans" | |
| } | |
| }, | |
| "chosen_range_access_summary": { | |
| "range_access_plan": { | |
| "type": "range_scan", | |
| "index": "uniq1", | |
| "rows": 1, | |
| "ranges": [ | |
| "host_2 <= hostname <= host_2" | |
| ] | |
| }, | |
| "rows_for_plan": 1, | |
| "cost_for_plan": 0.36, | |
| "chosen": true | |
| } | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "considered_execution_plans": [ | |
| { | |
| "plan_prefix": [ | |
| ], | |
| "table": "`tags`", | |
| "best_access_path": { | |
| "considered_access_paths": [ | |
| { | |
| "access_type": "ref", | |
| "index": "PRIMARY", | |
| "usable": false, | |
| "chosen": false | |
| }, | |
| { | |
| "access_type": "ref", | |
| "index": "uniq1", | |
| "rows": 1, | |
| "cost": 0.35, | |
| "chosen": true | |
| }, | |
| { | |
| "access_type": "ref", | |
| "index": "xhost", | |
| "rows": 1, | |
| "cost": 0.35, | |
| "chosen": false | |
| }, | |
| { | |
| "access_type": "range", | |
| "range_details": { | |
| "used_index": "uniq1" | |
| }, | |
| "chosen": false, | |
| "cause": "heuristic_index_cheaper" | |
| } | |
| ] | |
| }, | |
| "condition_filtering_pct": 100, | |
| "rows_for_plan": 1, | |
| "cost_for_plan": 0.35, | |
| "rest_of_plan": [ | |
| { | |
| "plan_prefix": [ | |
| "`tags`" | |
| ], | |
| "table": "`cpu`", | |
| "best_access_path": { | |
| "considered_access_paths": [ | |
| { | |
| "access_type": "ref", | |
| "index": "x_tags_time", | |
| "rows": 3112, | |
| "cost": 1225.1, | |
| "chosen": true | |
| }, | |
| { | |
| "rows_to_scan": 257280, | |
| "filtering_effect": [ | |
| ], | |
| "final_filtering_effect": 0.1156, | |
| "access_type": "scan", | |
| "using_join_cache": true, | |
| "buffers_needed": 1, | |
| "resulting_rows": 29732, | |
| "cost": 26345, | |
| "chosen": false | |
| } | |
| ] | |
| }, | |
| "added_to_eq_ref_extension": false | |
| }, | |
| { | |
| "plan_prefix": [ | |
| "`tags`" | |
| ], | |
| "table": "`cpu`", | |
| "best_access_path": { | |
| "considered_access_paths": [ | |
| { | |
| "access_type": "ref", | |
| "index": "x_tags_time", | |
| "rows": 3112, | |
| "cost": 1225.1, | |
| "chosen": true | |
| }, | |
| { | |
| "rows_to_scan": 257280, | |
| "filtering_effect": [ | |
| ], | |
| "final_filtering_effect": 0.1156, | |
| "access_type": "scan", | |
| "using_join_cache": true, | |
| "buffers_needed": 1, | |
| "resulting_rows": 29732, | |
| "cost": 26345, | |
| "chosen": false | |
| } | |
| ] | |
| }, | |
| "condition_filtering_pct": 100, | |
| "rows_for_plan": 3112, | |
| "cost_for_plan": 1225.5, | |
| "chosen": true | |
| } | |
| ] | |
| }, | |
| { | |
| "plan_prefix": [ | |
| ], | |
| "table": "`cpu`", | |
| "best_access_path": { | |
| "considered_access_paths": [ | |
| { | |
| "access_type": "ref", | |
| "index": "x_tags_time", | |
| "usable": false, | |
| "chosen": false | |
| }, | |
| { | |
| "rows_to_scan": 257280, | |
| "filtering_effect": [ | |
| ], | |
| "final_filtering_effect": 0.1156, | |
| "access_type": "scan", | |
| "resulting_rows": 29732, | |
| "cost": 26321, | |
| "chosen": true | |
| } | |
| ] | |
| }, | |
| "condition_filtering_pct": 100, | |
| "rows_for_plan": 29732, | |
| "cost_for_plan": 26321, | |
| "pruned_by_cost": true | |
| } | |
| ] | |
| }, | |
| { | |
| "attaching_conditions_to_tables": { | |
| "original_condition": "((`cpu`.`tags_id` = `tags`.`id`) and (`tags`.`hostname` = 'host_2') and (`cpu`.`usage_user` > 90) and (`cpu`.`time` >= TIMESTAMP'2016-01-01 00:26:02') and (`cpu`.`time` < TIMESTAMP'2016-01-01 12:26:02'))", | |
| "attached_conditions_computation": [ | |
| ], | |
| "attached_conditions_summary": [ | |
| { | |
| "table": "`tags`", | |
| "attached": "(`tags`.`hostname` = 'host_2')" | |
| }, | |
| { | |
| "table": "`cpu`", | |
| "attached": "((`cpu`.`tags_id` = `tags`.`id`) and (`cpu`.`usage_user` > 90) and (`cpu`.`time` >= TIMESTAMP'2016-01-01 00:26:02') and (`cpu`.`time` < TIMESTAMP'2016-01-01 12:26:02'))" | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| "finalizing_table_conditions": [ | |
| { | |
| "table": "`tags`", | |
| "original_table_condition": "(`tags`.`hostname` = 'host_2')", | |
| "final_table_condition ": null | |
| }, | |
| { | |
| "table": "`cpu`", | |
| "original_table_condition": "((`cpu`.`tags_id` = `tags`.`id`) and (`cpu`.`usage_user` > 90) and (`cpu`.`time` >= TIMESTAMP'2016-01-01 00:26:02') and (`cpu`.`time` < TIMESTAMP'2016-01-01 12:26:02'))", | |
| "final_table_condition ": "((`cpu`.`usage_user` > 90) and (`cpu`.`time` >= TIMESTAMP'2016-01-01 00:26:02') and (`cpu`.`time` < TIMESTAMP'2016-01-01 12:26:02'))" | |
| } | |
| ] | |
| }, | |
| { | |
| "refine_plan": [ | |
| { | |
| "table": "`tags`" | |
| }, | |
| { | |
| "table": "`cpu`", | |
| "pushed_index_condition": "((`cpu`.`time` >= TIMESTAMP'2016-01-01 00:26:02') and (`cpu`.`time` < TIMESTAMP'2016-01-01 12:26:02'))", | |
| "table_condition_attached": "(`cpu`.`usage_user` > 90)" | |
| } | |
| ] | |
| } | |
| ] | |
| } | |
| }, | |
| { | |
| "join_execution": { | |
| "select#": 1, | |
| "steps": [ | |
| ] | |
| } | |
| } | |
| ] | |
| } | |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 | |
| INSUFFICIENT_PRIVILEGES: 0 | |
| 1 row in set (0.00 sec) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment