The Slow Query Log Saved Our Migration. Here's How to Actually Read It.

-- Enable slow query logging immediately:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Set the threshold (seconds):
-- 1.0 = capture all queries over 1 second
-- 0.1 = capture all queries over 100ms (for performance tuning)
-- 0   = capture ALL queries (only for short diagnostic windows)
SET GLOBAL long_query_time = 1;

-- Also capture queries that do not use indexes (often the most important ones):
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Verify settings took effect:
SHOW VARIABLES LIKE 'slow%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

# -s t: sort by total time (sum of all executions)
# -t 20: show top 20 queries
# -a: show real numbers, not averages
# Normalises queries by replacing literal values with N or S

mysqldumpslow -s t -t 20 -a /var/log/mysql/slow.log

# Output format:
# Count: 1847  Time=0.18s (328s)  Lock=0.00s (0s)  Rows=1.0 (1847)
# SELECT * FROM orders WHERE status = 'S' ORDER BY created_at LIMIT N
#
# Read as: ran 1847 times, averaged 0.18s, total 328 seconds consumed
# The query normalised "pending" → S and 1 → N

# -s r: sort by average rows examined
# These are your worst missing-index candidates
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# Install: apt install percona-toolkit (Debian/Ubuntu)
#          yum install percona-toolkit (RHEL/CentOS)

# Full analysis — writes a report sorted by total time:
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

# Most important output sections:
# 1. "Overall" — total time, unique query patterns, total queries
# 2. "Profile" — ranked list of queries by total time consumed
# 3. Per-query detail — pct of total, concurrency, p95/p99 latency

# Common patterns to look for:
# Exec time: 4.28s avg, 29.1s max  → high variance = parameter-sensitive plan
# Rows examine: 8.49M avg           → full table scan
# Rows sent:    1 avg               → one row returned from 8M read
# Lock time: 12ms avg               → lock contention adding latency

-- Take the worst query from the log and EXPLAIN it:
-- (replace literal values with real values for the EXPLAIN)
EXPLAIN SELECT * FROM orders
WHERE  status = 'pending'
ORDER BY created_at
LIMIT  1;

-- Key columns to read:
-- type: 'ALL' = full table scan (bad), 'ref' = index lookup (good)
-- rows: estimated rows examined. Match against Rows_examined in log.
-- Extra: 'Using filesort' = sort in memory/disk (expensive)
--        'Using index' = covering index (fast)
--        'Using where' = filter applied after index

-- For full execution details (MySQL 8.0):
EXPLAIN ANALYZE SELECT * FROM orders
WHERE  status = 'pending'
ORDER BY created_at
LIMIT  1;

-- EXPLAIN ANALYZE actually runs the query and shows actual rows/loops

-- For: WHERE status = 'pending' ORDER BY created_at
-- The correct index covers both the filter and the sort:
CREATE INDEX idx_orders_status_created
  ON orders (status, created_at);

-- Why this order matters:
-- status first: allows index seek on status = 'pending'
-- created_at second: rows already sorted within each status group
-- Without this order: MySQL would seek on status, then filesort on created_at

SELECT
  DIGEST_TEXT                                          AS query_pattern,
  COUNT_STAR                                           AS executions,
  ROUND(SUM_TIMER_WAIT/1e12, 3)                       AS total_sec,
  ROUND(AVG_TIMER_WAIT/1e12, 3)                       AS avg_sec,
  ROUND(MAX_TIMER_WAIT/1e12, 3)                       AS max_sec,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_NO_INDEX_USED + SUM_NO_GOOD_INDEX_USED           AS no_index_count
FROM   performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT  20;

-- This is available without enabling the slow query log
-- Persists across connections, reset with:
-- TRUNCATE TABLE performance_schema.events_statements_summary_by_digest