MySQL’s slow query log is your second set of eyes, catching the database operations that are dragging your application down.
Let’s see it in action. Imagine a simple SELECT statement that’s taking too long:
SELECT * FROM users WHERE registration_date < '2023-01-01' AND last_login < '2022-06-01';
If this query is slow, the slow query log will capture it.
Here’s what a typical slow query log entry looks like:
# Time: 2023-10-27T10:30:00.123456Z
# User@Host: webapp[webapp] @ localhost [] Id: 12345
# Query_time: 5.678901 Lock_time: 0.000123 Rows_sent: 150 Rows_examined: 500000
# SQL_no_cache: 1 Client_log_id: 54321
SET timestamp=1698388200;
SELECT * FROM users WHERE registration_date < '2023-01-01' AND last_login < '2022-06-01';
The key fields are Query_time (how long the query took) and Rows_examined (how many rows the database had to look at). A high Rows_examined relative to Rows_sent is a classic indicator of missing indexes.
To enable the slow query log, you need to modify your MySQL configuration file (usually my.cnf or my.ini). You’ll need to set a few parameters:
slow_query_log = 1: This literally turns the slow query log on.slow_query_log_file = /var/log/mysql/mysql-slow.log: This specifies the path where the log file will be written. Make sure the MySQL user has write permissions to this directory.long_query_time = 2: This is the threshold in seconds. Any query taking longer than this will be logged. Start with a reasonable value like2or5seconds, and adjust as needed.log_queries_not_using_indexes: This is optional but highly recommended. It logs queries that examine a large number of rows but don’t use an index.
After editing the configuration file, you’ll need to restart the MySQL server for the changes to take effect.
sudo systemctl restart mysql
Once enabled, you can analyze the log file. The most common tool for this is mysqldumpslow. It aggregates similar queries and reports them by how often they occur or how much time they consume.
To see the slowest queries by execution time:
mysqldumpslow -s t /var/log/mysql/mysql-slow.log
To see the queries that examined the most rows:
mysqldumpslow -s r /var/log/mysql/mysql-slow.log
To see the top 10 slowest queries:
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
The output of mysqldumpslow will show you a summary of your slowest queries, grouped by their structure, along with counts and average times. For example:
Count: 5 Time: 25.12s (5.02s/query) Lock: 0.01s (0.00s/query) Rows_sent: 750 Rows_examined: 250000
SELECT * FROM users WHERE registration_date < '%s' AND last_login < '%s'
This tells you that the SELECT * FROM users WHERE registration_date < ... AND last_login < ... query appeared 5 times, took a total of 25.12 seconds, and on average examined 50,000 rows per execution.
The most common reason for slow queries logged here is a lack of appropriate indexes. When Rows_examined is significantly higher than Rows_sent, MySQL is likely performing a full table scan. Adding an index on the columns used in the WHERE clause (registration_date and last_login in our example) can drastically reduce Rows_examined and Query_time.
For instance, if the query is frequently used with registration_date and last_login, you might add a composite index:
ALTER TABLE users ADD INDEX idx_reg_last_login (registration_date, last_login);
This allows MySQL to quickly locate the relevant rows without scanning the entire table. The Query_time will drop, and Rows_examined will decrease to a number closer to Rows_sent.
Another common cause is inefficient query structure. Sometimes, a query can be rewritten to be more efficient, even with indexes. For example, avoiding SELECT * if you only need a few columns, or using EXISTS instead of COUNT(*) in certain subqueries.
Locking can also be a bottleneck. If Lock_time is high, it indicates that queries are waiting for table or row locks to be released. This often points to long-running transactions or poorly optimized UPDATE or DELETE statements that hold locks for too long. Analyzing transactions and breaking them into smaller chunks can help.
Queries involving complex joins, especially on large tables without proper indexing on the join columns, will also surface in the slow query log. Ensure that the columns used in JOIN ON clauses are indexed on both tables.
Finally, consider the SET timestamp=...; lines. If you see many such entries, it might indicate that your application is setting timestamps explicitly rather than relying on the server’s default behavior, which can sometimes add overhead or mask genuine query performance issues.
Once you’ve optimized your queries and indexes, you’ll want to keep an eye on the slow query log. The next problem you’ll likely encounter is dealing with a very large slow query log file and needing to rotate or archive it efficiently.