SQL

Use the Slow Query Log in MySQL

Slow queries can cause poor database performance. MySQL has a slow query log, which lets you log queries that take too long (with too long a user-configurable setting). This allows you to quickly find craptastic queries in your logs. To enable the slow query log, use SET GLOBAL and then set the slow_query_log option to ON:

SET GLOBAL slow_query_log = 'ON';

By default, the slow query log identifies any query that takes 10 seconds or longer. To change long_query_time using SET GLOBAL. Here, we’ll set it to 7 seconds:

SET GLOBAL long_query_time = 7;

You can also set the log file to something other than the default location of /var/lib/mysql/hostname-slow.log. Again, use SET GLOBAL and then provide a path for slow_query_log_file:

SET GLOBAL slow_query_log_file = '/var/logs/slowquery.log';

To disable the slow query log:

SET GLOBAL slow_query_log = 'OFF';