MySQL Slow Query Log: How to Enable and Analyze It

Last verified on Nov 8, 2026.

The MySQL slow query log records statements that run longer than a threshold. It is the quickest way to find the queries worth indexing or rewriting, and it costs little when the threshold is sensible. This guide covers turning it on, deciding what gets logged, reading an entry, summarizing it, and keeping the file under control. The behavior described is from the MySQL 8.4 manual.

Enable the Slow Query Log

The slow query log is disabled by default. You can turn it on at runtime, which needs no restart, or in the option file.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SHOW VARIABLES LIKE 'slow_query%';

long_query_time is in seconds, defaults to 10, and accepts values from 0 to 31536000 with microsecond resolution. The value is measured in real time, not CPU time, so a query under the threshold on a quiet server can go over it on a loaded one. A new long_query_time applies to connections made after the change, so reconnect before testing. Set it to 0 to log every statement for a short capture window.

To keep the settings across restarts, put them in the [mysqld] section:

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

If you give no file name, the log is named host_name-slow.log and is created in the data directory, unless you use an absolute path. Runtime changes made with SET GLOBAL are lost when the server restarts, so mirror them in the option file.

log_output selects where the log goes. It defaults to FILE, and can also be TABLE, which writes to the mysql.slow_log table, or both, or NONE. This page assumes FILE.

What Gets Logged

The server checks four conditions, in this order, before it writes a statement:

1. It is not an administrative statement, unless log_slow_admin_statements is on.
2. It took at least long_query_time seconds, or log_queries_not_using_indexes is on and it used no index for row lookups.
3. It examined at least min_examined_row_limit rows. The default is 0.
4. It is not suppressed by log_throttle_queries_not_using_indexes.

If a query you expect is missing, work through that list. Remember also that the server writes a statement after it runs and after its locks are released, so file order can differ from execution order, and that the time spent acquiring the initial locks does not count as execution time. Statements that cannot be parsed, such as those with syntax errors, are not written at all.

Log Queries That Do Not Use an Index

log_queries_not_using_indexes adds statements that did no index lookup, however fast they were. The manual notes the server skips tables with fewer than two rows, because an index would not help. On a busy system this can flood the file, so set a throttle:

SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_throttle_queries_not_using_indexes = 20;   -- per minute
SET GLOBAL min_examined_row_limit = 1000;

A positive throttle value is a per-minute limit. The first such query opens a 60-second window in which up to that many are logged. Extras are suppressed, and when the window ends the server logs a summary with the count and the total time they took. A minimum-rows filter keeps tiny tables out of the log.

Administrative and Replica Statements

ALTER TABLE, CREATE INDEX, OPTIMIZE TABLE, and similar statements are logged only if log_slow_admin_statements is on. A replica does not write replicated queries to its slow log unless log_slow_replica_statements is on, and with row-based replication that setting has no effect.

Read an Entry

In file output, each logged statement is preceded by a line that starts with # and carries these fields:

An entry looks like this. The values here are illustrative, not captured output:

# Time: 2026-11-08T09:14:02.381204Z
# User@Host: app[app] @ 10.0.4.17 []  Id: 8812
# Query_time: 3.482113  Lock_time: 0.000094 Rows_sent: 1  Rows_examined: 4812033
SET timestamp=1794124442;
SELECT COUNT(*) FROM orders WHERE customer_email = 'a@example.com';

The ratio of Rows_examined to Rows_sent is the useful number. A query that returns 1 row after examining 4.8 million almost always needs an index on the filtered column. Each logged statement is preceded by a SET timestamp statement giving the time it began, and the server rewrites passwords in logged statements so they do not appear in plain text.

Turning on log_slow_extra adds more fields to file output, including Thread_id, Errno, Killed, Bytes_received, Bytes_sent, Created_tmp_disk_tables, Sort_merge_passes, Start, and End. A file can mix entries with and without the extra fields, and analyzers tell them apart by field count. TABLE output is unaffected.

Summarize With mysqldumpslow

A raw log has thousands of near-identical entries. mysqldumpslow groups statements that differ only in literals, replacing numbers with N and strings with 'S', and sorts the groups:

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log      # top 10 by total time
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log      # most frequent
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log      # most rows sent
mysqldumpslow -g 'orders' -s at /var/log/mysql/mysql-slow.log   # grep-style filter
mysqldumpslow -a -t 5 /var/log/mysql/mysql-slow.log         # keep literals as written

The sort types are t and at for query time and average query time, l and al for lock time, r and ar for rows sent, and c for count. The default is at. -r reverses the order. The manual's example output shows the shape of the result:

Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  insert into t2 select * from t1 limit N

Here Count is how many times the statement ran, Time is the average with the total in parentheses, and the user and host follow. Sort by total time to find what costs the server the most, and by average time to find the single worst statement. Once you have a candidate, run EXPLAIN on it and check which index, if any, it uses.

Keep the File Under Control

A throttle and a sensible long_query_time keep volume down, and rotation keeps the file from growing without limit. The server holds the file open, so rotate with logrotate and then run FLUSH SLOW LOGS, which closes and reopens slow query log files. It requires the RELOAD privilege and has no effect on log tables.

/var/log/mysql/mysql-slow.log {
    weekly
    rotate 4
    compress
    delaycompress
    missingok
    notifempty
    create 0640 mysql adm
    postrotate
        mysql -e 'FLUSH SLOW LOGS'
    endscript
}

See the logrotate guide for what each directive does and how to test it. A permanent long_query_time = 0 on a busy production server is a common way to fill a disk, so use it for a short capture and then raise it.

Sending Slow Query Logs to Sazabi

The slow log is a file, so any file-tailing forwarder can ship it. The OpenTelemetry Collector source accepts a filelog receiver, and the entries span several lines, so configure multiline handling so that the # header lines and the statement stay together as one record. Sazabi, an AI-native observability and security platform, then stores the records with the rest of your logs. The MySQL integration page covers connecting the database itself, so the Sazabi agent can query it when you investigate a slow query. If you run on PlanetScale, see the PlanetScale integration for the Insights data it exposes. For the PostgreSQL equivalent of everything above, see the Postgres logs guide. If you are choosing how much to log, log sampling explains the tradeoffs.