MySQL slow query tuning — the EXPLAIN-driven workflow we use on customer databases

Every MySQL audit we run surfaces the same handful of query antipatterns. The fixes are nearly always small (an index, a rewrite, a LIMIT), but finding them at scale is the part that takes discipline. The slow query log is the right entry point — but only if you actually read it.

This is the EXPLAIN-driven workflow we use to triage slow queries on customer databases.

The slow query log + pt-query-digest loop

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_slow_admin_statements = 1
log_queries_not_using_indexes = 0    # noisy, off

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

pt-query-digest groups queries by fingerprint and ranks by total time. The top 10 fingerprints are almost always 80% of the database's pain.

The full write-up covers:

• The 0.5-second long_query_time threshold and when to drop it
• Reading EXPLAIN ANALYZE output (rows examined, key choice, filtered %)
• performance_schema.events_statements_summary_by_digest as a live alternative to the log
• The seven antipatterns we keep finding (leading wildcards, OR vs UNION, SELECT *, etc.)
• Index design — covering indexes, prefix length on VARCHAR, composite ordering
• When to fix the schema vs when to rewrite the query

Reach out if your slow log has grown to the point where nobody opens it any more.