MySQL Slow Query Log: Enabling, Analyzing with pt-query-digest, and Fixing the Top Patterns

At 2:47 AM, a SaaS platform's on-call engineer got paged: checkout latency had climbed from 180ms to 11 seconds. The application servers were healthy, RDS CPU was at 4%, and no deploys had gone out in six hours. The culprit turned out to be a single SELECT against an orders table that had quietly crossed 40 million rows overnight — a query that worked fine at 5 million rows but had been doing a full table scan the entire time. It had been running 900 times per minute for three months, burning CPU and I/O on every request, and nobody knew because it was never logged.

The MySQL slow query log would have caught it on day one. This guide covers how to enable it correctly, parse its output with pt-query-digest, interpret EXPLAIN plans, and fix the patterns that kill production performance — full table scans, missing indexes, N+1 loops, and runaway filesorts.

Background: Why the Slow Query Log Exists

MySQL's slow query log is a diagnostic facility that records queries exceeding a configurable execution time threshold. It has existed since MySQL 3.23 and remains the most direct instrument for identifying which SQL statements are harming production throughput. Unlike application-layer monitoring tools, which show you symptoms (high latency, error rates), the slow query log shows you the exact SQL, execution time, rows examined, and lock wait time for every offending query.

The log operates at near-zero overhead when the threshold is set appropriately. A long_query_time of 1 second on a busy OLTP server typically captures fewer than 0.1% of all queries, adding microseconds of logging I/O per captured statement. The cost of not having it enabled is vastly higher than the cost of enabling it.

Two additional variables extend its usefulness beyond time-based capture. log_queries_not_using_indexes captures any query that performs a full or range scan without using an index — regardless of how fast it ran on a small table today. min_examined_row_limit filters out trivial full scans (a SELECT * FROM config on a 10-row settings table) so the log stays focused on real problems.

Enabling the Slow Query Log

Runtime Configuration (No Restart Required)

All slow query log variables are dynamic in MySQL 5.6+ and can be changed at runtime with SET GLOBAL. This is the safest way to enable it on a running production server:

-- Enable the slow query log
SET GLOBAL slow_query_log          = 'ON';
SET GLOBAL long_query_time         = 1;       -- seconds; use 0.1 for high-throughput OLTP
SET GLOBAL slow_query_log_file     = '/var/log/mysql/slow.log';

-- Capture queries missing indexes (even if fast today)
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Ignore trivial full scans on tiny tables
SET GLOBAL min_examined_row_limit  = 1000;

-- Capture slow administrative statements (ALTER, ANALYZE, OPTIMIZE)
SET GLOBAL log_slow_admin_statements = 'ON';

-- Verify
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Persistent Configuration (my.cnf / mysqld.cnf)

Add these to the [mysqld] section of your MySQL configuration file:

[mysqld]
slow_query_log                = 1
slow_query_log_file           = /var/log/mysql/slow.log
long_query_time               = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements     = 1
min_examined_row_limit        = 1000

Choosing long_query_time

The right threshold depends on your application's latency SLA. For a typical web application targeting p95 under 200ms, start at long_query_time=1 to capture the worst offenders first. Once those are fixed, lower it to 0.5 or 0.1 to catch the next tier. Do not start at 0 — on a busy server, logging every query will saturate disk I/O and create a log file measured in gigabytes per hour.

Analyzing with pt-query-digest

The raw slow query log is chronological. A query that ran 3 seconds appears once. A query that ran 0.2 seconds but executed 50,000 times per hour — totaling over 10,000 seconds of CPU time — is invisible in raw chronological output. pt-query-digest from Percona Toolkit solves this by fingerprinting and aggregating queries across the entire log, then ranking by total time.

Installation

# Debian / Ubuntu
apt-get install percona-toolkit

# RHEL / Amazon Linux
yum install percona-toolkit

# Direct download (single Perl script, no install needed)
curl -O https://www.percona.com/get/pt-query-digest
chmod +x pt-query-digest

Basic Analysis

