Every conversation about a slow MySQL database should start in the same place: the slow query log. It is the one tool that tells you, from real traffic, which queries actually cost you time, instead of the ones you assume are the problem. Turn it on, read it properly, and the list of things to fix stops being a guess and becomes a short, ranked to-do list.
This guide covers turning it on safely, reading it, and turning the worst offenders into fast queries.
How do you turn on the slow query log?
You can enable it live, no restart required:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';long_query_time = 1 logs any query taking longer than one second, a good starting threshold for catching what users feel. New connections pick this up immediately.
To make it permanent, put the same settings in the [mysqld] section of my.cnf so they survive a restart:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1That last line is worth turning on for a while. It logs queries doing full table scans even when they are fast today, which is exactly how you catch the query that will get slow the moment the table grows.
How do you read the slow query log?
The raw file is readable but repetitive: the same query shows up hundreds of times with different values. You want it grouped and ranked. mysqldumpslow ships with MySQL and does this:
# Worst by total time spent (usually where to start)
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# Worst by number of executions
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# Worst by average rows examined
mysqldumpslow -s r -t 10 /var/log/mysql/slow.logSort by total time first (-s t). A query that takes 200ms but runs 50,000 times a day costs you far more than a two-second report that runs once, and only total time shows that.
For anything serious, pt-query-digest from Percona Toolkit is the better tool. It gives each query a full time distribution, so you see not just the average but the worst cases:
pt-query-digest /var/log/mysql/slow.logWhat do you do with what you find?
For each query at the top of the list, the loop is the same:
- EXPLAIN it. See how MySQL runs it:
EXPLAIN SELECT * FROM orders WHERE customer_id = 4471 AND status = 'open';A type of ALL means a full table scan and a missing index. Using filesort or Using temporary means an expensive sort.
- Add the index it needs, matching the columns in the
WHEREandORDER BY:
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);- Rewrite it if the query itself is the problem, for example selecting only the columns you use instead of
SELECT *, or removing a function wrapped around an indexed column.
- Re-run and confirm. The same query should drop off the slow log. Choosing the right index, especially composite ones, is where the complete MySQL optimization guide goes deeper.
Slow query log or Performance Schema?
Both, for different jobs. The slow query log is the durable record you leave running to catch offenders over hours or days. The Performance Schema and its friendlier sys views give you a live, in-memory ranking right now:
SELECT query, exec_count, total_latency, rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;Use sys when you are firefighting and need the current picture, such as when the server is pinned at 100% CPU. Use the slow query log when you want to find everything that has been slow, not just what is slow this minute.
Two gotchas worth knowing
The log can fill the disk. At long_query_time = 0 on a busy server, the file grows fast. Capture for a defined window, then set the threshold back up and rotate or truncate the file.
Admin statements are logged too. Backups and ALTER operations are legitimately slow and will clutter the log. log_slow_admin_statements = 0 keeps them out so you stay focused on application queries.
Frequently asked questions
How do I enable the MySQL slow query log without restarting?
Set it at runtime with SET GLOBAL slow_query_log = 'ON' and SET GLOBAL long_query_time = 1. That takes effect immediately for new connections. To make it survive a restart, add the same settings to the [mysqld] section of my.cnf.
What is a good value for long_query_time?
Start at 1 second to catch the queries users actually feel. Once those are fixed, lower it to 0.5 or 0.2 to find the next tier. Setting it to 0 logs every query, which is useful for a short capture but fills the disk fast in production.
How do I read the slow query log?
Use mysqldumpslow to group similar queries and sort them, for example mysqldumpslow -s t to sort by total time. For deeper analysis, pt-query-digest from Percona Toolkit gives per-query time distribution and is the tool most professionals reach for.
Should I log queries that don't use indexes?
Yes, temporarily. log_queries_not_using_indexes catches full table scans that are fast today because the table is small but will become slow as it grows. Review them, add indexes where needed, then turn it back off so the log stays focused.
Does the slow query log slow MySQL down?
The overhead is small at a sensible long_query_time, because only slow queries get written. The real risk is disk: at long_query_time = 0 on a busy server the log grows quickly, so cap your capture window and rotate the file.
Found the slow queries but not sure which index actually fixes them, or short on time to work through the list? That is the job I do. See MySQL performance tuning, or read the full MySQL optimization guide first.