MySQL slow_log: Find the Most Frequent and Slowest Query Digests

When a MySQL application becomes slow, looking at the longest individual query is often not enough.

A query taking 15 seconds once may be less important than another query taking 800 ms but executing 50,000 times.

During database performance troubleshooting, we therefore usually want to answer three different questions:

1. Which query patterns occur most frequently?
2. Which query patterns consume the most total database time?
3. Which query patterns have the worst individual execution time?

If MySQL writes its slow query log to TABLE, all three can be answered directly from mysql.slow_log.

The problem with looking at raw queries

Consider queries such as:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT *
FROM orders
WHERE customer_id = 12891;

SELECT *
FROM orders
WHERE customer_id = 73102;

SELECT *
FROM orders
WHERE customer_id = 44183;

These are three different SQL strings, but operationally they are the same query:

1
2
3

SELECT *
FROM orders
WHERE customer_id = ?;

Grouping by the original sql_text would therefore hide the actual frequency of the query pattern.

What we need is a query fingerprint, or digest-like normalized representation.

First check that the slow log is available

For this approach, MySQL needs to write slow queries into the mysql.slow_log table.

1
2
3
4
5
6

SHOW VARIABLES
WHERE Variable_name IN (
    'slow_query_log',
    'log_output',
    'long_query_time'
);

A typical configuration looks like:

1
2
3

slow_query_log = ON
log_output      = TABLE
long_query_time = 1

The appropriate long_query_time depends heavily on the workload. Setting it too high hides important high-frequency queries; setting it too low can generate a very large slow log.

Why not simply use STATEMENT_DIGEST()?

MySQL 8 provides:

1
2

STATEMENT_DIGEST()
STATEMENT_DIGEST_TEXT()

For example:

1
2
3
4

SELECT
    STATEMENT_DIGEST_TEXT(
        'SELECT * FROM orders WHERE customer_id = 123'
    );

returns a normalized representation similar to:

1

SELECT * FROM `orders` WHERE `customer_id` = ?

This looks ideal.

There is one practical problem when applying it to historical mysql.slow_log data: these functions invoke the MySQL SQL parser.

If even one stored statement cannot be parsed, the analysis itself can fail with an error such as:

1

ERROR 3677 (HY000): Could not parse argument to digest function.

That is particularly inconvenient when analysing thousands or millions of historical slow-log entries.

For exploratory troubleshooting, a simple SQL fingerprint can therefore be more robust.

Build a safe query fingerprint

The following example:

• converts sql_text to text;
• removes string literals;
• replaces numeric literals;
• collapses whitespace;
• generates a short SHA-256 fingerprint.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

WITH normalized AS (
    SELECT
        start_time,
        TIME_TO_SEC(query_time) AS query_sec,
        rows_examined,

        REGEXP_REPLACE(
            REGEXP_REPLACE(
                REGEXP_REPLACE(
                    TRIM(CONVERT(sql_text USING utf8mb4)),
                    '''([^'']|'''')*''',
                    '?'
                ),
                '[[:digit:]]+([.][[:digit:]]+)?',
                '?'
            ),
            '[[:space:]]+',
            ' '
        ) AS normalized_query

    FROM mysql.slow_log

    WHERE start_time >= NOW() - INTERVAL 24 HOUR
)
SELECT
    LEFT(SHA2(normalized_query, 256), 16) AS digest,
    normalized_query,
    COUNT(*) AS executions,
    ROUND(AVG(query_sec), 3) AS avg_sec,
    ROUND(MAX(query_sec), 3) AS max_sec,
    ROUND(SUM(query_sec), 3) AS total_sec,
    SUM(rows_examined) AS rows_examined,
    MAX(start_time) AS last_seen

FROM normalized

GROUP BY normalized_query

ORDER BY executions DESC, total_sec DESC
LIMIT 20;

The result immediately converts a large slow log into something much more useful:

1
2
3
4
5

digest            executions  avg_sec  max_sec  total_sec
----------------  ----------  -------  -------  ---------
a9d41765d18bf731       12843    0.821    7.192   10543.2
d921ef580c21899a        3812    1.972   11.402    7516.4
ef112046e59898a4          41   18.241   29.102     747.9

Now we can distinguish very different performance problems.

Find the most frequent query patterns

For queries that generate load through repetition:

1
2

