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';