Thursday, February 17, 2011

Tool of the Day: MySQL Query Profiler

I've always known that you can get MySQL to cough up what it's doing while running a query by using the explain command.

But, today, I found out MySQL will share quite a bit more with you, that is if you take the time to ask.

This article explains it all: Using the New MySQL Query Profiler.

I found out about this while trying to debug an issue with slow update times. This StackOverflow question includes the answer:

mysql> SET PROFILING=1;
mysql> INSERT INTO foo ($testdata);
mysql> show profile for QUERY 1;

That's all there is to it. The article mentions a few other ideas you'll want to know about (such as using show profiles), but it's still pretty simple stuff.

A few other handy things to note:

  • You don't need super user privileges to use the profile
  • When I attempted to run these commands from phpMyAdmin, I didn't get back any results. It took running them from the command line to see anything useful.

Happy debugging!

No comments:

Post a Comment

LinkWithin

Related Posts with Thumbnails