The MySQL Slow Query Log

-- Enable the log
SET GLOBAL slow_query_log = 'ON';

-- Log queries that take longer than 1 second
SET GLOBAL long_query_time = 1;

-- Check where the log file is being written
SHOW VARIABLES LIKE 'slow_query_log_file';

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

# Time: 2024-03-15T14:23:01.234567Z
# User@Host: app_user[app_user] @ localhost []  Id: 142
# Query_time: 4.521893  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 847293
SET timestamp=1710509381;
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;

SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Log at most 10 no-index queries per minute
SET GLOBAL log_throttle_queries_not_using_indexes = 10;

# Top 10 queries by total execution time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 by average execution time
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# Top 10 queries that examined the most rows
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

Count: 523  Time=4.52s (2364s)  Lock=0.00s (0s)  Rows=1.0 (523), app_user@localhost
  SELECT * FROM orders WHERE status = 'S' ORDER BY created_at LIMIT N

EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;

SET GLOBAL log_output = 'TABLE';
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;