ORDER BY executions DESC, total_sec DESC
LIMIT 20;

This is particularly useful for detecting application behavior such as:

• polling;
• N+1 queries;
• excessive API calls;
• repeated session lookups;
• inefficient background workers;
• missing application-side caching.

A query does not need to be exceptionally slow individually to become expensive.

For example:

1

0.4 seconds × 100,000 executions = 11.1 database-hours

That query may deserve more attention than a single 20-second report.

Find queries generating the most total load

In many investigations, this is the most useful ranking:

1
2

ORDER BY total_sec DESC
LIMIT 20;

total_sec answers a different question:

How much database execution time did this query family consume during the selected interval?

This often identifies the best optimization targets because it combines frequency and latency.

A query with:

1
2
3

executions = 20,000
avg_sec    = 0.7
total_sec  = 14,000

is usually much more interesting than:

1
2
3

executions = 3
avg_sec    = 20
total_sec  = 60

The second query looks much worse when examining individual slow-log records, but the first one puts substantially more load on the database.

Find query patterns with the worst latency

To investigate extreme response times, use:

1
2

ORDER BY max_sec DESC
LIMIT 20;

Alternatively, use average execution time:

1
2

ORDER BY avg_sec DESC
LIMIT 20;

These rankings are useful for finding queries affected by:

• poor execution plans;
• large scans;
• missing indexes;
• temporary tables;
• sorting;
• lock contention;
• highly variable parameter selectivity.

However, always look at executions and total_sec as well. A very slow query that runs once per week may not be the highest-priority performance problem.

Exclude a maintenance window

Slow logs frequently contain backups, reports, batch processing, maintenance jobs, or other workloads that should not affect normal application analysis.

For example, to ignore queries between midnight and 04:00:

1
2

WHERE start_time >= NOW() - INTERVAL 7 DAY
  AND TIME(start_time) NOT BETWEEN '00:00:00' AND '03:59:59'

This small filtering step can completely change the ranking on systems with heavy nightly workloads.

Inspect actual examples after finding a digest

Normalization is useful for ranking, but optimization still requires the original SQL.

After identifying an interesting query family, retrieve recent examples from mysql.slow_log and inspect:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

SELECT
    start_time,
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    db,
    CONVERT(sql_text USING utf8mb4) AS query_text

FROM mysql.slow_log

WHERE start_time >= NOW() - INTERVAL 24 HOUR

ORDER BY query_time DESC
LIMIT 50;

From there, the normal investigation continues with tools such as:

1

EXPLAIN

or, where safe and appropriate:

1

EXPLAIN ANALYZE

The slow log tells us which queries deserve attention. The execution plan tells us why they are expensive.

Native MySQL digests are still useful

If the SQL stored in the log is known to be parseable, native MySQL digest functions provide more accurate SQL-aware normalization:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
    STATEMENT_DIGEST(
        CONVERT(sql_text USING utf8mb4)
    ) AS digest,

    STATEMENT_DIGEST_TEXT(
        CONVERT(sql_text USING utf8mb4)
    ) AS digest_text,

    COUNT(*) AS executions

FROM mysql.slow_log

GROUP BY
    digest,
    digest_text

ORDER BY executions DESC
LIMIT 20;

This should normally be preferred when it works reliably.

The regular-expression fingerprint described earlier is not intended to reproduce MySQL’s parser exactly. Its purpose is different: provide a robust way to cluster historical slow-log records when parser-based digest calculation is not reliable for every stored statement.

Slowest does not necessarily mean most important

A useful performance review normally keeps at least three rankings:

In practice, total execution time is often the best first optimization queue, followed by frequency and then individual outliers.

This prevents a common troubleshooting mistake: spending hours optimizing the most visually impressive 30-second query while a much smaller query quietly consumes orders of magnitude more database capacity.

From one-off query to monitoring

Once this analysis becomes useful during an incident, it is usually worth turning it into a small Grafana dashboard.

A useful layout is:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

Slow queries over time
        |
        +-- Top query load
        |      ORDER BY total_sec
        |
        +-- Most frequent
        |      ORDER BY executions
        |
        +-- Slowest
               ORDER BY query_time

This gives engineers a fast path from:

1

"The database is slow"

to:

1

"These three query families produced most of the database load during the incident."

That is a much better starting point for database optimization.