If you need to identify slow queries on a production MySQL server you may benefit from using the MySQL Slow Query Log. When the MySQL server is started with the --log-slow-queries option, it writes all queries that take longer than a configurable number of seconds to a log file. The queries in the Slow Query Log can be further examined and optimized.

The Slow Query Log can also be activated by adding the log-slow-queries directive to the [mysqld] section of your server option file, or through the MySQL Administrator:

Figure 25.1. Activating the Slow Query Log using MySQL Administrator

The Slow Queries Log option is found in the Log Files tab of the Startup Variables screen. Click the clipboard icon to the left of the option to activate the Slow Query Log and click the Apply Changes button. Once the Slow Query Log is activated, restart the MySQL server using the Service Control screen.

The default name of the log file is server-name-slow.log. If your server is named doomhammer.myserver.org, the log file will be named doomhammer.myserver.org-slow.log.

The Slow Query Log is a plain-text file that contains three lines for every query logged:

          # User@Host: root[root] @ localhost [127.0.0.1]
          # Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
          SELECT last_name, first_name, actor_id FROM actor WHERE last_name = 'Brando';
        

The first line lists the username and hostname of the user who executed the query. The second line lists the time taken to execute the query, the time required to acquire the necessary locks, the number of rows returned by the query, and the number of rows the MySQL optimizer needed to examine. The final line of the entry shows the query that was executed.

The Slow Query Log can also be read using the MySQL Administrator using the Slow Log tab of the Server Logs screen:

Figure 25.2. Viewing the Slow Query Log with MySQL Administrator

The Slow Queries Log determines whether a query is slow by how long the query takes to execute in seconds, not counting the time required to acquire table locks. The default time is two seconds and can be adjusted by setting the long_query_time option in the [mysqld] section of the server configuration file. The long_query_time option can also be set using the Log Files tab of the Startup Variables screen of MySQL Administrator.

It should be noted that queries can appear in the Slow Query Log even if they are properly optimized if the server load is high enough to cause the query to take longer than the long_query_time.

If you wish to log all queries that do not use indexes, regardless of how long the queries take to execute, add the log-queries-not-using-indexes option to the [mysqld] section of your MySQL server configuration file, or check the Log queries that don't use indexes option of the Log Files tab of the Startup Variables screen of MySQL Administrator.