MySQL slow query log in practice: workflow from log to fix
What is your workflow for working with the MySQL slow query log? We have it enabled with long_query_time=1 but the volume of logs makes it hard to process manually.
Tools, query aggregation, where to start?
pt-query-digest from Percona Toolkit is the standard tool. It aggregates slow query log entries by normalized query pattern and shows you the top offenders by total time, not just per-query time. Install it and run it on a day of logs, the output is actionable.
Lower long_query_time to 0.1 seconds on staging if you want to catch more queries. On production 0.5 is usually reasonable without flooding the log.
MySQLWorkbench has a built-in slow query analyzer that is easier for one-off investigations. pt-query-digest is better for automation.
After you find the slow query, run EXPLAIN and look for full table scans (type = ALL) and high row counts in the Extra column. Those are your targets. Missing index or missing composite index covers most cases.
One thing people miss: queries that are fast individually but run 5000 times per minute. pt-query-digest sorts by total time which catches these. A 50ms query that runs 10k times is worse than a 2s query that runs once.
We use Datadog APM which captures slow queries automatically with query sampling. More expensive but you get correlation with request traces which makes root cause analysis much faster.