Sign inBook a Demo

How To

How to Triage MySQL Slow Queries with Native Tools Only

A hands-on MySQL slow query triage using only native tools: configure the slow query log properly (long_query_time, log_queries_not_using_indexes), aggregate with mysqldumpslow, rank offenders through performance_schema digests and sys schema views like statements_with_full_table_scans, then confirm fixes with EXPLAIN and EXPLAIN ANALYZE. Exact SQL and config included, plus how to read rows_examined ratios and spot redundant indexes. Part two of our MySQL slow query series — and an honest look at where DIY triage stops scaling.

ST
·
mysqldatabaseperformanceslowqueryperformanceschemaindexoptimizationsqltuning
Cover Image for How to Triage MySQL Slow Queries with Native Tools Only

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. Teams conclude "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 MySQL performance schema and sys schema, and EXPLAIN ANALYZE. No Percona toolkit, no APM, nothing to install. In part one of this series we covered the five ways queries go slow — missing indexes, full table scans, bad joins, on-disk temp tables, and lock contention. This article 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: capture, aggregate, rank, diagnose.

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 immediately):

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';

And 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

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 and times 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 every row regardless of how fast they ran — which is exactly 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 a chatty unindexed query can't flood the log.

Two traps worth knowing. First, long_query_time has a session copy: connections that were already open keep their 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 — convenient for SQL access 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 — turn 50,000 log lines into a top-ten list

A day of logging at a 1-second threshold on a busy server produces far more entries than you 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 you more than a 30-second report running twice. The count-sorted view is where N+1 query patterns from ORMs show up: the same one-row SELECT appearing tens of thousands of times.

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 MySQL performance schema has been enabled by default since MySQL 5.6.6 and records every statement digest, not just 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 single 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 is a missing or wrong index, full stop. 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 is a Using temporary problem you'll confirm with EXPLAIN below.

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 problem 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 view — because MySQL index optimization cuts both ways. Indexes you're missing slow reads; indexes nobody uses slow every write and bloat 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 after watching it across a full business cycle. 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 — EXPLAIN — and then what actually happened — 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 here 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/temp-table problems from Tools 3 and 4, now pinned to a specific query. Using index (covering index) is the good one.

EXPLAIN ANALYZE goes further: it executes the statement and reports actual times and actual row counts per plan node, so you can see precisely 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 statistics are stale (ANALYZE TABLE orders; refreshes them). Because it really runs the query, point it at a replica or staging when the statement is heavy.

Then close the loop the only way that counts: add the index (or rewrite the predicate), re-run EXPLAIN ANALYZE, and 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–1,000x range is normal, which is why index work dominates every serious triage.

Where DIY triage runs out of road

Run this process — it works, and everything above is free. But be honest about what you've built, because it has four structural gaps:

It's a snapshot. Your findings describe last Tuesday's workload. The next deploy ships new queries, the 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.

What comes next

Every check in this guide can run continuously instead. Tony, CloudThinker's database agent, connects to MySQL read-only, watches the slow log and performance_schema around the clock, and turns findings into index recommendations with the evidence attached — no schema change ever happens without your approval. That's part three of this series.

Or point it at your own database today: Try CloudThinker free — 100 premium credits, no card required.