Slow Query Analyzer

Slow Query Analysis

A systematic approach: enable slow log → aggregate with pt-query-digest → EXPLAIN → optimize → verify. This solves 90% of MySQL performance issues.

Enable Slow Log

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

pt-query-digest

pt-query-digest --limit 20 /var/log/mysql/mysql-slow.log
pt-query-digest --processlist h=localhost,u=root,p=pass --interval 1

EXPLAIN Key Fields

• type: const > eq_ref > ref > range > index > ALL (ALL is worst)
• key: actual index used (NULL = no index)
• Extra: Using filesort / Using temporary = needs optimization

EXPLAIN ANALYZE (8.0.18+)

Actually executes the query and shows actual vs estimated rows — reveals when optimizer statistics are stale.

Common Patterns

• Function on indexed column → rewrite to range scan
• Deep pagination (LIMIT 100000, 20) → keyset pagination
• SELECT * causing table lookups → covering indexes
• OR across different columns → UNION ALL