Slow Query Log

Also Known As

TL;DR

Explanation

MySQL's slow query log records queries taking longer than long_query_time (set to 1–2 seconds in production, 0 in development to catch all queries). Enable with slow_query_log=ON and log_queries_not_using_indexes=ON. Tools like mysqldumpslow, pt-query-digest (Percona Toolkit), or MySQL Workbench aggregate and rank slow queries by frequency and total time. In PHP applications, ORMs can hide expensive queries — the slow query log surfaces them regardless of abstraction layer. Pair with EXPLAIN to understand why a slow query is slow and what index would fix it.

Common Misconception

Why It Matters

Common Mistakes

• Not enabling the slow query log in production — slow queries are invisible without it.
• Setting long_query_time too high (10s) — queries taking 500ms still cause user-visible latency.
• Not enabling log_queries_not_using_indexes — catches missing index issues the time threshold misses.
• Not rotating or monitoring the slow query log file — it grows indefinitely and can fill the disk.

Code Examples

# my.cnf — slow query log disabled:
[mysqld]
# slow_query_log = 1          ; Not set
# slow_query_time = 0.5       ; Not set — use 0.5s not 10s
# log_queries_not_using_indexes = 1 ; Not set

# Correct:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

; MySQL slow query log — log queries exceeding threshold
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          ; seconds — log queries over 1s
log_queries_not_using_indexes = 1  ; also log full-table scans

; Analyse with pt-query-digest (Percona Toolkit)
$ pt-query-digest /var/log/mysql/slow.log
; Shows: call count, total time, average, worst offenders

; PostgreSQL equivalent:
; log_min_duration_statement = 1000   ; milliseconds
; log_destination = 'csvlog'

References

• ↗ https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

Tags

Related Terms