How to Enable MySQL's Slow Query Log

A familiar situation: your MySQL-powered website suddenly slows down, and you do not know why.

Inefficient queries may be the cause. Enable MySQL's slow query log to find them.

Open the MySQL configuration file my.cnf:

sudo nano /etc/mysql/my.cnf

and uncomment or add the following lines:

slow-query-log      = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time     = 3
log-queries-not-using-indexes = 1
  • The slow_query_log option specifies the path to the query log file.
  • The long_query_time option specifies the minimum execution time in seconds for a query to be considered slow.

Restart MySQL:

service mysql restart

Slow queries will now be recorded in /var/log/mysql/mysql-slow.log. Monitor them in real time using tail:

tail -f /var/log/mysql/mysql-slow.log

That is all you need to enable MySQL's slow query log.