MySQL's Over-looked and Under-worked Slow Query Log

The slow query log is one of the less-used logs, as by default it's not activated, but it's a useful log for identifying queries that are not optimal. Often, in an under-performing system, especially with the default MyISAM tables (that make use of table-level locking, not row-level locking), a single query may be the cause of problems.

To activate the query log, simply place:
log-slow-queries = [slow_query_log_filename]
in your configuration file (my.cnf or my.ini), slow_query_log_filename being the optional filename for your log file. If you don't supply a filename, the default name will be used, which is the name of the host machine, with -slow.log being appended.

The slow query log logs all queries that take longer than long_query_time, which is usually 10 seconds by default (more than long enough for a self-respecting query to complete). You can alter the long_query_time in the configuration file. The following example sets the time to 5 seconds:
set-variable = long_query_time = 5
The slow query log can also optionally log all queries that don't use an index by placing the following in the configuration file:
log-long-format

Let's take a look at a sample slow query log:

/usr/local/mysql/libexec/mysqld, Version: 3.23.54-log, started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 030207 15:03:33
# User@Host: wsuser[wsuser] @ localhost.localdomain [127.0.0.1]
# Query_time: 13  Lock_time: 0  Rows_sent: 117  Rows_examined: 234
use wsdb;
SELECT l FROM un WHERE ip='209.xx.xxx.xx';

The first few lines simply contain version information, but the really useful data begins with the Time. This tells you what time the query completed. The next line contains user data, and the following how long the query took to run, how long it took to secure its locks, how many rows were sent back as a result, and how many rows were examined to determine the result. The final line before the actual query tells you which database was used. A discussion on optimizing queries is beyond the scope of this article, but you should know something about this before you can get the most out of the log. Otherwise, you may be able to identify the un-optimal queries, but not be able to do much with them. You should read the article Optimizing MySQL: Queries and Indexes if you are unclear about the topic.