MySQL Explain Explained

Whenever I have a problem query in MySQL, I say to myself, I know, I’ll use Explain Plan. Now I have two problems. I run the explain plan, but then I have to look up all the cryptic terms and read through the documentation to know what in the world is going on. Well, over the weekend, I decided to solve my own problem. I created a simple page that allows you to run an explain plan, and then each of those obscure, abbreviated terms is annotated with a tooltip that explains exactly what’s going on. I’m calling it MySQL Explain Explained.

I created a demo using the sample employees database (which you can download here). Here’s a sample query:

select gender, count(emp_no) as manager_cnt, sum(cnt) as sum, avg(cnt) as avg
from
(
    select m.emp_no, m.gender, count(de.emp_no) as cnt
    from employees e
    join dept_emp de using(emp_no)
    join dept_manager dm using(dept_no)
    join employees m on m.emp_no = dm.emp_no
    where
        dm.to_date > now()
        and
        de.to_date > now()
    group by
        m.emp_no,
        m.gender
) o
group by gender

This is what it looks like when you run that query with MySQL Explain Explained:

mysql-explain-explain-scn-1

The headers are each highlighted too:

mysql-explain-explain-scn-2

I also put the potential keys in the keys column to make the table more compact:

mysql-explain-explain-scn-3

On the backend, it’s using PHP to run the query and generate a JSON object of the explain plan. Most of the work is in the JavaScript code, so you can pop it into your favorite language, whether that be Go, Java, or Erlang. I wanted to make it was simple for you to add this to your development machine, so it’s just a single page.

What are you waiting for? Go check it out!

Tagged with:
Posted in Databases, IT

Leave a Reply

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

*

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