# Analyze the entire slow query log, show top 20 queries by total time
pt-query-digest /var/log/mysql/slow.log

# Limit to queries from the last 24 hours
pt-query-digest --since 24h /var/log/mysql/slow.log

# Filter to a specific database
pt-query-digest --filter '$event->{db} eq "orders_db"' /var/log/mysql/slow.log

# Output to a file for offline review
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_query_report.txt

Reading the Output

The report has three sections. The header shows overall statistics: total queries analyzed, unique fingerprints found, and time range. The profile section ranks query fingerprints by total time — this is the right column to sort on. The per-query detail section shows the worst offender's full statistics:

# Rank Query ID           Response time  Calls   R/Call   Item
# ==== ================== ============== ======= ======== ====
#    1 0xA8C3A8F1B2D4E6C7 10842.3 38.2%   48291    0.2245 SELECT orders
#    2 0xB1D2E3F4A5C6D7E8  4201.1 14.8%   12004    0.3499 SELECT order_items

# Query 1: SELECT orders
# Databases: orders_db
# Rows examined: 40217843  ← full table scan every call
# Rows sent:     1
# Query:
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC LIMIT 1;

The critical numbers are Rows examined vs Rows sent. A ratio of 40 million examined to 1 sent means MySQL is reading the entire table to return a single row — the canonical signature of a missing index on the filter column.

MySQL 8.0 Alternative: performance_schema

If you cannot install Percona Toolkit, MySQL 8.0's Performance Schema provides the same digest-level aggregation live in SQL, without log files:

-- Top 10 queries by total execution time (digest-level, all-time since last truncation)
SELECT
    DIGEST_TEXT,
    COUNT_STAR                                          AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1e12, 2)                    AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 4)                    AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT,
    ROUND(SUM_ROWS_EXAMINED / COUNT_STAR)              AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'orders_db'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

This table is always populated (assuming Performance Schema is enabled, which is the default in MySQL 8.0). No log files to rotate, no pt-query-digest to run. The tradeoff is that it resets on restart and has a fixed digest table size (performance_schema_digests_size, default 10,000 rows).

Reading EXPLAIN Output

Once pt-query-digest or the Performance Schema identifies a problem query, EXPLAIN shows exactly how MySQL's optimizer plans to execute it. Prepend EXPLAIN (or EXPLAIN FORMAT=JSON for the full detail) to any SELECT statement:

EXPLAIN SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC LIMIT 1;

The type Column: The Most Important Signal

The type column tells you the join type — effectively, how MySQL is accessing the table. From worst to best:

Other Key Columns

Beyond type, watch these columns in every EXPLAIN output:

• key: Which index MySQL actually used. NULL means no index was used.
• rows: The optimizer's estimate of rows it will examine. Compare this to the actual rows examined in the slow log.
• Extra: Contains critical diagnostic strings. Using filesort means MySQL cannot satisfy the ORDER BY from an index and must sort in memory or on disk. Using temporary means a temporary table was created. Both are expensive.
• possible_keys: Indexes MySQL considered but may not have used. If this is NULL, no useful index exists.

-- EXPLAIN output for a query with no useful index
-- type: ALL, key: NULL, rows: 40217843, Extra: Using filesort
EXPLAIN SELECT * FROM orders
WHERE customer_email = 'user@example.com'
ORDER BY created_at DESC
LIMIT 1\G

-- After adding a composite index: (customer_email, created_at DESC)
-- type: ref, key: idx_email_created, rows: 3, Extra: (none)

Fixing Common Slow Query Patterns

Pattern 1: Full Table Scan on a Filtered Column

A query with WHERE column = value and type: ALL in EXPLAIN means the filter column has no index. The fix is straightforward:

-- Slow: 40M row scan for every checkout lookup
SELECT * FROM orders WHERE customer_email = 'user@example.com'
ORDER BY created_at DESC LIMIT 1;

-- Fix: composite index covering both the filter and the sort
CREATE INDEX idx_orders_email_created
    ON orders (customer_email, created_at DESC);

