One of MySQL’s hidden gems is the SLOW QUERY LOG parameter. Enabling this feature captures queries which take more than a certain amount of time (the default is 10 seconds) and initiates a log file with these slow queries. This proves to be a very useful tool in diagnosing the potential cause of performance issues on the database and the server. You can use this feature to figure out which queries perform full table scans and could benefit from an index being added.

There is a lot of detail which is logged with each slow query in the log file:

• Time – execution time
• Lock – duration of lock
• Rows – the rows being queried
• Host
• The query

(Ducea, 2006)

 

How to enable SLOW_QUERY_LOG parameter

1. Log into mysql with root user

          mysql –u root –p

2. Type in the password for root
3. Enable the slow queries log parameter

          SET GLOBAL slow_query_log = ‘ON’;

 

How to change the time for slow queries

          SET GLOBAL long_query_time = X;

 

How to change the location of the slow query log file saved

          SET GLOBAL slow_query_log_file = X;

 

How to disable SLOW_QUERY_LOG parameter

          SET GLOBAL slow_query_log = ‘OFF’;

(C, 2014)

 

How to test that SLOW_QUERY_LOG has been enabled

          SELECT SLEEP(X);

where X is higher than the amount set in long_query_time.

 

Seeing as sleep for a time longer than long_query_time would be considered as a ‘slow query’, it will be captured and logged. You can then open the log file to see if it has indeed been saved to the file.

(C, 2014)

Using the logged queries, it is recommended that the user goes through the list of queries, adding indexes where necessary. In case that the queries have been converted and made more efficient, re-enable the slow_query_log parameter to see if there are any improvements. It is recommended to minimize slow queries to maintain a high level of performance.

(Identify slow query without slow query logs in mysql server, 2015)

 

References

C, C. (2014, June 20). How to enable the slow query log in MySQL. Retrieved from A2 Hosting: https://www.a2hosting.com/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql

Ducea, M. (2006, November 6). Identifying MySQL Slow Queries. Retrieved from The Journal of a Linux Sysadmin: http://www.ducea.com/2006/11/06/identifying-mysql-slow-queries/

Identify slow query without slow query logs in mysql server. (2015, May 15). Retrieved from DBA stack exchange: https://dba.stackexchange.com/questions/101623/identify-slow-query-without-slow-query-logs-in-mysql-server