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.

Table Of Content

  1. Enable MySQL slow query log
  2. Clean up
  3. More useful techniques
  4. The end
Enable MySQL slow query log
  • Login to MySQL by running the command below:
  • Select the database you are trying to debug:
  • Enable slow query log:
  • Set the query running time threshold in seconds:
  • We can verify if the settings are done properly by running the command below:

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

  • Test if the logging is working:

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:
  • Verify if it is turned off successfully:
More useful techniques

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

  • Running query without cache by adding the keyword SQLNOCACHE:
The end

We hope you find this tutorial helpful.