MySQL Icon

MySQL: Identifying Slow SQL Queries

If you have a database driven site, like this one, the database will slowly increase in size and the tables will get bigger as your site content and traffic increases. Over time some areas that were fast to load in the browser start to slow down. Why is that? Well one possibility is that the database queries that generate the content on your page are not optimized. As your database keeps growing, these queries get slower and slower until the load time of a page is actually noticeable to one of your visitors. The first step is identifying slow queries.

Find your MySQL Config File

MySQL logs any queries into its slow log, if the query takes longer than a certain threshold to execute. The first thing to do is check our MySQL config file. It is usually located at /etc/my.cnf on your web server. If you do not have access to this on your web server or are using shared hosting, please contact your provide and see if you can access the slow query logs.

Through SSH, running the following will output the MySQL config file:

[pre lang=’unix’]
cat /etc/my.cnf
[/pre]

Now there is two options we want to look for. One is log-slow-queries which tells us where the slow log is stored and long_query_time which tells us the threshold in seconds for logging a slow query. Mine looks like this:

[pre lang=’text’]
log-slow-queries=/var/lib/mysqllogs/slow-log
long_query_time=2
[/pre]

This tells me that any query over 2 seconds will be logged to the /var/lib/mysqllogs/slow-log file.

Decipher the Slow Query Log

A typical entry in this file will look like the following:

[pre lang=’text’]
# Time: 100808 4:23:11
# User@Host: unix_user[unix_user] @ localhost []
# Query_time: 6 Lock_time: 0 Rows_sent: 78155 Rows_examined: 233997
SELECT * FROM table WHERE … ORDER BY …;
[/pre]

This tells me the query was run on Aug. 8th 2010 at 4:23AM. It took 6 seconds to run. It didn’t need to wait because of any other queries locking up the table it required. 78155 rows were returned and 233997 rows were examined. Next it provides the slow query that was run.

From here the best thing is to do is examine the tables in question and check to make sure the query is using as many indexes as possible. I will touch base on examining queries using EXPLAIN in a future post.

Continue Reading