MySql Slow query Log.

If you want to increase your application performance you need to find out where your system is going to slow. For MySQL-based applications, you can easily find out which query is slow just by enabling the slow query log.

mysql -u root -p

SET GLOBAL slow_query_log = 'ON';

By default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run. But you can change this interval time by type the following command, replacing X with the time in seconds:

mysql -u root -p

> SET GLOBAL long_query_time = X;

Initially location is: /var/lib/mysql/hostname-slow.log.
But you can change the query log location:

mysql -u root -p

> SET GLOBAL slow_query_log_file = '/path/filename';

We can also indicate to log queries which not using indexes. which is very helpful.

mysql -u root -p

> SET GLOBAL log_queries_not_using_indexes = 1;

To find out the current slow query log location.

mysql -u root -p

> show global variables like 'slow%log%'; 

The parameter changes are lost after MySQL reboots because the parameters are set dynamically, so in order to make the changes permanent, we need to set up the parameters in the MySQL configuration file (/etc/mysql/my.cnf)

[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 5
log-queries-not-using-indexes = 1

We can also check the variable/parameters values as 

> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
> SHOW GLOBAL VARIABLES LIKE 'long_query_time';