Wednesday, November 19, 2008

MySQL Query Analyzer - first impressions

Very interesting review of the new MySQL Query Analyzer that comes with MySQL Enterprise Monitor.
I installed Quan (Query Analyzer) and within about 20 minutes it became clear I had a query that was averaging 14 seconds to run. This query doesn't get run all that often, every 5 minutes or so, but it uses a table that is the most active table on our system.

OK, click on the query and it pops up a full view, showing the query with literals replaced by place holders. If you have Example Queries configured, you can check to see if there is an example showing all of the data, and if you have Example Explain configured, you will also be able to get a full breakdown of what indexes will be used in resolving the query.

With a few minutes of checking the query and fine tuning it I was able to get it down to 0.15 seconds on average. That is almost a 100 fold improvement.

No comments: