MySQL interview question · Most asked
How do you find the slowest queries in a MySQL server?
Summary
Enable the slow query log (long_query_time, optionally log_queries_not_using_indexes) and summarise it with pt-query-digest, or query performance_schema.events_statements_summary_by_digest (and the sys schema views) to rank normalised queries by total time.
Longer Answer
Rank by total time (calls x average), not just the single slowest execution: a 5 ms query called 10 million times a day matters more than one 10-second report. sys.statement_analysis and sys.statements_with_full_table_scans are quick starting points. Then EXPLAIN the top offenders, fix indexes or queries, and compare before and after. APM tools show the same data per endpoint.
Key Points
- Slow query log + pt-query-digest.
- performance_schema digests / sys schema.
- Rank by total time.
- Fix the top few, measure again.
More most-asked MySQL questions
- What is a clustered index in InnoDB, and why does primary key choice matter so much?
- Why is OFFSET pagination slow on large tables, and what is keyset pagination?
- How do you read EXPLAIN output in MySQL?
- Why might MySQL not use an index you created?
- What are the transaction isolation levels, and which does InnoDB use by default?
- How does a composite index work, and what is the leftmost-prefix rule?
- What causes deadlocks in MySQL, and how do you handle them?
- What are gap locks and next-key locks, and why do they surprise developers?