MySQL

MySQL Slow Query Log Analysis: Identifying Queries Without Indexes

The slow query log is MySQL's built-in mechanism for recording queries that exceed a configurable time threshold — enabling and properly analyzing it is the systematic, data-driven way to find genuinely problematic queries across your application, rather than guessing which queries might be slow based on assumption alone.

The Problem

An application feels generally slow, or specific pages/endpoints take longer than expected, but without a systematic approach, it's hard to know which specific queries are actually responsible versus which are already performing fine.

Slow query log workflow Enable logging Collect over time Analyze & fix

Why It Matters

Without systematic logging, teams often optimize based on intuition or anecdotal reports ("this page feels slow"), which can lead to spending effort on queries that aren't actually significant contributors to overall load, while genuinely expensive queries running frequently in the background go unnoticed. The slow query log, combined with proper analysis tools, gives an objective, ranked view of which specific queries are actually consuming the most cumulative time across real production traffic.

The Fix

First, enable the slow query log and set an appropriate threshold — start with a relatively low threshold to catch a broader set of queries, then narrow based on what you find:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- log queries taking longer than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

Also enable logging of queries that don't use an index at all, even if they complete quickly, since these represent scalability risks that will become genuinely slow as data grows, even if they're not currently slow enough to exceed your time threshold:

SET GLOBAL log_queries_not_using_indexes = 'ON';

Let the log accumulate over a representative period of real traffic — a few hours to a full day, ideally covering your typical peak usage pattern, since a short collection window might miss queries that only run during specific, less frequent operations.

Analyze the accumulated log using mysqldumpslow, which comes bundled with MySQL and provides a quick, ranked summary:

mysqldumpslow -s t -t 20 /var/log/mysql/slow-query.log

-s t sorts by total time consumed (cumulative across all executions of a similar query pattern), and -t 20 limits output to the top 20 — sorting by total time rather than average time per query is usually more actionable, since a query that's individually fast but runs extremely frequently can consume more cumulative server time than a query that's slow but rarely executed.

For more detailed, sophisticated analysis, pt-query-digest from Percona Toolkit provides considerably richer analysis than the built-in tool, including query normalization, detailed timing distribution, and more actionable groupings:

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

For each identified problematic query pattern, use EXPLAIN to understand exactly why it's slow, which typically points directly at the fix:

EXPLAIN SELECT * FROM orders WHERE customer_email = 'user@example.com';

A type value of ALL in the output confirms a full table scan, and the specific columns in the WHERE/JOIN/ORDER BY clauses without corresponding indexes are the direct, actionable next step — add appropriate indexes for the columns that show up repeatedly across your top slow queries.

Still Not Working?

If the slow query log reveals many different query patterns rather than a few clear, dominant offenders, prioritize based on total cumulative time first (frequency × individual duration) rather than individual query duration alone, since fixing the single highest-total-time query pattern often has more real-world impact than fixing several individually-slower but much less frequently executed queries. If the slow query log itself is adding measurable overhead to a very high-traffic production server (logging every query has some cost), consider running it for a limited, deliberately scheduled analysis window rather than leaving it permanently enabled at a very low threshold on your busiest production systems.

You might also like