-- Result: type changes from ALL to ref, rows drops from 40M to ~3

Use a composite index when the query filters on one column and sorts or ranges on another. Column order matters: put the equality filter column first, the range or sort column second.

Pattern 2: Leading Wildcard LIKE

A LIKE '%keyword%' with a leading wildcard cannot use a B-tree index. MySQL must scan every row and apply the pattern. This appears as type: ALL even with an index on the column:

-- Cannot use index — leading wildcard forces full scan
SELECT * FROM products WHERE description LIKE '%wireless headphones%';

-- Option 1: Full-text index (for natural language search)
ALTER TABLE products ADD FULLTEXT INDEX ft_description (description);

SELECT * FROM products
WHERE MATCH(description) AGAINST ('wireless headphones' IN BOOLEAN MODE);

-- Option 2: Move search to Elasticsearch or MySQL 8.0 FULLTEXT with ngram parser
-- for partial-word matching requirements

Pattern 3: N+1 Query Loops

The N+1 pattern does not appear as a single slow query in the log — it appears as one medium-speed query executed thousands of times per minute. The pt-query-digest Calls column exposes it: a query with 50,000 calls per hour at 0.2ms average is spending 10,000 seconds of total time and will dominate the profile.

-- N+1 pattern: application fetches orders, then loops to fetch each customer
-- Appears as: SELECT * FROM customers WHERE id = ?  (called 50,000x/hour)
SELECT * FROM customers WHERE id = ?;

-- Fix: JOIN at the database layer
SELECT
    o.id           AS order_id,
    o.total,
    o.created_at,
    c.name         AS customer_name,
    c.email        AS customer_email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= NOW() - INTERVAL 24 HOUR;

Pattern 4: Filesort on Large Result Sets

When EXPLAIN shows Using filesort, MySQL cannot use an index to satisfy the ORDER BY. For small result sets this is fast; for large ones it spills to disk and causes latency spikes. The fix is a covering index that includes both the filter and the sort column in the right order:

-- Slow: Using filesort because index on status doesn't include created_at
SELECT id, total, created_at FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

-- Check current EXPLAIN
EXPLAIN SELECT id, total, created_at FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50\G
-- Extra: Using filesort

-- Fix: composite index matching the WHERE + ORDER BY
CREATE INDEX idx_orders_status_created
    ON orders (status, created_at DESC);

-- Bonus: make it covering by including the selected columns
CREATE INDEX idx_orders_status_created_covering
    ON orders (status, created_at DESC, id, total);

-- Now Extra: Using index (covering index — no table row access needed)

Pattern 5: SELECT * With Implicit Full Scan

SELECT * fetches every column, forcing MySQL to read the full row from the clustered index even when a covering index could have answered the query from index pages alone. Replace SELECT * with explicit column lists, and you give the optimizer the option to use a covering index:

-- Avoid: forces heap row access even if index exists on (status, created_at)
SELECT * FROM orders WHERE status = 'active' ORDER BY created_at DESC LIMIT 100;

-- Prefer: explicit columns enable covering index optimization
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

Key Takeaways

Working with JusDB on MySQL

JusDB manages MySQL for engineering teams who need production-grade query performance without the overhead of maintaining DBA expertise in-house. Our engineers handle slow query log analysis, EXPLAIN-driven index design, schema optimization, and the operational work of safely applying changes to large production tables — so your team ships features instead of diagnosing 2 AM latency spikes.

We run this exact diagnostic workflow — slow log enablement, pt-query-digest analysis, EXPLAIN review, and index recommendations — as part of every MySQL engagement. For teams on RDS, Aurora, or self-managed MySQL 8.0, we deliver a ranked list of query improvements with projected impact before touching a single index.

Explore JusDB MySQL Services →  |  Talk to a MySQL DBA

Related reading:

• MySQL EXPLAIN Deep Dive: Reading Query Plans and Fixing Optimizer Mistakes
• MySQL Indexes Explained: Complete Data Structure Guide for Query Performance
• MySQL Architecture Deep Dive: From Query Execution to Physical Storage