How to Find and Fix Slow Queries in MySQL

A methodical workflow beats random tweaking: capture the slow queries, understand why they're slow, apply the smallest fix, and verify. Here's the loop professionals run.

Key Points

• Capture: enable slow_query_log; summarise with mysqldumpslow or pt-query-digest
• Live view: SHOW PROCESSLIST spots queries running right now
• Diagnose: EXPLAIN ANALYZE the worst offenders
• Common fixes: add a missing index, remove functions from WHERE, reduce selected columns
• Rewrite anti-patterns: correlated subqueries → JOINs or window functions
• Verify: measure runtime before and after — keep evidence of each win