+1 (541) 238-9429
QueryKings

Finding the queries that matter: statement digests before EXPLAIN

Most query tuning work we are asked to review starts in the wrong place. Someone noticed a slow page, pulled the query out of the application, ran EXPLAIN, added an index, and moved on. Sometimes that helps. Often the query was already responsible for two percent of database time, and the thing saturating the server was a 4 ms statement running nine thousand times a minute.

Reading a plan is the second step. The first is ranking the workload, and MySQL ships with everything you need to do it.

Total time, not worst case

The number worth optimising is total time: calls multiplied by average latency. A statement at 4 ms x 9,000 calls per minute costs 36 seconds of database time per minute. A report query at 12 seconds x 3 calls per minute costs 36 seconds too. They are equally expensive to the server, and they need completely different fixes — an index or a cache for the first, a schedule change or a replica for the second.

Sort by total latency first. Then look at the tail separately, because p99 is what your users feel even when the mean is fine. Two rankings, two lists, deliberately.

performance_schema statement digests

The digest tables normalise statements — literals replaced with ?, whitespace collapsed — so a million parameterised executions collapse into one row with aggregated counters. On 5.7 and later this is on by default and cheap enough to leave on in production.

SELECT DIGEST_TEXT,
       COUNT_STAR                         AS calls,
       ROUND(SUM_TIMER_WAIT/1e12, 1)      AS total_secs,
       ROUND(AVG_TIMER_WAIT/1e9, 2)       AS avg_ms,
       ROUND(MAX_TIMER_WAIT/1e9, 2)       AS max_ms,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       SUM_NO_INDEX_USED                  AS full_scans,
       SUM_CREATED_TMP_DISK_TABLES        AS disk_tmp
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Timer units are picoseconds — divide by 1e12 for seconds, 1e9 for milliseconds. Get that wrong once and you will spend an afternoon tuning something that takes a microsecond.

The sys schema wraps the same data with the units already handled:

SELECT * FROM sys.statement_analysis LIMIT 20;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;
SELECT * FROM sys.statements_with_temp_tables LIMIT 20;

One discipline makes this usable: the counters are cumulative since the last reset or restart, so a top-20 taken cold is a top-20 of everything since Tuesday, including the nightly batch. Take a delta instead. Snapshot the table into a scratch table, wait a representative interval — fifteen minutes of peak traffic is usually enough — snapshot again, and subtract. If you would rather reset, CALL sys.ps_truncate_all_tables(FALSE); clears the summaries, at the cost of any history other tooling was reading.

Also note the digest table has a fixed size, performance_schema_digests_size (200 by default on many builds, 5,000 on 8.0). When it fills, everything else aggregates into a single NULL digest row. If that row is large, you are missing statements, and raising the limit is worth the memory.

The three columns that point at the fix

Beyond time, three aggregated counters tell you what kind of problem you have before you open a plan:

  • SUM_ROWS_EXAMINED versus SUM_ROWS_SENT. A ratio near 1 means the access path is precise. A ratio of 10,000 examined per row returned means a scan or a filter applied after reading. This single ratio finds more missing indexes than any other signal.
  • SUM_NO_INDEX_USED. Counts executions that scanned a table with no index at all. Non-zero on a hot statement is a bug, not a tuning opportunity.
  • SUM_CREATED_TMP_DISK_TABLES and SUM_SORT_MERGE_PASSES. Sorts and grouping spilling to disk. Sometimes the fix is an index that provides the ordering; sometimes it is a sort_buffer_size or tmp_table_size that has been at its 2012 value since the server was built. Check the query first — raising buffers globally to paper over one bad GROUP BY costs memory on every connection.

When the slow query log is still the right tool

Digests aggregate. Sometimes you need the individual execution: the exact parameters, the client host, the timestamp that lines up with an incident.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.2;
SET GLOBAL log_slow_extra = ON;     -- 8.0.14+: rows examined, tmp tables, locking time

Run it for a bounded window, then analyse with pt-query-digest on the file. On RDS and Aurora these are parameter-group settings and the log is readable through the console or mysql.slow_log table; log_output = TABLE keeps it queryable but writes are more expensive than to a file.

Setting long_query_time = 0 captures everything and is genuinely useful for a two-minute sample on a low-traffic system. On a busy production server it produces gigabytes and adds I/O to the path you are trying to measure. Use a threshold, or use digests.

A fair rule: digests for what to fix, slow log for what happened at 14:05.

Turning a ranked list into work

The output of the triage should be a short table, not a feeling. For each of the top five statements by total time, record calls per minute, average and p99 latency, rows examined per row sent, and the share of total database time. Then decide which lever applies, in this order of preference:

  1. Do not run the query. The cheapest query is the one an application removed, cached, or stopped calling in a loop. N+1 patterns show up here as an absurd call count on a trivially fast statement — no index will fix ten thousand round trips.
  2. Give it a better access path. An index, a covering index, or a rewrite that lets an existing index be used. Verify with EXPLAIN ANALYZE on 8.0, which reports actual rows and timing rather than estimates.
  3. Move it. Reporting and analytics belong on a replica, not in the OLTP connection pool.
  4. Change the server setting — last, and only with the specific query as evidence.

Then re-measure with the same delta method. A tuning change that is not visible in the digest ranking did not matter, however satisfying the plan looks.

What this looks like on an engagement

A performance and health review begins here, read-only: a digest delta across a representative peak, the slow log for one incident window, and the schema of whatever the ranking names. The deliverable is the same ranked table with an expected impact and a cost against each line, so someone can pick the top three and skip the rest. If your own top-20 is in front of you and the next move is not obvious, send it over with the table definitions — the shape of the problem is usually legible in those two things alone.