How to Triage MySQL Slow Queries with Native Tools Only

Out of the box, most MySQL servers are configured to hide their slow queries. The slow query log ships disabled, and even when someone flips it on, long_query_time defaults to 10 seconds. So the 800 ms query that fires 4,000 times an hour and quietly dominates your database load never appears in any log. The team concludes "the slow log is empty, the database is fine" while p95 latency says otherwise.

This guide is a complete MySQL slow query triage using only what ships with the server: the slow query log configured correctly, mysqldumpslow, the performance schema and sys schema, and EXPLAIN ANALYZE. No Percona Toolkit, no APM, nothing to install. Part one of this series covered the five ways queries go slow: missing indexes, full table scans, bad joins, on-disk temp tables and lock contention. This part is how you find your instances of each, rank them and prove the fix. Budget half a day for a first pass.

We'll go tool by tool, in the order you'd actually use them.

01Capture

Slow query log

A 1-second threshold plus unindexed queries, persisted in my.cnf.

02Aggregate

mysqldumpslow

Normalizes the log so identical statements group into one line.

03Rank

performance_schema

Every digest, ranked by total time, with sys schema views on top.

04Diagnose

EXPLAIN ANALYZE

The plan and the real per-step timings for each suspect.

05Prove

Before and after

Apply the index or rewrite, re-run the plan, compare actual times.

---

Tool 1: The slow query log, configured so it catches something

The slow query log is your capture layer, but the defaults make it useless. Fix three settings.

Runtime (no restart, takes effect for new connections):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_throttle_queries_not_using_indexes = 10;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

Then persist it in my.cnf so a restart doesn't silently revert you to blindness:

[mysqld]
slow_query_log                          = 1
long_query_time                         = 1
log_queries_not_using_indexes           = 1
log_throttle_queries_not_using_indexes  = 10
slow_query_log_file                     = /var/log/mysql/mysql-slow.log
log_output                              = FILE

On RDS, Aurora or Cloud SQL, set the same names in the parameter group or database flags. On MySQL 8.0 and later you can also use SET PERSIST, which writes the value to mysqld-auto.cnf and survives restarts.

Why these values. long_query_time = 1 is the working threshold for a web-backed OLTP database. For a latency-sensitive service, 0.5 is reasonable; the setting accepts fractions with microsecond resolution when logging to a file. log_queries_not_using_indexes is the setting most people skip and shouldn't. It logs queries that scan without an index regardless of how fast they ran, which is how you catch the full table scan that's fast today on 40,000 rows and a fire next year on 4 million. The throttle caps those entries at 10 per minute so one chatty unindexed query can't flood the log.

Two traps. First, long_query_time has a session copy: connections that were already open keep the old value, so pooled applications won't fully pick up the change until connections recycle. Second, log_output = TABLE routes entries to the mysql.slow_log table instead of a file. That's convenient on managed platforms where you can't reach the filesystem, but the table uses the CSV engine and is slower to write. On a self-managed server, prefer FILE.

Now let it collect during representative traffic: a business day, not a Sunday night.

Tool 2: mysqldumpslow, from 50,000 log lines to a top-ten list

A day of logging at a 1-second threshold on a busy server produces far more entries than anyone can read. mysqldumpslow ships with the server and normalizes queries (literals become N and 'S') so identical statements group together:

# Top 10 by total time: your priority list
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# Top 10 by execution count: the death-by-a-thousand-cuts queries
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

# Top 10 by average time: the individually painful ones
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

How to read it. Each entry shows Count, total and average Time, Lock time and Rows sent. Sort by total time first: a 200 ms query running 100,000 times a day costs more than a 30-second report running twice. The count-sorted view is where ORM N+1 patterns show up, the same one-row SELECT appearing tens of thousands of times. The Lock column is your early warning for part one's contention pattern: a high lock time next to a low average time means the query is waiting, not working.

mysqldumpslow's normalization is crude, and it only knows about queries that crossed your threshold. For the full statistical picture, move to the next layer.

Tool 3: performance_schema, the always-on statement census

The performance schema has been enabled by default since MySQL 5.6.6 and records every statement digest, not only the slow ones. Confirm it's on:

SHOW VARIABLES LIKE 'performance_schema';

