MySQL Slow Query Log: Enable and Analyze Slow Queries

What is Slow Query Log

The Slow Query Log records SQL queries that take longer than a set threshold. It is the primary tool for diagnosing database performance — showing which queries are overloading the server.

Enable in my.cnf

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Enable Without Restart

mysql -u root -p -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;"

Analyze with mysqldumpslow

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

Analyze with pt-query-digest

sudo apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log

Use EXPLAIN to Optimize

EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';