Skip to main content

Performance Optimization: MySQL Slow-Query Log

Category: MySQL

For optimizing a website's performance it might be helpful to identify slow queries. To activate the slow query log your database user needs to have permission to set global variables.

Activating the Slow-Query-Log:

Set global slow_query_log = 'ON';

Defining path to logfile:

Set global slow_query_log_file ='/var/log/mysql/slow-query.log';

Defining minimal execution time for logging:
The slow query log consists of SQL statements that took more than long_query_time seconds. The minimum and default values of long_query_time are 0 and 10, respectively

Set global long_query_time = '20';

Activate logging for queries not using indexes:

Set global log_queries_not_using_indexes = 'ON'