Then pull the true top offenders, ranked by total time consumed (timers are in picoseconds, hence the division):

SELECT SCHEMA_NAME,
       LEFT(DIGEST_TEXT, 80)                    AS query,
       COUNT_STAR                               AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 2)          AS total_s,
       ROUND(AVG_TIMER_WAIT / 1e12, 4)          AS avg_s,
       SUM_ROWS_EXAMINED                        AS rows_examined,
       SUM_ROWS_SENT                            AS rows_sent,
       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 10;

How to read it. The most diagnostic number here is the ratio of rows_examined to rows_sent. A query that examines 2,000,000 rows to return 20 has a ratio of 100,000:1. That's a missing or wrong index. Healthy indexed lookups run in the low single digits. Also watch disk_tmp: any nonzero value means sorts or GROUP BYs spilling to disk, which you'll confirm with EXPLAIN below. SUM_LOCK_TIME is in the same table if you want to rank by waiting instead of working.

These counters accumulate since server start, which blurs old and new behavior. Before a focused triage window, reset the digest table and let it repopulate under current traffic:

TRUNCATE performance_schema.events_statements_summary_by_digest;

Tool 4: sys schema, the pre-written triage views

Raw performance_schema queries are verbose. The sys schema (installed by default since MySQL 5.7) wraps them in views that map almost one to one onto part one's list.

Full table scans:

SELECT query, db, exec_count, total_latency,
       no_index_used_count, rows_examined_avg
FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC
LIMIT 10;

Temp tables spilling to disk, and expensive sorts:

SELECT query, db, exec_count, disk_tmp_tables, tmp_tables_to_disk_pct
FROM sys.statements_with_temp_tables
ORDER BY disk_tmp_tables DESC
LIMIT 10;

SELECT query, db, exec_count, sort_merge_passes, rows_sorted
FROM sys.statements_with_sorting
ORDER BY sort_merge_passes DESC
LIMIT 10;

And the index side, because index optimization cuts both ways. Indexes you're missing slow reads. Indexes nobody uses slow every write and take buffer pool memory:

-- Indexes never touched since the last restart
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys');

-- Indexes made redundant by a wider index with the same leading columns
SELECT table_schema, table_name, redundant_index_name, dominant_index_name
FROM sys.schema_redundant_indexes;

How to read it. Every row in statements_with_full_table_scans with a high rows_examined_avg goes on your EXPLAIN list. For schema_unused_indexes, one big caveat: "unused" means since the last restart. An index that only serves the month-end billing job will look unused on day 12. Check uptime with SHOW GLOBAL STATUS LIKE 'Uptime' and only drop an index the data has had time to vouch against, ideally across a full business cycle. MySQL 8.0 gives you a reversible test first:

ALTER TABLE orders ALTER INDEX idx_customer INVISIBLE;
-- watch the digests for a week; if nothing regresses, drop it
-- if something does: ALTER TABLE orders ALTER INDEX idx_customer VISIBLE;

Redundant indexes are safer kills. If (customer_id) is fully covered by (customer_id, created_at), the narrower one is nearly pure write overhead.

Tool 5: EXPLAIN and EXPLAIN ANALYZE, the verdict on each suspect

You now have a ranked shortlist. For each query on it, ask the optimizer what it plans to do with EXPLAIN, then what actually happened with EXPLAIN ANALYZE, available since MySQL 8.0.18:

EXPLAIN SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.region = 'APAC' AND o.created_at >= '2026-06-01';

EXPLAIN ANALYZE SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.region = 'APAC' AND o.created_at >= '2026-06-01';

Reading EXPLAIN, in priority order:

• type: the access method. ALL is a full table scan; index is a full index scan (barely better). You want range, ref, eq_ref or const on any table with real row counts.
• key: the index actually chosen. NULL on a large table is your smoking gun. Check possible_keys to see whether an index existed but was skipped (often because a function wraps the column, or a type mismatch forces a conversion).
• rows: the optimizer's estimate of rows examined per step. Multiply down the join chain; that product is your work factor.
• Extra: Using filesort and Using temporary are the sort and temp-table problems from Tools 3 and 4, now pinned to a specific query. Using index (a covering index) is the good one.

EXPLAIN ANALYZE goes further: it executes the statement and reports actual times and row counts per plan node. You see which node ate 1.9 of the 2.0 seconds, and where the optimizer's row estimate was off by 1,000x, which usually means stale statistics (ANALYZE TABLE orders; refreshes them). Because it really runs the query, point it at a replica or staging copy when the statement is heavy.

---

One suspect, start to finish

Here's how the five tools chain on a single case. The numbers are illustrative.

mysqldumpslow -s t puts a reporting query at the top: Count: 1,140, Time=3.80s, Lock=0.00s. Lock time is zero, so contention is ruled out before you open anything else. Three readings remain:

• Missing index on the filter. Predicts a huge rows-examined ratio in the digest table.
• A sort or group spilling to disk. Predicts nonzero SUM_CREATED_TMP_DISK_TABLES and Using temporary in the plan.
• A bad join. Predicts one inner table with type: ALL and actual rows far above estimate.

The digest row answers two of them at once: rows examined per call is about 60,000 and rows sent about 50, a ratio near 1,200:1, and disk_tmp equals calls. Every execution spills. EXPLAIN ANALYZE then shows where:

-> Sort: revenue DESC  (actual time=3712..3712 rows=50 loops=1)
    -> Table scan on <temporary>  (actual time=3690..3701 rows=4100 loops=1)
        -> Aggregate using temporary table  (actual time=3690..3690 rows=4100 loops=1)
            -> Nested loop inner join  (actual time=0.9..3104 rows=61200 loops=1)
                -> Filter: (o.created_at >= '2026-06-01')  (actual time=0.8..2950 rows=61200 loops=1)
                    -> Table scan on o  (actual time=0.8..2210 rows=2400000 loops=1)
                -> Single-row index lookup on c using PRIMARY (id=o.customer_id)  (actual time=0.002..0.002 rows=1 loops=61200)

The join is fine (a primary-key lookup per row). The table scan on orders reads 2.4 million rows to keep 61,200, and the aggregate on top spills. One index on orders (created_at, customer_id) turns the scan into a range read, and with far fewer rows reaching the aggregate, the temp table fits in memory again. Re-run EXPLAIN ANALYZE, compare actual times, and the ticket carries its own proof.

That's the only way to close the loop that counts: apply the index (or rewrite the predicate), re-run the plan, compare actual times before and after. A missing-index fix on a mid-sized table routinely turns a multi-second scan into a millisecond lookup. A 100x to 1,000x range is normal, which is why index work dominates every serious triage.

Which tool answers which question

Where DIY triage runs out of road

Run this process. It works, and everything above is free. But be clear about what you've built.

01Time

A snapshot

Findings describe last Tuesday. The next deploy ships new query shapes.

02Cost

Engineer-hours

Half a day for the first pass, hours for every repeat, all competing with features.

03Gap

Unguarded window

A regression shipped the day after the review runs unexamined until the next one.

04Action

Detection only

Nothing here creates an index or refreshes statistics. Every finding becomes a ticket.

It's a snapshot. Your findings describe last Tuesday's workload. The next deploy ships new queries, an ORM upgrade changes generated SQL, tables grow past the size where yesterday's plan was fine. Full table scans are a recurring condition, not a one-time infestation.

It's engineer-hours, every cycle. Half a day for the first pass, hours for each repeat, and repeats compete with feature work. In practice the monthly triage quietly becomes "when someone complains."

The window between triages is unguarded. A regression introduced the day after your review runs unexamined until the next one. The slow log keeps growing; nobody is reading it.

Detection is not remediation. Nothing above creates an index, drops a redundant one or refreshes stale statistics. Every finding becomes a ticket, and on teams without a full-time DBA, those tickets lose to the roadmap more often than they win.

The native tools stop exactly where judgment and memory start: deciding which digest moved, why, and what to do about it, every week, without being asked.

What comes next

Every check in this guide can run continuously instead. CloudThinker connects to MySQL with a read-only user, watches the slow log and performance_schema around the clock, and turns findings into index proposals with the evidence attached. Changes go through Auto Mode, so you decide what runs on its own and what waits for approval. That's part three of this series.

Or point it at your own database today: try CloudThinker free with 100 premium credits and no card required.