Slowest MySQL Query Digests with Performance Schema
Groups similar queries to show total time, average time and no-index executions. Finds the costliest query patterns without enabling the slow log.
SELECT schema_name,
LEFT(digest_text, 150) AS query_pattern,
count_star AS exec_count,
ROUND(sum_timer_wait / 1e12, 2) AS total_sec,
ROUND(avg_timer_wait / 1e9, 2) AS avg_ms,
sum_rows_examined, sum_rows_sent,
sum_no_index_used AS no_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL
ORDER BY sum_timer_wait DESC
LIMIT 20;Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 5.7).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Other MYSQL Diagnostic Scripts
View All (100)Find Long-Running Queries in MySQL
Lists active queries running longer than 60 seconds from the processlist. Fast diagnosis for slowdowns and lock pile-ups.
Find MySQL InnoDB Lock Waits and the Blocking Query
Uses the sys schema to show waiting and blocking sessions with lock modes. Resolves who is holding up whom in a single query.
List the Largest Tables and Disk Usage in MySQL
Ranks tables by data, index and free space size. Core input for capacity planning and archiving decisions.
Detect Unused Indexes in MySQL
Uses sys.schema_unused_indexes to list indexes that were never used. Helps safely drop indexes that slow down writes.