SnapShooter Backups Server, Database, Application and Laravel Backups - Get fully protected with SnapShooter

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.