How To Find Slow SQL Queries

There are generally multiple ways to query a database for the same set of results, but some will perform better than others. As queries are often the most time consuming part of the process of delivering dynamic web pages, it makes sense to know how to find which ones are most in need of improvement and, of course, what you can do to improve them.

I’ve been talking about SQL databases all summer, most recently in a mini-series about using indexes to speed up some database searches. I talked about the pros and cons of indexes, their different types, and how to create and add indexes to new and existing tables.

Now I want to talk about queries, the SQL statements you write to find specific values in your database. Today I’ll talk about how to identify queries in need of improvement and next week I’ll share some thoughts for how to improve them.

Check Query Performance with EXPLAIN and EXTENDED

MySQL offers some very useful commands to gather information about queries and even how to improve them. The first one to know about is EXPLAIN.

EXPLAIN is a statement you add to other statements, like SELECT, and it returns information about how MySQL executes the statement. It works with SELECT, DELETE, INSERT, REPLACE, and UPDATE.

To use EXPLAIN you prepend it to the query you want more information about.

[code type==”mysql”]
EXPLAIN SELECT * FROM wp_options
[/code]

The example wants information about selecting everything from the wp_options table in WordPress. Here’s the output from the EXPLAIN command.

It doesn’t look like much, especially with so many NULLs returned, but that’s because the SELECT asks for everything to be returned. More specific queries will receive more specific information. It also helps to understand all the information being returned so let me walk through each field.

id is an identifier for each SELECT inside the query. The results above have only the single SELECT statement, but if you use JOINs or subqueries there will be multiple rows of results each with a different id.

select_type is the type of SELECT statement. It can be simple, primary, derived, materialized, subquery, dependent subquery, uncacheable subquery, union, dependent union, uncacheable union, or union result.

table is the table name in the output row. It will be one of the tables used in your query, the one specific to the particular row of output being presented.

type is how MySQL joins multiple tables used in the query.
Results can be system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index _subquery, range, index, all. ALL is a full table scan, which makes sense with the wp_options SELECT since every row was requested using the wildcard.

possible_keys are suggestions for possible indexes that might be be useful. NULL means no indexes were applied to the specific query as in this case.

key shows the actual index/key used, if any.

key_len is the length of the index/key that was used, if any.

ref shows the columns in the table that were compared to the index that was used.

rows provides an estimate for the number of rows that will be looked at when the query is run.

Extra provides additional information about the query.

Depending on how and where you run the query, you should also receive information about how long the query takes to run. I ran the EXPLAIN through phpMyAdmin and it didn’t have a time returned with the other results, however when I run the query without the EXPLAIN, phpMyAdmin tells me how long the query took to complete.

For the query to select every row of the wp_options table MySQL reported that 602554 total rows were affected and the query took 0.0002 seconds to execute.

You can get a little more information using the EXTENDED statement along with EXPLAIN. You simply add EXTENDED after EXPLAIN and then your query.

[code type==”mysql”]
EXPLAIN EXTENDED SELECT * FROM wp_options
[/code]

You’ll receive the same information as for EXPLAIN with an extra column called filtered, which gives you the percentage of rows filtered by the table condition, in the case of my example query, 100%

Show Warnings

Another statement you can use is SHOW WARNINGS, which is used in conjunction with EXPLAIN and EXTEND. It shows additional information not included the EXPLAIN or EXTEND display. It provides information about how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after optimizing it and other notes about the process.

To use SHOW WARNINGS, you follow an EXPLAIN statement with a SHOW WARNINGS statement.

[code type==”mysql”]
EXPLAIN SELECT * FROM wp_options;
SHOW WARNINGS;
[/code]

I couldn’t find a good query against my database that would bring back any output so instead I’ll point you to the MySQL dev site which will provide more information and show you an example of what the output might look like.

Slow Query Log

Another way to get feedback about the queries your site runs is by using the slow query log, which will write any slow queries to the log file. If a query is listed it’s a good candidate for optimization.

The slow query log needs to be enabled, which you do by setting the slow_query_log configuration variable to 1 in your my.cnf file.

[code type==”mysql”]
slow_query_log = 1
[/code]

I talked about MySQL configuration a couple of months ago and I’ll refer you to that post for more details. I didn’t specifically mention the slow_query_log variable in the configuration post, but covered variables in general.

Once you have the slow query log enabled, MySQL will write any slow queries to a file you can name with another variable.

[code type==”mysql”]
–slow_query_log_file = file_name
[/code]

You can set the location of the file using another configuration variable, –log-output and setting it equal to the path where you want the file to be located.

[code type==”mysql”]
–log-output = path-to-file
[/code]

Note: You may have noticed the use of both underscores and dashes in naming these variables. I don’t know why they aren’t more consistent.

It’s possible the file might already be set up for you by your web host. In that case its location will depend on where your hosting company places it, but it’s probably something similar to /var/lib/mysql/mysql-slow.log. You might also need root access for security reasons in order to see the file.

A slow query is defined as anything that takes longer to execute than what’s set in the long_query_time configuration variable, which I think is set to 10 seconds by default and is probably a value you want to decrease.

If you want to know all the queries that don’t make use of indexes you can add a log_queries_not_using_indexes variable.

[code type==”mysql”]
slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes
[/code]

These three lines create the slow query log and define one second as a long query. The file will also log all queries that have no index.

I didn’t have any slow queries in my log to show you what the file looks like, but here’s an article with an example.

Instead of opening the slow query log file directly you can use mysqldumpslow to get a summary of the results.

[code type==”mysql”]
mysqldumpslow [options] path-to-log-file
[/code]

The command has a number of options to tailor the results you receive.

If you run WordPress, I recommend a plugin called Query Monitor. Once installed it shows information on every page of your WordPress site (both the front end and the administrative back end) in the WordPress admin bar. For every page you can find all the queries that were made and how long each took to execute.

The plugin also alerts you to pages with slow queries and there’s plenty of other information including possible PHP errors you might want to look at and optimize.

Closing Thoughts

Before you optimize slower queries for performance, you have to know which of your queries are running slow. You can test queries using the EXPLAIN, EXTENDED, and SHOW WARNINGS SQL statements or you can run the slow query log and have your worst performing queries written to the file.

Either should give you enough information to choose which queries to improve and possibly some suggestions for how to improve them.

Speaking of optimizing your queries, that’s what I’d like to focus on next week. I’ll give you a bunch of tips for writing more performant queries and then I’ll point you to additional resources for even more tips.

« Prev PostNext Post »

Download a free sample from my book, Design Fundamentals.