Tuning Your MySQL Database Queries That Execute in Less Than One Second Using the Slow Query Log

Prior to MySQL 5.1.21, it was not possible to use the existing tools in MySQL to easily identify queries that took less than a second to execute but were still considered slow within your environment. Beginning in MySQL 5.1.21, MySQL allows for queries to be logged to the slow query log down to the microsecond level using the long_query_time dynamic variable. Also, beginning in MySQL 5.1.29, the MySQL variable log_slow_queries was deprecated by the new slow_query_log variable that enables you to turn on the slow query log without needing to restart MySQL (no downtime).

If you want to enable the slow query log in MySQL 5.1.21 or later, use the following parameters:

slow_query_log = 1 # Turn on the slow query log
long_query_time=1 # Log all queries to the slow query log that take a second or longer
slow_query_log_file=/var/log/mysql/log-slow-queries.log # or modify the path to where you want the log located.

Logging Queries that take less than a second:

If you would like to log queries to the slow query log that take less than a second to execute, specify the syntax in milliseconds using the format below:

long_query_time = 0.5 # Half a second or 500 milliseconds

There are 1000 milliseconds in one second so therefore 500 milliseconds will equal a half of a second.

From there, we recommend using MySQL Explain to tune your MySQL queries.

Tags: , , ,

No comments yet.

Leave a Reply