• Debug MySQL slow query
  • Enable MySQL slow query log
  • Clean up
  • More useful techniques
  • The end

#Debug MySQL slow query

Slow MySQL query is a common cause of the application bottleneck. Slow queries are often discovered when a large dataset is added, which means they often happen in a production environment.

In this tutorial, we will learn how to debug slow MySQL queries.

#Enable MySQL slow query log

• Login to MySQL by running the command below:

mysql -u root -p

• Select the database you are trying to debug:

use my_database

• Enable slow query log:

SET GLOBAL slow_query_log = 'ON’;

• Set the query running time threshold in seconds:

SET GLOBAL long_query_time = X;

• We can verify if the settings are done properly by running the command below:

show variables like '%slow%’;

The command above would show the log file path as well.

• Test if the logging is working:

SELECT SLEEP(X);

The log file should show a log belongs to the query above.

Now we have successfully enabled MySQL slow query log and we can now investigate and debug which query is causing the problem.

#Clean up

When we have identified the problem, always remember to turn off the slow query log since it would add additional time to each query we run.

• We can run the command below to turn it off:

SET GLOBAL slow_query_log = ‘OFF’;

• Verify if it is turned off successfully:

show variables like '%slow%’;

#More useful techniques

We also introduce some useful MySQL commands you may find useful.

• Running query without cache by adding the keyword SQLNOCACHE:

select SQL_NO_CACHE * from mytable where 1 limit 1;

#The end

We hope you find this tutorial helpful.