How to find slow queries in MySQL

Three tools, three questions

• What is running now? The process list.
• What has been slow over time? Performance Schema, which groups queries by shape (a “digest”).
• Why is this query slow? EXPLAIN ANALYZE.

The examples are for MySQL 8.0 and later, where Performance Schema and the sys schema are on by default.

1. What is running now

SELECT id, user, host, db, command, time AS seconds, state, LEFT(info, 120) AS query
FROM information_schema.processlist
WHERE command <> 'Sleep'
ORDER BY time DESC;

SHOW FULL PROCESSLIST; gives the same view. A long time with a state such as Sending data or Creating sort index is doing work; Waiting for table metadata lock is waiting behind something else.

2. What has been slow over time

Performance Schema keeps totals per query shape since the server started or the table was last truncated. Timer columns are in picoseconds; dividing by 10¹² gives seconds.

SELECT schema_name,
       LEFT(digest_text, 120)                 AS query,
       count_star                             AS calls,
       ROUND(sum_timer_wait / 1e12, 1)        AS total_s,
       ROUND(avg_timer_wait / 1e9, 1)         AS avg_ms,
       sum_rows_examined,
       sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 20;

Compare sum_rows_examined with sum_rows_sent. Examining a million rows to return ten is the classic sign of a missing index.

3. The sys schema shortcut

The sys schema wraps Performance Schema in readable views with formatted times.

-- statements by total latency, already formatted
SELECT * FROM sys.statement_analysis LIMIT 20;

-- statements that did full table scans
SELECT * FROM sys.statements_with_full_table_scans LIMIT 20;

-- indexes never used since startup (candidates to review, not to drop blindly)
SELECT * FROM sys.schema_unused_indexes;

4. Waiting on locks

If queries are slow because they wait, find out for whom.

SELECT wait_age,
       waiting_pid,
       LEFT(waiting_query, 80)  AS waiting_query,
       blocking_pid,
       LEFT(blocking_query, 80) AS blocking_query
FROM sys.innodb_lock_waits
ORDER BY wait_age DESC;

A blocking_query of NULL usually means the blocker is idle with an open transaction. KILL <blocking_pid>; ends it and rolls back its work, so confirm the owner first.

5. Why a query is slow

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'PENDING'
  AND created_at > NOW() - INTERVAL 1 HOUR;

• Table scan on a large table where you expected an index lookup.
• Estimated rows far from actual rows: run ANALYZE TABLE orders; to refresh statistics.
• Using filesort or temporary table on large inputs.

6. The slow query log

The slow log records every statement above a time threshold, which is useful when you want each slow call, not totals.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- seconds
SHOW VARIABLES LIKE 'slow_query_log_file';

Summarise the file with mysqldumpslow or pt-query-digest. Settings made with SET GLOBAL are lost on restart unless you also add them to the configuration file or use SET PERSIST.

The same steps in Quorum

Quorum reads Performance Schema for you, groups queries by fingerprint, ranks them by observed impact and shows waits beside them, in the same window as your editor.

• Top queries by fingerprint with Performance Schema as the source.
• Plan history and regressions, with estimated against actual rows explained in plain English.
• Index advice weighed honestly: ranked by measured impact, with storage and write overhead shown.