Query Samples, Explained

Posted by John Potocny on Oct 9, 2014 2:13:00 AM

Query_Samples

The EXPLAIN command is one of MySQL’s most useful tools for understanding query performance. When you EXPLAIN a query, MySQL will return the plan created by the query optimizer. It also shows you how that query will be indexed and an estimate of how many rows are processed by that query. From this information, it is easy to see if your queries are taking advantage of table indexes or if you can change them for some extra performance. VividCortex provides a lot of information on query performance, including samples of the queries that are run against your database. Now, those samples will have EXPLAIN data for them too!

So what does the EXPLAIN feature look like in VividCortex? Here’s a screenshot to illustrate:

Query_Samples_Explained

Those who are familiar with our application will recognize the query-drilldown view. Each of the dots in the scatterplot represents a sample for the selected query, graphed against its latency. Underneath, we show the EXPLAIN information for that sample. You can see the estimated number of rows processed, as well as the index that was used for the query. You can also see EXPLAIN information for prepared statements - we rebuild the query text for prepared statement samples so they get this information as well.

There are a few limitations existing at the moment. First, we will not try to run an EXPLAIN if a sample contains sub-queries. Also, if there is an error running the EXPLAIN, it is not displayed (stay tuned for updates on this feature!).

Of course, this EXPLAIN feature is just the first step for us. Data on query performance is good, but interpreting it is even better. One of our development priorities is proactively alerting customers to queries with potentially poor performance so that they don’t have to search for problems. Let us know if you have any thoughts on what you would like to see here!

Pic Cred

Recent Posts

Posts by Topic

see all