MySQL slow query log, explained

What gets written

A statement is written when all of these hold:

1. It is not an administrative statement (ALTER TABLE, ANALYZE TABLE, CREATE INDEX, OPTIMIZE TABLE…), or log_slow_admin_statements is enabled.
2. It took at least long_query_time seconds, or log_queries_not_using_indexes is enabled and it used no index for row lookups.
3. It examined at least min_examined_row_limit rows.
4. It was not suppressed by log_throttle_queries_not_using_indexes.

long_query_time defaults to 10 seconds, can be as low as 0, and accepts microsecond resolution. The log is off by default. A minimal configuration:

[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 1
log_output          = FILE

Details that matter when you read the file:

• The time to acquire the initial locks is not counted as execution time.
• A statement is written after it finished and released its locks, so the order in the file can differ from the order of execution.
• Statements that fail to parse (syntax errors) are not written.
• Passwords in statements are rewritten so they do not appear in plain text.
• log_timestamps sets the time zone of the timestamps in the file.

One entry, line by line

# Time: 2026-10-18T10:41:02.551000Z
# User@Host: report[report] @  [10.0.3.5]  Id:  5102
# Query_time: 12.880415  Lock_time: 0.412003 Rows_sent: 1  Rows_examined: 9120400
SET timestamp=1792320062;
SELECT COUNT(*) FROM order_items WHERE created_at >= '2026-10-01';

The entry above read 9.1 million rows to return one, held a lock for 0.4 s and ran for 12.9 s. That ratio is the first thing to look at.

Extra fields with log_slow_extra

With log_slow_extra enabled, the # Query_time line gains per-statement counters: Thread_id, Errno, Killed, Bytes_received, Bytes_sent, the handler reads (Read_first, Read_last, Read_key, Read_next, Read_prev, Read_rnd, Read_rnd_next), sort counters (Sort_merge_passes, Sort_range_count, Sort_rows, Sort_scan_count), Created_tmp_disk_tables, Created_tmp_tables, Start and End. A file can mix lines with and without them; parsers tell them apart by field count. The one-liners below only use the first four fields, so they work either way.

How to read it

Grouping by statement shape and adding up Query_time is what slow log summarizers do; MySQL ships mysqldumpslow for exactly this. A query that takes 0.8 s but runs 50,000 times a day matters more than one 30-second report.

Shell one-liners for the slow query log

The # Query_time line splits into fields as $3 = Query_time, $5 = Lock_time, $7 = Rows_sent, $9 = Rows_examined. All commands were run against a sample file with several entries.

• How many statements are in the file?

  grep -c '^# Query_time' slow.log

  Copy

• How many ran longer than one second?

  awk '/^# Query_time/ && $3 > 1' slow.log | wc -l

  Copy

• Statement count and total execution time.

  awk '/^# Query_time/ {s += $3; n++} END {printf "%d statements, %.1f s total\n", n, s}' slow.log

  Copy

• Slowest statements with the first line of their SQL.

  awk '/^# Query_time/ {qt = $3; next} /^(#|SET timestamp|use )/ {next} qt != "" {print qt, $0; qt = ""}' slow.log | sort -rn | head

  Copy

• Rows examined per row sent, worst first.

  awk '/^# Query_time/ && $7 > 0 {printf "%d %s/%s\n", $9/$7, $9, $7}' slow.log | sort -rn | head

  Copy

• Statements with more than 0.1 s of lock time.

  awk '/^# Query_time/ && $5 > 0.1 {print $5, $3}' slow.log | sort -rn

  Copy

• Which accounts produce slow statements.

  awk '/^# User@Host/ {print $3}' slow.log | sort | uniq -c | sort -rn

  Copy

• Which client addresses produce slow statements.

  grep '^# User@Host' slow.log | grep -oE '\[[0-9.]+\]' | sort | uniq -c | sort -rn

  Copy

• Group statements by the shape of their first line: literals replaced by ? and N.

  awk '/^SET timestamp/ {getline; print}' slow.log | sed -E "s/'[^']*'/?/g; s/[0-9]+/N/g" | sort | uniq -c | sort -rn | head

  Copy

• Entries per hour (UTC unless log_timestamps says otherwise).

  grep '^# Time:' slow.log | cut -c9-21 | uniq -c

  Copy