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.

One thought on “MySQL EXPLAIN Explained

Leave a Reply

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


one × = 1

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">