MySQL - DBA Toolkit - Slow Query Analysis

Slow Query Analysis: Find the Queries Costing You the Most

The slow query log tells you about individual queries that exceeded a threshold. That is useful — but it is not the full picture.

A query that runs in 200ms and executes 50,000 times a day is costing you far more than a query that ran once and took 10 seconds. Cumulative time is the number that matters for capacity and performance work.

performance_schema keeps digest-level statistics that give you exactly this: total time, average time, execution count, and rows examined — aggregated across every execution pattern since the last reset or restart.

    Loading…

Reading the Results

Column	What to Check
`query_pattern`	Normalized query — literals replaced with `?`, so `WHERE id = 1` and `WHERE id = 2` are the same pattern
`executions`	How many times this pattern has run since last reset
`avg_sec`	Average wall-clock time per execution
`max_sec`	Worst single execution — useful for SLA violations
`total_sec`	Cumulative time — the true cost of this query pattern
`avg_rows_examined`	Rows read by the engine to return results — high values suggest missing indexes
`avg_rows_returned`	Rows actually sent back to the client
`pct_no_index`	Percentage of executions where no index was used — 100% on a large table is a problem

performance_schema stats accumulate from last MySQL restart. Truncate events_statements_summary_by_digest to reset if needed.

What to Watch For

Signal	What It Means
High `total_sec` with low `avg_sec`	High-frequency query — optimize it and the gain multiplies across all executions
`avg_rows_examined` >> `avg_rows_returned`	Full or near-full scan — missing or unused index
`pct_no_index = 100`	Every execution scanned without an index
`max_sec` >> `avg_sec`	Occasional outlier — may be lock wait or buffer miss during peak
Same pattern appearing multiple times	Digest grouping varies by schema — confirm it is not the same query under different databases

Gareth Winterman