One the tools mentioned was
pt-visual-explain, and I’ve been playing with it to see how it can help me better understand my queries and what is happening when I’m getting data.
I thought that I would document the process so I don’t forget it.
Go grab your specific platform download of Percona Toolkit. Personally I used the tarball option for my work machine as it’s OS X.
Here are the instructions for installation on a Mac. I’ve not tried it on my Ubuntu laptop, but when I do, I’ll update this post.
Then I unzipped the tarball into a folder. You’ll notice that there is a
Makefile.PL, this is a Perl script for building the various executables you’ll need.
Make sure you have Perl installed,
perl --version, which you should be default I think. Then it’s time to build the various executables.
1 2 3 4 5
Then you’ll probably find some warnings or errors about a missing Perl package called
DBD::MySQL. So let’s install this. I used this guide, but I will summarise it here.
If this is the first time you’ve used the Cpan module on your Mac you will be asked about configuring your Cpan set-up.
1 2 3 4 5 6 7
Don’t include the
--testpassword='' argument if your database doesn’t have a password.
1 2 3
So now it’s time to run it and see what our queries look like.
You should get a nice visual output of what is going on with your query. At this point you can add indexes, change the query and keep running the visual explainer against it to see how things are changing.
That’s it! Pretty simple really, apart from all that crap with Perl ;)