How to enable Slow Query Log in MySQL

Enabling slow_query_log and log_queries_not_using_indexes will help to identify slow queries and queries without indexes that affect the performance of the MySQL server.

Connect to the MySQL server and see the current settings:

mysql -u root -p
SHOW GLOBAL VARIABLES LIKE 'slow\_%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL VARIABLES LIKE 'log_queries_not_using_indexes';

You can change these parameters without restarting MySQL (I note that in versions of MySQL below 5.7 slow_query_log is called log_slow_queries):

SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 5;
SET GLOBAL slow_query_log = 'ON';

To enable query logging without indexes:

SET GLOBAL log_queries_not_using_indexes = 'ON';

long_query_time indicates the minimum query execution time in seconds, after which it is considered slow, by default 10 seconds.

You can also specify these parameters in the configuration file:

slow_query_log=ON
slow_query_log=/var/log/mysql/slow.log
long_query_time=5
log_queries_not_using_indexes=ON

And restart the MySQL server to apply the changes:

sudo /etc/init.d/mysql restart

For a more readable format, you can use the pt-query-digest utility:

apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log > /var/log/mysql/slow_ixnfo.com.txt

Do not forget to disable these parameters after the analysis of logs is completed:

SET GLOBAL slow_query_log = 'OFF';
SET GLOBAL log_queries_not_using_indexes = 'OFF';

See also my articles:
Time mismatch in error logs and slow queries logs
Installing and using mysqltuner.pl
How to change join_buffer_size