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.
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:
The headers are each highlighted too:
I also put the potential keys in the keys column to make the table more compact:
What are you waiting for? Go check it out!