Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- {
- "steps": [
- {
- "join_preparation": {
- "select#": 1,
- "steps": [
- {
- "expanded_query":
- "/* select#1 */ select `departments`.`dept_no` AS `dept_no`
- from (`departments` join `dept_manager`
- on((`departments`.`dept_no` = `dept_manager`.`dept_no`)))
- where (`dept_manager`.`to_date` > now())
- group by `departments`.`dept_no`
- having (count(`dept_manager`.`emp_no`) > 1)
- limit 0,1000"
- }
- ]
- }
- },
- {
- "join_optimization": {
- "select#": 1,
- "steps": [
- {
- "transformations_to_nested_joins": {
- "transformations": [
- "JOIN_condition_to_WHERE",
- "parenthesis_removal"
- ],
- "expanded_query": "/* select#1 */ select `departments`.`dept_no` AS `dept_no` from `departments` join `dept_manager` where ((`dept_manager`.`to_date` > now()) and (`departments`.`dept_no` = `dept_manager`.`dept_no`)) group by `departments`.`dept_no` having (count(`dept_manager`.`emp_no`) > 1) limit 0,1000"
- }
- },
- {
- "condition_processing": {
- "condition": "WHERE",
- "original_condition": "((`dept_manager`.`to_date` > now()) and (`departments`.`dept_no` = `dept_manager`.`dept_no`))",
- "steps": [
- {
- "transformation": "equality_propagation",
- "resulting_condition": "((`dept_manager`.`to_date` > now()) and multiple equal(`departments`.`dept_no`, `dept_manager`.`dept_no`))"
- },
- {
- "transformation": "constant_propagation",
- "resulting_condition": "((`dept_manager`.`to_date` > now()) and multiple equal(`departments`.`dept_no`, `dept_manager`.`dept_no`))"
- },
- {
- "transformation": "trivial_condition_removal",
- "resulting_condition": "((`dept_manager`.`to_date` > now()) and multiple equal(`departments`.`dept_no`, `dept_manager`.`dept_no`))"
- }
- ]
- }
- },
- {
- "condition_processing": {
- "condition": "HAVING",
- "original_condition": "(count(`dept_manager`.`emp_no`) > 1)",
- "steps": [
- {
- "transformation": "constant_propagation",
- "resulting_condition": "(count(`dept_manager`.`emp_no`) > 1)"
- },
- {
- "transformation": "trivial_condition_removal",
- "resulting_condition": "(count(`dept_manager`.`emp_no`) > 1)"
- }
- ]
- }
- },
- {
- "table_dependencies": [
- {
- "table": "`departments`",
- "row_may_be_null": false,
- "map_bit": 0,
- "depends_on_map_bits": [
- ]
- },
- {
- "table": "`dept_manager`",
- "row_may_be_null": false,
- "map_bit": 1,
- "depends_on_map_bits": [
- ]
- }
- ]
- },
- {
- "ref_optimizer_key_uses": [
- {
- "table": "`departments`",
- "field": "dept_no",
- "equals": "`dept_manager`.`dept_no`",
- "null_rejecting": false
- },
- {
- "table": "`dept_manager`",
- "field": "dept_no",
- "equals": "`departments`.`dept_no`",
- "null_rejecting": false
- }
- ]
- },
- {
- "rows_estimation": [
- {
- "table": "`departments`",
- "const_keys_added": {
- "keys": [
- "PRIMARY",
- "dept_name"
- ],
- "cause": "group_by"
- },
- "range_analysis": {
- "table_scan": {
- "rows": 9,
- "cost": 4.9
- },
- "potential_range_indices": [
- {
- "index": "PRIMARY",
- "usable": true,
- "key_parts": [
- "dept_no"
- ]
- },
- {
- "index": "dept_name",
- "usable": true,
- "key_parts": [
- "dept_name"
- ]
- }
- ],
- "best_covering_index_scan": {
- "index": "dept_name",
- "cost": 2.929,
- "chosen": true
- },
- "setup_range_conditions": [
- ],
- "group_index_range": {
- "chosen": false,
- "cause": "not_single_table"
- }
- }
- },
- {
- "table": "`dept_manager`",
- "table_scan": {
- "rows": 24,
- "cost": 1
- }
- }
- ]
- },
- {
- "considered_execution_plans": [
- {
- "plan_prefix": [
- ],
- "table": "`departments`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "PRIMARY",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "scan",
- "rows": 9,
- "cost": 2.8,
- "chosen": true,
- "use_tmp_table": true
- }
- ]
- },
- "cost_for_plan": 2.8,
- "rows_for_plan": 9,
- "rest_of_plan": [
- {
- "plan_prefix": [
- "`departments`"
- ],
- "table": "`dept_manager`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "dept_no",
- "rows": 1,
- "cost": 9.2,
- "chosen": true
- },
- {
- "access_type": "scan",
- "using_join_cache": true,
- "rows": 18,
- "cost": 34.6,
- "chosen": false
- }
- ]
- },
- "cost_for_plan": 13.6,
- "rows_for_plan": 9,
- "sort_cost": 9,
- "new_cost_for_plan": 22.6,
- "chosen": true
- }
- ]
- },
- {
- "plan_prefix": [
- ],
- "table": "`dept_manager`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "dept_no",
- "usable": false,
- "chosen": false
- },
- {
- "access_type": "scan",
- "rows": 24,
- "cost": 5.8,
- "chosen": true
- }
- ]
- },
- "cost_for_plan": 5.8,
- "rows_for_plan": 24,
- "rest_of_plan": [
- {
- "plan_prefix": [
- "`dept_manager`"
- ],
- "table": "`departments`",
- "best_access_path": {
- "considered_access_paths": [
- {
- "access_type": "ref",
- "index": "PRIMARY",
- "rows": 1,
- "cost": 24.2,
- "chosen": true
- },
- {
- "access_type": "scan",
- "cause": "covering_index_better_than_full_scan",
- "chosen": false
- }
- ]
- },
- "cost_for_plan": 34.6,
- "rows_for_plan": 24,
- "pruned_by_cost": true
- }
- ]
- }
- ]
- },
- {
- "attaching_conditions_to_tables": {
- "original_condition": "((`dept_manager`.`dept_no` = `departments`.`dept_no`) and (`dept_manager`.`to_date` > now()))",
- "attached_conditions_computation": [
- ],
- "attached_conditions_summary": [
- {
- "table": "`departments`",
- "attached": null
- },
- {
- "table": "`dept_manager`",
- "attached": "(`dept_manager`.`to_date` > now())"
- }
- ]
- }
- },
- {
- "clause_processing": {
- "clause": "GROUP BY",
- "original_clause": "`departments`.`dept_no`",
- "items": [
- {
- "item": "`departments`.`dept_no`"
- }
- ],
- "resulting_clause_is_simple": true,
- "resulting_clause": "`departments`.`dept_no`"
- }
- },
- {
- "refine_plan": [
- {
- "table": "`departments`",
- "access_type": "index_scan"
- },
- {
- "table": "`dept_manager`"
- }
- ]
- },
- {
- "reconsidering_access_paths_for_index_ordering": {
- "clause": "GROUP BY",
- "index_order_summary": {
- "table": "`departments`",
- "index_provides_order": true,
- "order_direction": "asc",
- "index": "PRIMARY",
- "plan_changed": true,
- "access_type": "index_scan"
- }
- }
- }
- ]
- }
- },
- {
- "join_execution": {
- "select#": 1,
- "steps": [
- ]
- }
- }
- ]
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement