How to find slow queries in MySQL

Quick answer: Start with performance_schema, not the slow query log. The slow log records individual executions that crossed a time threshold; events_statements_summary_by_digest aggregates every execution by query shape, so it shows total cost. A query taking 40ms a hundred thousand times never reaches the slow log and can cost more overall. Sort by SUM_TIMER_WAIT, and read SUM_ROWS_EXAMINED against SUM_ROWS_SENT to spot a missing index.

Two questions sound the same but have different answers:

• Which executions were slow? That is the slow query log.
• Which queries cost me the most? That is performance_schema.

Most of the time you want the second one, but many people reach for the first.

A query that takes 40ms and runs a hundred thousand times an hour will never appear in your slow query log, yet it costs far more than the 3-second report someone runs twice a day. Total execution time matters more than the duration of a single run.

Start with the digest table

events_statements_summary_by_digest holds one row per query shape, with the literal values normalized away, so every execution of the same query rolls up together.

Sort it by total time to see where the time goes:

SELECT
  LEFT(DIGEST_TEXT, 80)                      AS query,
  COUNT_STAR                                 AS execs,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2)   AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2)      AS avg_ms,
  SUM_ROWS_EXAMINED                          AS rows_examined,
  SUM_ROWS_SENT                              AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

The timer columns are in picoseconds, which is easy to miss the first time. Divide by 1,000,000,000,000 for seconds, or 1,000,000,000 for milliseconds. Get it wrong and your slowest query looks like it takes four hours.

The ratio that finds the problem

rows_examined against rows_sent is the most useful column pair here.

If a query examines 500,000 rows to return 10, it is doing a scan where it should be doing a lookup. That ratio can indicate a missing index, and it shows up long before the query is slow enough for anyone to complain:

SELECT
  LEFT(DIGEST_TEXT, 80)                                  AS query,
  COUNT_STAR                                             AS execs,
  SUM_ROWS_EXAMINED                                      AS examined,
  SUM_ROWS_SENT                                          AS sent,
  ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 1) AS examined_per_row
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
  AND SUM_ROWS_SENT > 0
ORDER BY examined_per_row DESC
LIMIT 20;

If the table is empty

performance_schema = ON does not turn on every consumer, and statements_digest is one you have to check:

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME IN ('statements_digest', 'events_statements_current');

Both should read YES. Enable them in your parameter group on RDS and Aurora, or in my.cnf for self-managed. A runtime UPDATE to setup_consumers does not survive a restart or a failover. Collection works until the next one, then stops.

The sys schema says the same thing in English

sys.statement_analysis is a view over the same digest table with the units already converted. It is easier to read and less prone to unit errors:

SELECT query, db, exec_count, total_latency, avg_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis
LIMIT 10;

Use sys when you are reading interactively. Use the raw table when you are building something, because the sys views do extra work per row and are slower on a busy server.

When the slow query log is the right tool

Use it when you need the actual parameter values from a specific bad execution, which the digest table has normalized away. Turn it on for a window, capture what you need, then turn it off:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'OFF';

Leave log_queries_not_using_indexes off. On a normal workload it writes a line for every small-table lookup where a scan is the right plan, and the file grows fast enough to matter. The result is noise, not a shortlist of problems.

Measuring one workload cleanly

To attribute cost to a specific job rather than to everything since the server started, reset the counters and re-read:

TRUNCATE performance_schema.events_statements_summary_by_digest;
-- run the workload you care about, then:
SELECT LEFT(DIGEST_TEXT, 80) AS query, COUNT_STAR AS execs,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

That truncate is destructive and has no undo. On a shared production server, someone else may be mid-measurement. Say so before you run it.

How DBGorilla helps

DBGorilla can help

DBGorilla connects with a read-only monitoring user and keeps the digest ranking described above, so it is already there when you need it. It collects performance_schema statement digests continuously: the query text, the per-digest statistics, and, through events_statements_current, the link from an active session back to the digest it is running. Your AI coding agent reads that ranking alongside your code, so it works from the queries costing you time rather than the one you happened to notice. It surfaces and explains, and it never truncates the summary table you are measuring against.

Start free

FAQ

• Should I use the slow query log or performance_schema?

  performance_schema first. The slow query log records individual executions that crossed a time threshold, so it shows you the worst single runs. events_statements_summary_by_digest aggregates every execution by query shape, so it shows you total cost. A query running 40ms a hundred thousand times never appears in the slow log and is often your real problem.

• Why is events_statements_summary_by_digest empty?

  Check whether the statements_digest consumer is off. It is on by default, but it can be switched off, and performance_schema = ON does not enable every consumer. Check with SELECT NAME, ENABLED FROM performance_schema.setup_consumers WHERE NAME = 'statements_digest'.

• What is a digest?

  A normalized fingerprint of a query with the literal values stripped out, so SELECT * FROM t WHERE id = 1 and SELECT * FROM t WHERE id = 2 collapse into one row. It is how MySQL groups executions of the same query shape.

• Does resetting the digest table lose data permanently?

  Yes. TRUNCATE performance_schema.events_statements_summary_by_digest clears the counters and there is no undo. That is fine and often useful (reset, run a workload, then read), but do not do it casually on a production server someone else is also measuring.

Related

• How to read a MySQL EXPLAIN plan
• Why is MySQL not using my index?
• How to fix “Too many connections” in MySQL
• Product: DBGorilla docs