Slow Query Log
debt(d7/e3/b3/t5)
Closest to 'only careful code review or runtime testing' (d7), because slow queries are silent in production unless the slow query log is explicitly enabled. The common mistake is not enabling it at all, meaning degraded performance is invisible. Tools like mysql-slow-query-log, pt-query-digest, laravel-debugbar, and clockwork can surface the data once enabled, but they don't warn you that the log is disabled — a developer must proactively check. Not quite d9 because once enabled, the tooling is well-automated.
Closest to 'simple parameterised fix' (e3), because the quick_fix involves enabling slow_query_log with long_query_time=0.1, running EXPLAIN on flagged queries, and adding a covering index. This is more than a single-line patch (it touches MySQL config, query analysis, and index creation) but is contained within a small focused workflow rather than spanning multiple application files — so e3 rather than e5.
Closest to 'localised tax' (b3), because the slow query log is a MySQL-level configuration concern. Once properly configured it doesn't impose ongoing structural weight on the application codebase itself. The common mistakes (log not rotated, threshold too high) are operational rather than architectural burdens, and the impact is isolated to database operations rather than reaching across the whole system.
Closest to 'notable trap (a documented gotcha most devs eventually learn)' (t5), because the misconception is well-defined: developers assume the slow query log only captures queries exceeding long_query_time, but with log_queries_not_using_indexes=ON it also captures fast full-table-scan queries. Additionally, the common default of long_query_time=10s misses user-visible latency at 500ms. These are documented gotchas that experienced developers learn but that trip up beginners and intermediate users.
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'