MySQL Slow Query Log: Enable and Analyze Slow Queries

What the Slow Query Log Is

The Slow Query Log records SQL queries that take longer than a set threshold. It is the main tool for diagnosing database performance: it shows which queries load the server and helps you find the queries that need indexes.

Enabling It 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
min_examined_row_limit = 100

# Apply without a restart (MySQL 5.1+)
mysql -u root -p -e "
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;"

Slow Query Log Parameters

Analysis with mysqldumpslow

# Top 10 slowest queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 by number of executions
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Sorted by avg time
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

Analysis with pt-query-digest (recommended)

# Install Percona Toolkit
sudo apt install percona-toolkit

# Analyze the slow log
pt-query-digest /var/log/mysql/slow.log

# Top queries for the last 24 hours
pt-query-digest --since=24h /var/log/mysql/slow.log

# Write the output to a file
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

Using EXPLAIN for Optimization

-- Analysis of a specific query
EXPLAIN SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';

-- Detailed analysis (MySQL 5.6+)
EXPLAIN FORMAT=JSON SELECT ...;

-- Execute and show the real plan
EXPLAIN ANALYZE SELECT ...;

Reading a Typical slow.log Entry

The log line alone does not tell you where the problem is — you need to look at the specific fields.

# Time: 2026-09-30T10:15:32.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 4.812345  Lock_time: 0.000112 Rows_sent: 1  Rows_examined: 842103
SELECT * FROM orders WHERE customer_email = 'user@example.com';

Query_time is how long the query took overall. Lock_time is how much of that was spent waiting for a lock; if it is large relative to Query_time, the issue is not a missing index but contention for a row or table. Rows_examined far exceeding Rows_sent (842103 vs 1) is a classic sign of a missing index: MySQL read almost the whole table to find a single row.

Lock Wait Timeout Exceeded in the Context of the Slow Log

If slow.log regularly shows queries with a large Lock_time, and the application error log shows Lock wait timeout exceeded; try restarting transaction, it is the same problem: a long transaction holds a row lock, and other queries sit in the wait queue beyond innodb_lock_wait_timeout (50 seconds by default). In this case the slow log shows not just the culprit (the one holding the lock) but also the victims — queries that are short by design but long in practice.

When the Slow Log Will Not Help

The slow log only records queries that have already finished — if the server is stuck on a query that has been running for hours and has not completed, it will not appear in the log until it finishes or times out. For that case you need a real-time SHOW PROCESSLIST, not a post-mortem log analysis. The slow log also will not show the cause of load from a large number of fast queries (5 ms each, but 50,000 per second) — that needs an application-level profiler or SHOW STATUS LIKE 'Questions' to estimate overall QPS. It also helps to watch SHOW STATUS LIKE 'Slow_queries' over time — a sharp jump in the counter over a short period pinpoints the moment of degradation more precisely than a one-off look at the log file.

Rotation and Disk Footprint of the Slow Log

Overall settings affecting server disk and memory are covered in the article on my.cnf optimization, and common missing-index problems are covered in the article on MySQL indexes.