Finding Slow Queries with the MySQL Slow Query Log

The slow query log records every query that takes longer than a configured threshold — the single most direct way to find out what’s actually slowing your database down, rather than guessing.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- log queries taking over 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

# mysqldumpslow summarizes the log, grouping similar queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# pt-query-digest (Percona Toolkit) gives much richer analysis
pt-query-digest /var/log/mysql/slow.log

Set long_query_time low enough to actually catch problems (1 second is a reasonable starting point for most web apps) but not so low that the log fills with noise from normal queries — tune it based on your application’s actual latency budget.