Slow Query Log
IntermediateEnable slow_query_log with a long_query_time threshold to capture expensive queries; use mysqldumpslow or pt-query-digest to identify and rank the worst offenders.
Overview
The slow query log is MySQL's built-in mechanism for capturing queries that exceed a configurable execution time threshold. It is the primary discovery tool for finding the worst-performing queries in a production system — before you know which query to EXPLAIN, you first need to identify it. By setting long_query_time (e.g., 1 second) and enabling log_queries_not_using_indexes, MySQL writes the offending query, its execution time, rows examined, and the lock time to the slow log. The raw log is verbose — use mysqldumpslow (bundled with MySQL) or pt-query-digest (Percona Toolkit) to aggregate, sort, and rank queries by total execution time, call count, or average latency.
Enabling and Configuring the Slow Query Log
The slow query log can be enabled at runtime (no restart needed) or in my.cnf for persistence. key variables: - `slow_query_log` — ON/OFF toggle - `long_query_time` — threshold in seconds (supports decimals: 0.1 = 100ms) - `slow_query_log_file` — path to the log file - `log_queries_not_using_indexes` — also log queries that skip indexes even if fast - `log_slow_extra` — log additional fields (threads, memory) in MySQL 8.0+
-- Enable at runtime (no restart needed)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- log queries taking > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON'; -- also catch full-table scans
-- Verify settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- Persist in my.cnf (survives restart)
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5 # 500ms threshold for busy production apps
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10 # limit to 10 per minute (avoids log flood)
-- Check how many slow queries have been captured
SHOW GLOBAL STATUS LIKE 'Slow_queries';Analysing the Log — mysqldumpslow and pt-query-digest
Raw slow log files can be gigabytes. These tools aggregate queries by normalised form (removing literal values) and rank them:
**mysqldumpslow** — bundled with MySQL; simple aggregation by count, total time, or average time.
**pt-query-digest** — Percona Toolkit; full statistical analysis (percentiles, stddev), can output to a MySQL report table, and integrates with monitoring tools. The gold standard for slow log analysis.
# mysqldumpslow — top 10 slowest queries by total time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s c = sort by count (most frequent)
# -s t = sort by total time (highest impact)
# -s at = sort by average time (consistently slow)
# -t 10 = top 10
# -r = reverse sort
# Sample mysqldumpslow output:
# Count: 1234 Time=2.30s (2843s) Lock=0.00s (0s) Rows=1.0 (1234)
# SELECT * FROM orders WHERE YEAR(created_at) = S
# ↑ 1234 calls × 2.3s avg = 2843s total — fix this first!
# pt-query-digest — full statistical analysis (recommended)
pt-query-digest /var/log/mysql/slow.log
# pt-query-digest output shows:
# Rank Query ID Response time Calls R/Call V/M Item
# 1 0xABC123 2843.00 35.1% 1234 2.3000 ... SELECT orders
#
# Top query analysis (profile, example, EXPLAIN output)Workflow — From Slow Log to Fix
The slow query log slots into a repeatable performance improvement workflow:
1. Enable slow log with appropriate threshold (start at 1s, lower to 100ms later). 2. Run the application under production load for 1–24 hours. 3. Analyse with pt-query-digest — identify top 5 queries by total time. 4. Run EXPLAIN (and EXPLAIN ANALYZE) on each candidate. 5. Add missing indexes, rewrite inefficient patterns, update statistics. 6. Verify with EXPLAIN that the plan improved; monitor slow log for regression.
-- Example workflow
-- Step 1: Identify from pt-query-digest output
-- Worst query: SELECT * FROM orders WHERE YEAR(created_at) = 2024
-- Calls: 5000, Avg: 1.8s, Total: 9000s
-- Step 2: EXPLAIN
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- type: ALL, rows: 500000, key: NULL — full table scan
-- Step 3: Fix — rewrite as range
EXPLAIN SELECT id, total, status
FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- type: ALL still — missing index on created_at
-- Step 4: Add index
ALTER TABLE orders ADD INDEX idx_created_at (created_at);
-- Step 5: Verify
EXPLAIN SELECT id, total, status
FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- type: range, key: idx_created_at, rows: 12000 ← much better
-- Step 6: Monitor slow log — YEAR() query no longer appears
SHOW GLOBAL STATUS LIKE 'Slow_queries'; -- count should plateauKey Points to Remember
- 1Enable slow_query_log at runtime with SET GLOBAL — no MySQL restart needed.
- 2Start with long_query_time=1 in production; lower to 0.1–0.5 once you've fixed the worst offenders.
- 3log_queries_not_using_indexes captures harmful full-table scans even when they are fast on small datasets.
- 4Use pt-query-digest (not mysqldumpslow) for production analysis — it gives full statistics, percentiles, and can output to a report table.
- 5Sort by total time (not average) to identify the highest-impact queries — a 0.1s query called 100 000 times matters more than a 5s query called once.
- 6The slow log is discovery; EXPLAIN is diagnosis; adding indexes/rewriting is the fix.
Interview Questions
Sign in to ask AriaHow do you enable the slow query log in MySQL without restarting the server?
What does log_queries_not_using_indexes capture and why is it useful?
What is the difference between mysqldumpslow and pt-query-digest?
You have 1 million slow log entries. How do you identify the queries to fix first?
Describe the end-to-end workflow for finding and fixing a performance problem in MySQL.
Ask Aria about Slow Query Log
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.