In years past, MySQL was a bit of a black box when it came to understanding what was happening and why. In MySQL 5.6 and 5.7, we’ve added many new features that provide much needed transparency and insight into the inner workings of MySQL. The single biggest feature was the new Performance Schema, but some other examples are:
- The ability to see what query generated a row based binary log event.
- The ability to see a tremendous amount of data points for InnoDB.
- The ability to see an extended query execution plan using EXPLAIN FORMAT=JSON (which can also now be used for running queries).
- The ability to see a query execution trace via the new optimizer trace functionality.
I’m going to focus on #3 and #4 here. This will hopefully whet your appetite for a webinar coming up where I will dive deeper into the EXPLAIN related features.
Toward that end, I’ll show you an example of the JSON formatted explain, an optimizer trace, and finally of Workbench’s Visual Explain. For all of these, I’ll be using the Sakila test database.
JSON EXPLAIN
mysql> explain format=json select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from ((`film` `f` join `film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `film_actor` `fa` on((`f`.`film_id` = `fa`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`actor` `a` left join `film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`))) left join `film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`))) left join `category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "9603.34"
},
"grouping_operation": {
"using_filesort": true,
"cost_info": {
"sort_cost": "2600.00"
},
"nested_loop": [
{
"table": {
"table_name": "a",
"access_type": "index",
"possible_keys": [
"last_name",
"ln_fn_idx"
],
"key": "last_name",
"used_key_parts": [
"last_name",
"first_name"
],
"key_length": "274",
"rows_examined_per_scan": 200,
"rows_produced_per_join": 200,
"filtered": 100,
"using_index": true,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "40.00",
"prefix_cost": "41.00",
"data_read_per_join": "54K"
},
"used_columns": [
"actor_id",
"first_name",
"last_name"
]
}
},
{
"table": {
"table_name": "fa",
"access_type": "ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"actor_id"
],
"key_length": "2",
"ref": [
"sakila.a.actor_id"
],
"rows_examined_per_scan": 13,
"rows_produced_per_join": 2600,
"filtered": 100,
"using_index": true,
"cost_info": {
"read_cost": "202.34",
"eval_cost": "520.00",
"prefix_cost": "763.34",
"data_read_per_join": "40K"
},
"used_columns": [
"actor_id",
"film_id"
]
}
},
{
"table": {
"table_name": "fc",
"access_type": "ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"film_id"
],
"key_length": "2",
"ref": [
"sakila.fa.film_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2600,
"filtered": 100,
"using_index": true,
"cost_info": {
"read_cost": "2600.00",
"eval_cost": "520.00",
"prefix_cost": "3883.34",
"data_read_per_join": "40K"
},
"used_columns": [
"film_id",
"category_id"
]
}
},
{
"table": {
"table_name": "c",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "1",
"ref": [
"sakila.fc.category_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2600,
"filtered": 100,
"cost_info": {
"read_cost": "2600.00",
"eval_cost": "520.00",
"prefix_cost": "7003.34",
"data_read_per_join": "223K"
},
"used_columns": [
"category_id",
"name"
]
}
}
],
"select_list_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "34.81"
},
"nested_loop": [
{
"table": {
"table_name": "fa",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"idx_fk_film_id"
],
"key": "PRIMARY",
"used_key_parts": [
"actor_id"
],
"key_length": "2",
"ref": [
"sakila.a.actor_id"
],
"rows_examined_per_scan": 13,
"rows_produced_per_join": 13,
"filtered": 100,
"using_index": true,
"cost_info": {
"read_cost": "1.01",
"eval_cost": "2.60",
"prefix_cost": "3.61",
"data_read_per_join": "208"
},
"used_columns": [
"actor_id",
"film_id"
]
}
},
{
"table": {
"table_name": "f",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"film_id"
],
"key_length": "2",
"ref": [
"sakila.fa.film_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 13,
"filtered": 100,
"cost_info": {
"read_cost": "13.00",
"eval_cost": "2.60",
"prefix_cost": "19.21",
"data_read_per_join": "10K"
},
"used_columns": [
"film_id",
"title"
]
}
},
{
"table": {
"table_name": "fc",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"fk_film_category_category"
],
"key": "PRIMARY",
"used_key_parts": [
"film_id",
"category_id"
],
"key_length": "3",
"ref": [
"sakila.fa.film_id",
"sakila.c.category_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 13,
"filtered": 100,
"using_index": true,
"cost_info": {
"read_cost": "13.00",
"eval_cost": "2.60",
"prefix_cost": "34.81",
"data_read_per_join": "208"
},
"used_columns": [
"film_id",
"category_id"
]
}
}
]
}
}
]
}
}
}
1 row in set, 3 warnings (0.02 sec)
Optimizer Trace
mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on; # be readable
Query OK, 0 rows affected (0.00 sec)
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=90000000; # avoid small default
Query OK, 0 rows affected (0.00 sec)
mysql> select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(/* select#2 */ select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from `sakila`.`film` `f` join `sakila`.`film_category` `fc` join `sakila`.`film_actor` `fa` where ((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from `sakila`.`actor` `a` left join `sakila`.`film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`)) left join `sakila`.`film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`)) left join `sakila`.`category` `c` on((`fc`.`category_id` = `c`.`category_id`)) where 1 group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`;
+----------+-------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| actor_id | first_name | last_name | film_info |
+----------+-------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | PENELOPE | GUINESS | Animation: ANACONDA CONFESSIONS; Children: LANGUAGE COWBOY; Classics: COLOR PHILADELPHIA, WESTWARD SEABISCUIT; Comedy: VERTIGO NORTHWEST; Documentary: ACADEMY DINOSAUR; Family: KING EVOLUTION, SPLASH GUMP; Foreign: MULHOLLAND BEAST; Games: BULWORTH COMMANDMENTS, HUMAN GRAFFITI; Horror: ELEPHANT TROJAN, LADY STAGE, RULES HUMAN; Music: WIZARD COLDBLOODED; New: ANGELS LIFE, OKLAHOMA JUMANJI; Sci-Fi: CHEAPER CLYDE; Sports: GLEAMING JAWBREAKER
...
| 200 | THORA | TEMPLE | Animation: CAROL TEXAS; Children: CHRISTMAS MOONSHINE, WRONG BEHAVIOR; Classics: CANDIDATE PERDITION, GALAXY SWEETHEARTS, LOVER TRUMAN; Documentary: MADISON TRAP, VIRGINIAN PLUTO; Drama: WARDROBE PHANTOM; Family: AFRICAN EGG, BLANKET BEVERLY, HOCUS FRIDA; Music: INSIDER ARIZONA, TELEGRAPH VOYAGE; New: INTERVIEW LIAISONS, LOVERBOY ATTACKS; Sci-Fi: BADMAN DAWN, RANDOM GO, TROJAN TOMORROW; Sports: JADE BUNCH |
+----------+-------------+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
200 rows in set (0.44 sec)
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',( select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from `sakila`.`film` `f` join `sakila`.`film_category` `fc` join `sakila`.`film_actor` `fa` where ((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from `sakila`.`actor` `a` left join `sakila`.`film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`)) left join `sakila`.`film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`)) left join `sakila`.`category` `c` on((`fc`.`category_id` = `c`.`category_id`)) where 1 group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from ((`film` `f` join `film_category` `fc`) join `film_actor` `fa`) where ((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`))"
}
] /* steps */
} /* join_preparation */
},
{
"expanded_query": "/* select#1 */ select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(/* select#2 */ select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from ((`film` `f` join `film_category` `fc`) join `film_actor` `fa`) where ((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`actor` `a` left join `film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`))) left join `film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`))) left join `category` `c` on((`fc`.`category_id` = `c`.`category_id`))) where 1 group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`"
},
{
"transformations_to_nested_joins": {
"transformations": [
"parenthesis_removal"
] /* transformations */,
"expanded_query": "/* select#2 */ select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from `film` `f` join `film_category` `fc` join `film_actor` `fa` where ((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`))"
} /* transformations_to_nested_joins */
},
{
"transformations_to_nested_joins": {
"transformations": [
"parenthesis_removal"
] /* transformations */,
"expanded_query": "/* select#1 */ select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(/* select#2 */ select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from `film` `f` join `film_category` `fc` join `film_actor` `fa` where ((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from `actor` `a` left join `film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`)) left join `film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`)) left join `category` `c` on((`fc`.`category_id` = `c`.`category_id`)) where 1 group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`"
} /* transformations_to_nested_joins */
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "1",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "1"
},
{
"transformation": "constant_propagation",
"resulting_condition": "1"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": null
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`actor` `a`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`film_actor` `fa`",
"row_may_be_null": true,
"map_bit": 1,
"depends_on_map_bits": [
0
] /* depends_on_map_bits */
},
{
"table": "`film_category` `fc`",
"row_may_be_null": true,
"map_bit": 2,
"depends_on_map_bits": [
0,
1
] /* depends_on_map_bits */
},
{
"table": "`category` `c`",
"row_may_be_null": true,
"map_bit": 3,
"depends_on_map_bits": [
0,
1,
2
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`film_actor` `fa`",
"field": "actor_id",
"equals": "`a`.`actor_id`",
"null_rejecting": false
},
{
"table": "`film_category` `fc`",
"field": "film_id",
"equals": "`fa`.`film_id`",
"null_rejecting": true
},
{
"table": "`category` `c`",
"field": "category_id",
"equals": "`fc`.`category_id`",
"null_rejecting": true
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`actor` `a`",
"const_keys_added": {
"keys": [
"last_name",
"ln_fn_idx"
] /* keys */,
"cause": "group_by"
} /* const_keys_added */,
"range_analysis": {
"table_scan": {
"rows": 200,
"cost": 43.1
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_actor_last_name",
"usable": false,
"cause": "not_applicable"
},
{
"index": "first_name",
"usable": false,
"cause": "not_applicable"
},
{
"index": "last_name",
"usable": true,
"key_parts": [
"last_name",
"first_name",
"actor_id"
] /* key_parts */
},
{
"index": "ln_fn_idx",
"usable": true,
"key_parts": [
"last_name",
"first_name",
"actor_id"
] /* key_parts */
}
] /* potential_range_indexes */,
"best_covering_index_scan": {
"index": "last_name",
"cost": 47.633,
"chosen": false,
"cause": "cost"
} /* best_covering_index_scan */,
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
} /* group_index_range */
} /* range_analysis */
},
{
"table": "`film_actor` `fa`",
"table_scan": {
"rows": 5462,
"cost": 12
} /* table_scan */
},
{
"table": "`film_category` `fc`",
"table_scan": {
"rows": 1000,
"cost": 4
} /* table_scan */
},
{
"table": "`category` `c`",
"table_scan": {
"rows": 16,
"cost": 1
} /* table_scan */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`actor` `a`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 200,
"access_type": "scan",
"resulting_rows": 200,
"cost": 41,
"chosen": true,
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 200,
"cost_for_plan": 41,
"rest_of_plan": [
{
"plan_prefix": [
"`actor` `a`"
] /* plan_prefix */,
"table": "`film_actor` `fa`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 13,
"cost": 722.34,
"chosen": true
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 2600,
"cost_for_plan": 763.34,
"rest_of_plan": [
{
"plan_prefix": [
"`actor` `a`",
"`film_actor` `fa`"
] /* plan_prefix */,
"table": "`film_category` `fc`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 1,
"cost": 3120,
"chosen": true
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 2600,
"cost_for_plan": 3883.3,
"rest_of_plan": [
{
"plan_prefix": [
"`actor` `a`",
"`film_actor` `fa`",
"`film_category` `fc`"
] /* plan_prefix */,
"table": "`category` `c`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 3120,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"rows_to_scan": 16,
"access_type": "scan",
"using_join_cache": true,
"buffers_needed": 3,
"resulting_rows": 16,
"cost": 8323.8,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"condition_filtering_pct": 100,
"rows_for_plan": 2600,
"cost_for_plan": 7003.3,
"sort_cost": 2600,
"new_cost_for_plan": 9603.3,
"chosen": true
}
] /* rest_of_plan */
}
] /* rest_of_plan */
}
] /* rest_of_plan */
}
] /* considered_execution_plans */
},
{
"condition_on_constant_tables": "1",
"condition_value": true
},
{
"attaching_conditions_to_tables": {
"original_condition": "1",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`actor` `a`",
"attached": null
},
{
"table": "`film_actor` `fa`",
"attached": null
},
{
"table": "`film_category` `fc`",
"attached": null
},
{
"table": "`category` `c`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "GROUP BY",
"original_clause": "`a`.`actor_id`,`a`.`first_name`,`a`.`last_name`",
"items": [
{
"item": "`a`.`actor_id`"
},
{
"item": "`a`.`first_name`"
},
{
"item": "`a`.`last_name`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`a`.`actor_id`,`a`.`first_name`,`a`.`last_name`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
"index_order_summary": {
"table": "`actor` `a`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`actor` `a`"
},
{
"table": "`film_actor` `fa`"
},
{
"table": "`film_category` `fc`"
},
{
"table": "`category` `c`"
}
] /* refine_plan */
},
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 1183,
"key_length": 0,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 14181
} /* tmp_table_info */
} /* creating_tmp_table */
}
] /* steps */
} /* join_optimization */
},
{
"join_optimization": {
"select#": 2,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`) and multiple equal(`f`.`film_id`, `fa`.`film_id`, `fc`.`film_id`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`) and multiple equal(`f`.`film_id`, `fa`.`film_id`, `fc`.`film_id`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`) and multiple equal(`f`.`film_id`, `fa`.`film_id`, `fc`.`film_id`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`film` `f`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`film_category` `fc`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
] /* depends_on_map_bits */
},
{
"table": "`film_actor` `fa`",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`film` `f`",
"field": "film_id",
"equals": "`fc`.`film_id`",
"null_rejecting": false
},
{
"table": "`film` `f`",
"field": "film_id",
"equals": "`fa`.`film_id`",
"null_rejecting": false
},
{
"table": "`film_category` `fc`",
"field": "film_id",
"equals": "`f`.`film_id`",
"null_rejecting": false
},
{
"table": "`film_category` `fc`",
"field": "film_id",
"equals": "`fa`.`film_id`",
"null_rejecting": false
},
{
"table": "`film_category` `fc`",
"field": "category_id",
"equals": "`c`.`category_id`",
"null_rejecting": true
},
{
"table": "`film_category` `fc`",
"field": "category_id",
"equals": "`c`.`category_id`",
"null_rejecting": true
},
{
"table": "`film_actor` `fa`",
"field": "actor_id",
"equals": "`a`.`actor_id`",
"null_rejecting": false
},
{
"table": "`film_actor` `fa`",
"field": "film_id",
"equals": "`f`.`film_id`",
"null_rejecting": false
},
{
"table": "`film_actor` `fa`",
"field": "film_id",
"equals": "`fc`.`film_id`",
"null_rejecting": false
},
{
"table": "`film_actor` `fa`",
"field": "film_id",
"equals": "`f`.`film_id`",
"null_rejecting": false
},
{
"table": "`film_actor` `fa`",
"field": "film_id",
"equals": "`fc`.`film_id`",
"null_rejecting": false
},
{
"table": "`film_actor` `fa`",
"field": "actor_id",
"equals": "`a`.`actor_id`",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`film` `f`",
"table_scan": {
"rows": 1000,
"cost": 12
} /* table_scan */
},
{
"table": "`film_category` `fc`",
"table_scan": {
"rows": 1000,
"cost": 4
} /* table_scan */
},
{
"table": "`film_actor` `fa`",
"table_scan": {
"rows": 5462,
"cost": 12
} /* table_scan */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`film` `f`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"rows_to_scan": 1000,
"access_type": "scan",
"resulting_rows": 1000,
"cost": 212,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1000,
"cost_for_plan": 212,
"rest_of_plan": [
{
"plan_prefix": [
"`film` `f`"
] /* plan_prefix */,
"table": "`film_category` `fc`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 1200,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 1000,
"cost_for_plan": 1412,
"rest_of_plan": [
{
"plan_prefix": [
"`film` `f`",
"`film_category` `fc`"
] /* plan_prefix */,
"table": "`film_actor` `fa`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 1200,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"condition_filtering_pct": 100,
"rows_for_plan": 1000,
"cost_for_plan": 2612,
"chosen": true
}
] /* rest_of_plan */
}
] /* rest_of_plan */
},
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`film_category` `fc`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"usable": false,
"chosen": false
},
{
"access_type": "ref",
"index": "fk_film_category_category",
"rows": 31,
"cost": 7.2146,
"chosen": true
},
{
"access_type": "scan",
"cost": 204,
"rows": 1000,
"chosen": false,
"cause": "cost"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 31,
"cost_for_plan": 7.2146,
"rest_of_plan": [
{
"plan_prefix": [
"`film_category` `fc`"
] /* plan_prefix */,
"table": "`film` `f`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 37.2,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 31,
"cost_for_plan": 44.415,
"rest_of_plan": [
{
"plan_prefix": [
"`film_category` `fc`",
"`film` `f`"
] /* plan_prefix */,
"table": "`film_actor` `fa`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 37.2,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"condition_filtering_pct": 100,
"rows_for_plan": 31,
"cost_for_plan": 81.615,
"chosen": true
}
] /* rest_of_plan */
}
] /* rest_of_plan */
},
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`film_actor` `fa`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "PRIMARY",
"rows": 13,
"cost": 3.6117,
"chosen": true
},
{
"access_type": "ref",
"index": "idx_fk_film_id",
"usable": false,
"chosen": false
},
{
"access_type": "scan",
"cost": 1104.4,
"rows": 5462,
"chosen": false,
"cause": "cost"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 13,
"cost_for_plan": 3.6117,
"rest_of_plan": [
{
"plan_prefix": [
"`film_actor` `fa`"
] /* plan_prefix */,
"table": "`film` `f`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 15.6,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 13,
"cost_for_plan": 19.212,
"rest_of_plan": [
{
"plan_prefix": [
"`film_actor` `fa`",
"`film` `f`"
] /* plan_prefix */,
"table": "`film_category` `fc`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 15.6,
"chosen": true,
"cause": "clustered_pk_chosen_by_heuristics"
},
{
"access_type": "scan",
"chosen": false,
"cause": "covering_index_better_than_full_scan"
}
] /* considered_access_paths */
} /* best_access_path */,
"added_to_eq_ref_extension": true,
"condition_filtering_pct": 100,
"rows_for_plan": 13,
"cost_for_plan": 34.812,
"chosen": true
}
] /* rest_of_plan */
}
}
] /* rest_of_plan */
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`f`.`film_id` = `fa`.`film_id`) and (`fc`.`film_id` = `fa`.`film_id`) and (`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`film_actor` `fa`",
"attached": null
},
{
"table": "`film` `f`",
"attached": null
},
{
"table": "`film_category` `fc`",
"attached": null
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`film_actor` `fa`"
},
{
"table": "`film` `f`"
},
{
"table": "`film_category` `fc`"
}
] /* refine_plan */
},
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 768,
"key_length": 0,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 21845
} /* tmp_table_info */
} /* creating_tmp_table */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`actor` `a`",
"field": "actor_id"
},
{
"direction": "asc",
"table": "`actor` `a`",
"field": "first_name"
},
{
"direction": "asc",
"table": "`actor` `a`",
"field": "last_name"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 200,
"examined_rows": 200,
"number_of_tmp_files": 0,
"sort_buffer_size": 261896,
"sort_mode": ""
} /* filesort_summary */
},
{
"subselect_execution": {
"select#": 2,
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
} /* subselect_execution */
},
...# repeats for each row
{
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
} /* subselect_execution */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)
Workbench Visual Explain
These new insights also allow for clients to provide additional
visual representations of this data. The new Visual Explain
functionality in MySQL Workbench 6.1 is a great example. Let’s
look at that query again:
Conclusion
Again, if all of this looks interesting, but a bit overwhelming,
then please join Matt and I for our webinar coming up this month.