Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
MySQL EXPLAIN Explained
+6 Vote Up -0 Vote Down

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 (http://www.mysql.com/news-and-events/web-seminars/mysql-explain-explained/" target="_blank) 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:

    Example Visual Explain

    Conclusion
    Again, if all of this looks interesting, but a bit overwhelming, then please join Matt and I for our webinar coming up (http://www.mysql.com/news-and-events/web-seminars/mysql-explain-explained/" target="_blank) this month.

    Votes:

    You must be logged in with a MySQL account to vote on Planet MySQL entries. More information on PlanetMySQL voting.

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.