MySQL EXPLAIN Explained

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:

  1. The ability to see what query generated a row based binary log event.
  2. The ability to see a tremendous amount of data points for InnoDB.
  3. The ability to see an extended query execution plan using EXPLAIN FORMAT=JSON (which can also now be used for running queries).
  4. 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

Optimizer Trace

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
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 this month.

5 thoughts on “MySQL EXPLAIN Explained

  1. EXPLAIN FORMAT=json SELECT artist.ID, CONCAT_WS(”, artist.post_title, CONCAT(‘(‘,post.post_title,’)’)) AS TEXT, postmeta.meta_value FROM M8t7_posts artist
    LEFT JOIN M8t7_relationships rea ON rea.meta_value = artist.ID AND posttype = ‘movie’
    LEFT JOIN M8t7_posts post ON post.ID = rea.post_id AND post.post_type=’movie’
    LEFT JOIN M8t7_postmeta postmeta ON post.ID = postmeta.post_id AND post.post_type=’movie’ AND postmeta.meta_key = ‘wpcf-date-of-release’
    WHERE artist.post_title LIKE ‘salman%’ AND artist.post_type=’artiste’ AND artist.post_status=’publish’
    ORDER BY CAST(postmeta.meta_value AS SIGNED) DESC

    I have used above query and columns in where conditions(post_title, post_type and post_status) have the indexed. However, they are not shown in the key and thats reason query cost becomes very huge. can you please help me why it is not taking index.

    {
    “query_block”: {
    “select_id”: 1,
    “cost_info”: {
    “query_cost”: “70836460.54”
    },
    “grouping_operation”: {
    “using_filesort”: false,
    “nested_loop”: [
    {
    “table”: {
    “table_name”: “artist”,
    “access_type”: “index”,
    “possible_keys”: [
    “PRIMARY”,
    “post_type”,
    “old_id”,
    “post_title”,
    “post_name”,
    “post_parent”,
    “post_author”,
    “type_status_date”,
    “post_type_mime_type”
    ],
    “key”: “PRIMARY”,
    “used_key_parts”: [
    “ID”
    ],
    “key_length”: “8”,
    “rows_examined_per_scan”: 491846,
    “rows_produced_per_join”: 46,
    “filtered”: “0.01”,
    “cost_info”: {
    “read_cost”: “2076.28”,
    “eval_cost”: “9.33”,
    “prefix_cost”: “2085.61”,
    “data_read_per_join”: “126K”
    },
    “used_columns”: [
    “ID”,
    “post_title”,
    “post_status”,
    “post_type”
    ],
    “attached_condition”: “((movie_mysql.artist.post_title like ‘salman%’) and (movie_mysql.artist.post_type = ‘artiste’) and (movie_mysql.artist.post_status = ‘publish’))”
    }
    },
    {
    “table”: {
    “table_name”: “rea”,
    “access_type”: “ref”,
    “possible_keys”: [
    “post_type”
    ],
    “key”: “post_type”,
    “used_key_parts”: [
    “posttype”
    ],
    “key_length”: “502”,
    “ref”: [
    “const”
    ],
    “rows_examined_per_scan”: 184269,
    “rows_produced_per_join”: 8591678,
    “filtered”: “100.00”,
    “using_index”: true,
    “cost_info”: {
    “read_cost”: “1718373.09”,
    “eval_cost”: “1718335.79”,
    “prefix_cost”: “3438794.48”,
    “data_read_per_join”: “12G”
    },
    “used_columns”: [
    “post_id”,
    “meta_key”,
    “meta_value”,
    “posttype”
    ],
    “attached_condition”: “(is_not_null_compl(rea), (movie_mysql.rea.meta_value = movie_mysql.artist.ID), true)”
    }
    },
    {
    “table”: {
    “table_name”: “post”,
    “access_type”: “eq_ref”,
    “possible_keys”: [
    “PRIMARY”,
    “post_type”,
    “type_status_date”,
    “post_type_mime_type”
    ],
    “key”: “PRIMARY”,
    “used_key_parts”: [
    “ID”
    ],
    “key_length”: “8”,
    “ref”: [
    “movie_mysql.rea.post_id”
    ],
    “rows_examined_per_scan”: 1,
    “rows_produced_per_join”: 8591678,
    “filtered”: “100.00”,
    “cost_info”: {
    “read_cost”: “184269.00”,
    “eval_cost”: “1718335.79”,
    “prefix_cost”: “5341399.27”,
    “data_read_per_join”: “22G”
    },
    “used_columns”: [
    “ID”,
    “post_title”,
    “post_type”
    ],
    “attached_condition”: “(is_not_null_compl(post), ((movie_mysql.post.ID = movie_mysql.rea.post_id) and (movie_mysql.post.post_type = ‘movie’)), true)”
    }
    },
    {
    “table”: {
    “table_name”: “postmeta”,
    “access_type”: “ref”,
    “possible_keys”: [
    “post_id”,
    “meta_key”
    ],
    “key”: “post_id”,
    “used_key_parts”: [
    “post_id”
    ],
    “key_length”: “8”,
    “ref”: [
    “movie_mysql.post.ID”
    ],
    “rows_examined_per_scan”: 6,
    “rows_produced_per_join”: 54579217,
    “filtered”: “100.00”,
    “cost_info”: {
    “read_cost”: “54579217.72”,
    “eval_cost”: “10915843.54”,
    “prefix_cost”: “70836460.54”,
    “data_read_per_join”: “53G”
    },
    “used_columns”: [
    “meta_id”,
    “post_id”,
    “meta_key”,
    “meta_value”
    ],
    “attached_condition”: “(is_not_null_compl(postmeta), ((movie_mysql.post.post_type = ‘movie’) and (movie_mysql.postmeta.meta_key = ‘wpcf-date-of-release’)), true)”
    }
    }
    ]
    }
    }
    }

    1. Hi,
      The first thing I would check is whether the character set/collation of your connection is the same as defined for the columns of your tables. If there is a mismatch, it may not be possible to use an index.
      Also, do you have an index on M8t7_relationships.meta_value? If yes, what type is that column? (A mismatch of types could also cause index not to be used.)

      1. Yes, meta_value is varchar and ID is bigint.
        So how shall it can be used index? Shall we use cast or convert function for comparison?

  2. Yes, meta_value is varchar and ID is bigint.
    So how shall it can be used index? Shall we use cast or convert function for comparison?

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.