Master MySQL Slow Query Log: Full Hands‑On Workflow to Capture, Analyze, and Fix Slow SQL

What is the Slow Query Log?

The slow query log is a MySQL feature that automatically records any SQL statement whose execution time exceeds a configured threshold. For each logged statement MySQL stores the query text, execution time, rows examined, rows sent, and lock time.

Why Enable the Slow Query Log?

In many small‑to‑medium production environments a typical incident follows the pattern: sudden business slowdown → CPU saturation → request timeouts → unknown offending SQL → blind index changes or restarts. The root cause is the absence of a mechanism to collect slow SQL.

Core benefits of the log are:

Automatic capture of hidden slow SQL that appears only under load.

Precise identification of full‑table scans, deep pagination, missing indexes, index failures, and large transactions.

Batch discovery of long‑standing, unoptimised “garbage” SQL across the code base.

Provides the only data‑driven evidence for SQL tuning, eliminating guesswork.

Common interview topic and essential skill for operations, backend architecture, and DBA work.

Where to Use It

All MySQL instances in production (baseline for performance troubleshooting).

Load‑testing environments for bottleneck analysis.

Investigation of high CPU, high I/O, or intermittent request latency.

Observing performance of newly deployed services.

It is generally unnecessary in local development environments with negligible data volume or for very low‑frequency administrative SQL that does not require optimisation.

How to Activate the Slow Query Log

Step 1 – Check Current Status

-- Check whether slow query logging is enabled
SHOW VARIABLES LIKE 'slow_query_log';

-- View the timeout threshold (default 10 seconds)
SHOW VARIABLES LIKE 'long_query_time';

-- Locate the log file path
SHOW VARIABLES LIKE 'slow_query_log_file';

-- Verify whether queries that do not use indexes are logged
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

Step 2 – Temporary Enable (effective immediately, lost after restart)

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;   -- production standard of 1 second
SET GLOBAL log_queries_not_using_indexes = ON;

Note: Global changes do not affect the current session; reconnect to see the effect.

Step 3 – Permanent Configuration (production standard)

Edit my.cnf or my.ini and restart MySQL:

[mysqld]
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1
slow_query_log_file = /usr/local/mysql/log/slow.log

Core Parameter Deep Dive

long_query_time

The default value is 10 seconds. Production environments should set it to 1 second because most business‑level timeouts occur around 1 s; the default would miss roughly 99 % of problematic queries.

log_queries_not_using_indexes

When enabled, any query that does not use an index is logged regardless of execution time, capturing full‑table scans, index failures, and implicit conversions.

log_slow_admin_statements

Records slow DDL statements such as ALTER and ADD INDEX, aiding troubleshooting of large‑table schema changes.

Practical Exercise: Simulate a Slow Query, View the Log, Analyse

Step 1 – Create a Slow Query

-- Sleep for 2 seconds to simulate a timeout
SELECT SLEEP(2);

Step 2 – Key Log Fields to Examine

Query_time : execution duration (primary metric).

Lock_time : time spent waiting for locks.

Rows_examined : number of rows scanned (major performance killer).

Rows_sent : number of rows returned.

Rule of thumb: if Rows_examined is far larger than Rows_sent, the query is a prime candidate for optimisation.

Online Analysis Tool: mysqldumpslow

The raw log is noisy; mysqldumpslow aggregates entries and extracts the top slow statements.

Common Commands (copy‑paste ready)

# Top 10 longest‑running queries
mysqldumpslow -s t -t 10 slow.log

# Top 10 queries with the most rows examined
mysqldumpslow -s r -t 10 slow.log

# Filter queries longer than 1 second
mysqldumpslow -s t -t 10 -g "Query_time>1" slow.log

Parameter meanings: -s t: sort by query time. -s r: sort by rows examined. -t 10: show only the top 10 entries.

Enterprise‑Grade Slow‑SQL Optimisation Pipeline

Extract top‑time‑consuming SQL from the slow log.

Inspect Rows_examined to identify full‑table or large‑range scans.

Run EXPLAIN on the candidate queries to locate index failures, back‑table reads, or sorting issues.

Refine indexes, rewrite SQL, and improve pagination logic.

Retest execution time and verify that the log no longer records the query.

Continuously monitor database load during peak periods.

Frequent Pitfalls

Pitfall 1: Leaving the default 10‑second threshold, which misses the majority of business‑critical slow queries (most fall in the 1‑5 s range).

Pitfall 2: Focusing only on execution time and ignoring rows scanned; a short‑running query that scans millions of rows can still exhaust CPU.

Pitfall 3: Keeping the slow log enabled indefinitely without rotation, leading to disk‑space exhaustion.

Pitfall 4: Disabling log_queries_not_using_indexes, thereby overlooking frequent full‑table scans.

Pitfall 5: Ignoring slow DDL statements, which can cause massive pauses during large‑table schema changes.

Key Takeaways

The slow query log is the only official MySQL tool for post‑mortem performance investigation; it must be enabled in production.

Set long_query_time=1 and enable log_queries_not_using_indexes to capture all relevant slow SQL.

Prioritise queries where execution time is high and Rows_examined vastly exceeds Rows_sent.

Use mysqldumpslow to aggregate top slow queries and avoid manual searching.

Follow the closed‑loop process: capture → EXPLAIN → index/SQL rewrite → retest → verify.