Finding the Queries That Matter: The MySQL Slow Query Log and pt-query-digest

Most MySQL tuning on legacy PHP applications goes wrong in the same way: someone picks the query that looks ugly, adds an index, and nothing measurable changes. The query that hurts is rarely the slowest one. It is the one that consumes the most total time. This tutorial is a loop for finding it, fixing it, and proving the fix worked. It applies to MySQL 5.7, 8.0, and 8.4, and to MariaDB with minor differences.

Step 1: capture a representative slow log

Turn the slow log on at runtime, no restart needed:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_queries_not_using_indexes = 'OFF';

A few notes on those settings:

• long_query_time = 0.2 catches anything over 200 ms. The default of 10 seconds hides nearly everything that matters on a web workload. On a very busy server start at 0.5 to keep the log small.
• Leave log_queries_not_using_indexes off at first. It floods the log with tiny lookups on small tables that are fine as they are.
• SET GLOBAL does not survive a restart. Once you are happy, put the same values in my.cnf under [mysqld].
• Make sure the log directory has logrotate coverage. A 0.2 s threshold on a busy site can write several GB a day.

Capture at least one full business cycle: a weekday peak, plus whatever batch jobs, reports, or imports run overnight. A log taken at 3 a.m. on a Sunday describes a different application.

Step 2: rank by total time, not by worst case

Install Percona Toolkit (apt install percona-toolkit or the equivalent from the Percona repository) and digest the log:

pt-query-digest /var/log/mysql/slow.log > digest.txt
head -60 digest.txt

The top of the report is a profile table ranking query fingerprints by response time share. Literals are stripped, so WHERE id = 7 and WHERE id = 9031 collapse into one entry. Read three columns:

• Response time %: the share of all slow-log time. If the top query holds 40%, that is your first target.
• Calls: a 150 ms query called 90,000 times a day costs more than a 30 s report run twice.
• R/Call: per-execution time. Useful for deciding whether the fix is an index (many calls, moderate time) or a rewrite (few calls, huge time).

In our experience on legacy applications, the top three fingerprints commonly account for more than half of logged time. Work only on those.

Step 3: read the detail for the top fingerprint

Below the profile, each fingerprint has a block with Rows examined and Rows sent. The ratio is the quickest diagnostic available. A query that examines 480,000 rows to return 12 is scanning. A query that examines 12 and returns 12 but is still slow has a different problem: locking, a cold buffer pool, or a slow disk.

Pull a real example with literals from the block, then run it:

EXPLAIN FORMAT=TREE SELECT ... ;   -- MySQL 8.0+
EXPLAIN SELECT ... ;               -- 5.7

What to look for:

• type: ALL on a large table, a full scan.
• Using filesort or Using temporary on a big result set.
• key: NULL when possible_keys lists candidates, which often means a type mismatch (a string column compared to an integer, or mismatched collations across a join).
• A very different rows estimate versus reality. Run ANALYZE TABLE on the table and compare again. On 8.0.18 and later, EXPLAIN ANALYZE gives actual row counts and timings.

Step 4: record a baseline before changing anything

Write down the numbers the change has to move: the fingerprint's average and p95 time from the digest, rows examined per call, and the overall response time share. Without that, you will be tuning by feel.

To get the p95 of a single fingerprint, filter the log and digest again:

pt-query-digest --filter '$event->{fingerprint} =~ m/from orders/i' slow.log

Step 5: make the smallest fix that works

In rough order of how often each one is the answer:

1. A composite index matching the filter and sort. Equality columns first, then range, then sort columns. WHERE customer_id = ? AND status = 'open' ORDER BY created_at DESC wants (customer_id, status, created_at).
2. Fixing a type or collation mismatch so an existing index can be used.
3. Removing SELECT * where a covering index could answer the query from the index alone.
4. Replacing a deep LIMIT 100000, 20 offset with keyset pagination (WHERE id < ? using the last id seen).
5. Caching the result in Redis or APCu when the query is correct but the data changes rarely.

Before adding an index, check what exists. Redundant indexes slow every write:

pt-duplicate-key-checker --host=localhost

On MySQL 8.0 you can add the index as invisible first (ALTER TABLE orders ADD INDEX idx_cust_status (customer_id, status, created_at) INVISIBLE;), confirm with EXPLAIN using SET SESSION optimizer_switch='use_invisible_indexes=on', then make it visible with ALTER TABLE ... ALTER INDEX idx_cust_status VISIBLE. If you ever need to roll it back, making it invisible is instant. On a large table, build the index with an online method; our post on changing a 200 GB table without downtime covers that.

Step 6: verify against the baseline

Rotate the slow log, wait for the same kind of traffic, and digest again:

mv /var/log/mysql/slow.log /var/log/mysql/slow.$(date +%F).log
mysql -e 'FLUSH SLOW LOGS;'

Compare to the baseline. The fingerprint should have dropped in response time share, and rows examined per call should be close to rows sent. If the number did not move, revert the change. An index that does not help is a cost on every write.

Then go back to Step 2. The second-ranked query is now the first.

Caveats

• The slow log records only statements above the threshold. A fast query called a million times will not appear, so also check performance_schema (events_statements_summary_by_digest) for call-heavy statements.
• On very write-heavy servers, logging at 0 s adds measurable overhead. Use a short window, not a permanent setting.
• Slow queries are sometimes a symptom of an application loop (the N+1 pattern). If one fingerprint has enormous call counts with tiny per-call time, the fix belongs in the PHP code, not in an index.
• If the buffer pool is much smaller than the working set, no index will save you. Check innodb_buffer_pool_size against the data size first.

When to call someone

This loop is usually a half-day of work for an engineer who has done it before, and many teams can run it themselves. If the digest shows no dominant query, or the fixes need schema changes on tables too big to alter in place, a performance and reliability engagement is a reasonable next step. Otherwise, run the loop, write down the numbers, and keep what moved them.