To trouble shoot which queries are running slow in your application you need to first check which queries are running slow. MySQL provides an easy way to log all the queries that are slow using some easy to use commands. I will describe them below
First, you need to login to mysql server using appropriate privileges to set some global variables, normally a root user would be required.
Once you login into the mysql console run the following command to see what the current status of logging of slow mysql queries. To do, that run the following command in mysql command line.
$mysql > show global variables like '%slow%';
This might show you an output like below
Look for the slow_query_log variable in the above output and see what is the status. If slow query log is enabled in your MySQL then this value will be ON otherwise it will be OFF, which means you need to turn it on.
To enable mysql slow query log run the below command in your mysql console
SET global slow_query_log=1;
This will turn enable the slow query logs in your mysql. To confirm it is enabled run again the command as described in the first step. You should see the status to ON as shown in the above screenshot.
To check the path where mysql will log the slow queries check the slow_query_log_file variable in the above screenshot.
There are other important variables like `long_query_time` which will determine which queries MySQL will consider as slow and log them in the log file. So if you set this variable to 1 second it log all those queries that take longer than 1 second to execute and give the result. You may also set this value to 0 to log all queries that take less than 1 second to execute but are called large number of times which might make your system slow. However for this tutorial I will set this limit to 1 second. To set the long_query_time variable to 1 second you need to run the following command in mysql console.
SET global long_query_time=1;
MySQL will now log all those queries take more than 1 second to execute.
There are many other such variables which help you log your slow queries which you can then later optimize to make your application faster