The Slow Query Log

You cannot fix slow queries you cannot see. The slow query log records every statement that takes longer than a threshold, together with its execution time, the rows examined and the rows sent.

The settings

1

SELECT @@slow_query_log AS enabled, @@long_query_time AS threshold_seconds, @@log_output AS destination;

1
2
3
4
5
6

+---------+-------------------+-------------+
| enabled | threshold_seconds | destination |
+---------+-------------------+-------------+
|       0 |         10.000000 | FILE        |
+---------+-------------------+-------------+
1 row in set

Switch it on

For a running server (until the next restart):

1
2
3

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;              -- seconds. Try 0.5 or lower on busy systems
SET GLOBAL log_queries_not_using_indexes = 0; -- turn it on only for short investigations

To make it permanent, put it in the configuration file (my.cnf or my.ini):

1
2
3
4
5

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_slow_admin_statements = 1

Write the log to a table (easy to query)

With log_output = 'TABLE', slow statements go into the table mysql.slow_log, which you can query with SQL. Here the threshold is set to 0 for this one session, so even fast statements are logged:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

TRUNCATE TABLE mysql.slow_log;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 1;
SET SESSION long_query_time = 0;

SELECT SLEEP(0.2);
SELECT 'marker query' AS x;

SET GLOBAL slow_query_log = 0;
SET GLOBAL log_output = 'FILE';

SELECT query_time > 0.1 AS slow, rows_sent, rows_examined, CONVERT(sql_text USING utf8mb4) AS sql_text
FROM mysql.slow_log
WHERE CONVERT(sql_text USING utf8mb4) LIKE '%marker query%' OR CONVERT(sql_text USING utf8mb4) LIKE '%SLEEP(0.2)%'
ORDER BY sql_text
LIMIT 3;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

+------------+
| SLEEP(0.2) |
+------------+
|          0 |
+------------+
1 row in set

+--------------+
| x            |
+--------------+
| marker query |
+--------------+
1 row in set

+------+-----------+---------------+----------------------------+
| slow | rows_sent | rows_examined | sql_text                   |
+------+-----------+---------------+----------------------------+
|    0 |         1 |             1 | SELECT 'marker query' AS x |
|    1 |         1 |             1 | SELECT SLEEP(0.2)          |
+------+-----------+---------------+----------------------------+
2 rows in set

(Verified on a real server. The table log is convenient for exploring, but a file log is faster and is what most tools read. Keep log_output = FILE on production.)

What a file entry looks like

1
2
3
4
5

# Time: 2026-07-15T10:21:08.123456Z
# User@Host: app[app] @ 10.0.0.12 []  Id:  4521
# Query_time: 2.314159  Lock_time: 0.000112  Rows_sent: 20  Rows_examined: 3180442
SET timestamp=1784110868;
SELECT id, title FROM posts WHERE LOWER(title) LIKE '%mysql%' ORDER BY created_at DESC LIMIT 20;

The numbers to look at:

• Query_time: how long it took.
• Rows_examined vs Rows_sent: the query looked at 3.18 million rows to return 20. A huge ratio means missing indexes or a poor query shape.
• Lock_time: time waiting for locks.

Summarise the log

A log of thousands of entries is useless to read by eye. Group the entries by query shape (the same statement with different values):

mysqldumpslow (comes with MySQL):

1

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

• -s t: sort by total time (c count, a average time, r rows).
• -t 10: show the top 10.

pt-query-digest (Percona Toolkit) gives a much richer report, with the share of total time of each query shape, percentiles and an example of each:

1

pt-query-digest /var/log/mysql/mysql-slow.log | head -100

The first entries of its report are the queries that cost the most in total, which are the ones to fix first.

What threshold?

• Start at long_query_time = 1 second to find the worst queries.
• Then lower it to 0.5, 0.2 and further, as you fix things.
• For a short investigation, set it to 0 for a few minutes to capture all queries, then analyse (the file grows quickly).
• Use min_examined_row_limit to ignore tiny queries.

Queries without indexes

log_queries_not_using_indexes = 1 logs every statement that does a full scan, even if it is fast. On small tables, this creates a lot of noise. Turn it on for a short time on a representative environment, and combine it with min_examined_row_limit = 1000.

Cost of logging

Logging to a file is cheap. Logging every statement on a very busy server (threshold 0) writes a lot of data, so do it for minutes, not days. Rotate the log files (logrotate, or FLUSH SLOW LOGS).

Beyond the log

The slow log shows what was slow in the past. For a live view, use performance_schema and the sys schema (Performance Schema and sys), and SHOW FULL PROCESSLIST for what is running now.