MySQL Slow Query Guide: 5 Root Causes and How to Catch Each One

It's Thursday, the app is slow again, and the Slack thread has reached the "is it the database?" stage. Nobody on the team is a DBA. Someone runs SHOW PROCESSLIST, sees forty connections stuck in Sending data, restarts a pod, and the problem goes away. Until next Thursday.

That loop is the default state of MySQL slow query management at teams without a dedicated database person. The queries were slow last week too. There was just nobody whose job it was to look. The useful part: when you finally do look, slow queries almost always trace back to the same five root causes. Each one has a distinct signature, a specific detection query, and a predictable cost in latency and instance size.

This guide covers all five: what each looks like in practice, one real detection command per cause, a worked example of telling them apart, and what each typically costs you.

Part two is a hands-on slow-query triage using only MySQL's native tools, and part three covers keeping the whole loop running automatically with an AI agent.

01Plan

Missing index

Rows examined grow with the table while rows returned stay flat.

02Plan

Full table scan

Every execution walks the table and churns the buffer pool for everyone.

03Plan

Bad join

An unindexed join column turns a nested loop into millions of row visits.

04Memory

Disk temp tables

GROUP BY and ORDER BY results outgrow memory and spill to disk.

05Concurrency

Lock contention

Fast queries wait behind a long transaction or a hot row.

First, turn on the meter

You can't triage what you don't record. MySQL ships with a slow query log, off by default, that captures every statement exceeding a time threshold:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- seconds; the default is 10, far too high

On RDS or Cloud SQL you set the same variables through a parameter group or database flags. The default long_query_time of 10 seconds is why many teams believe they have no slow queries: a 4-second checkout query never gets logged. Start at 1 second. Mature setups go lower. Part two covers tuning this properly, including log_queries_not_using_indexes and persisting the settings in my.cnf.

With the meter running, here's what you'll find.

---

1. Missing or wrong indexes

What it looks like. One query that was fine at 100K rows and is now the top line in every slow log sample. Latency grows with table size: 80 ms last quarter, 400 ms now, 2 seconds by year end. Nothing changed in the code. The table just grew past the point where scanning it was cheap.

Why it happens. Indexes get created for the queries that existed at schema-design time. Then the product adds a filter, an ORM generates a new WHERE clause, or a column gets wrapped in a function (WHERE DATE(created_at) = ... can't use an index on created_at). A wrong index is as common as a missing one: an index on (status) helps a query filtering on status and tenant_id far less than a composite index would.

How to detect it. EXPLAIN the suspect query:

EXPLAIN SELECT * FROM orders
WHERE tenant_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

Read three columns: type (avoid ALL), key (the index actually chosen; NULL means none), and rows (the optimizer's estimate of rows examined). A query returning 20 rows while examining 800,000 is the classic missing-index signature. MySQL index optimization is mostly the discipline of making rows examined converge toward rows returned.

Typical impact. The biggest category. Fixing one missing composite index routinely takes a query from seconds to single-digit milliseconds, two to three orders of magnitude, and cuts the CPU that query was burning on every execution.

2. Full table scans

What it is. The symptom cause #1 produces at scale, worth treating separately because full scans also come from queries that can't use any index: leading-wildcard LIKE '%term%', type mismatches (quoted numbers against an integer column, or the reverse), and OR conditions across different columns. Each execution reads the entire table through the buffer pool, evicting pages that hot queries needed.

Why it happens. Nobody notices a scan on a small table, and every table starts small. The scan also poisons its neighbors: well-indexed queries slow down because the buffer pool is churning, so the query you blame is often not the query at fault.

How to detect it. The performance_schema aggregates every statement digest, and the sys schema wraps it in readable views:

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

This is the fastest single query for finding scan offenders you didn't know existed. No log parsing, and it covers statements too fast to hit the slow log individually but expensive in aggregate.

Typical impact. Beyond the query's own latency, scans are the usual reason a db.r6g.xlarge "needs" to become a 2xlarge. Teams that clear their top scan offenders often find the instance was one size too big all along, commonly a 30 to 50% saving on that instance.

3. Bad joins

What it looks like. A query joining three or four tables that's fine in staging and pathological in production. In EXPLAIN output, one joined table shows type: ALL, meaning MySQL scans that entire table for every row of the outer table. A 10,000 × 50,000 nested loop is 500 million row visits for a report that returns 200 rows. (MySQL 8.0.18+ may use a hash join instead, which helps, but it still reads the whole inner table and spends memory doing it.)

Why it happens. The join column on one side has no index (foreign keys in application-managed schemas often don't), the columns have mismatched types or collations (utf8mb4_general_ci joined to utf8mb4_0900_ai_ci prevents index use), or an ORM eagerly joins six tables when two were needed.

How to detect it. EXPLAIN ANALYZE (MySQL 8.0.18+) actually executes the query and reports real row counts and per-step timing, which shows exactly which join step explodes:

EXPLAIN ANALYZE
SELECT o.id, c.email FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= NOW() - INTERVAL 7 DAY;

Compare estimated and actual rows at each loop. A join step whose actual rows are orders of magnitude above the estimate, or any inner table accessed as a full scan, is your culprit. Because it really runs the statement, point it at a replica when the query is heavy.

Typical impact. Bad joins are the queries that take down instances. One analytics query with an unindexed join can hold a CPU core for minutes and starve OLTP traffic. Fixing the join index typically turns minutes into milliseconds.

4. Temporary tables spilling to disk

What it is. GROUP BY, ORDER BY on non-indexed columns, DISTINCT and UNION often need an internal temporary table. Small ones live in memory. Once a result outgrows the in-memory limit (tmp_table_size, plus temptable_max_ram for the TempTable engine in MySQL 8.0), MySQL moves it to disk. The query's cost profile changes from CPU-bound to I/O-bound, and p99 latency becomes hostage to disk throughput.

Why it happens. Result sets grow with the business. The GROUP BY that fit in memory at launch doesn't at 10x the data, and the query text never changed, so nothing shows up in code review.

How to detect it. Check the ratio of disk temp tables to total:

SHOW GLOBAL STATUS LIKE 'Created_tmp%tables';

If Created_tmp_disk_tables is more than 10 to 20% of Created_tmp_tables, some queries are spilling. To find which statements, query the digest table directly:

SELECT DIGEST_TEXT, COUNT_STAR, SUM_CREATED_TMP_DISK_TABLES
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_CREATED_TMP_DISK_TABLES > 0
ORDER BY SUM_CREATED_TMP_DISK_TABLES DESC LIMIT 10;

Typical impact. Latency multipliers of 5 to 20x on the affected queries, plus IOPS charges on cloud volumes. Often fixable with an index that lets MySQL read rows already in order and skip the temp table entirely, which is cheaper than the RAM upgrade teams usually reach for.

5. Lock contention

What it looks like. Queries that are fast in isolation but slow in production, with wildly variable latency. SHOW PROCESSLIST shows sessions in Waiting for table metadata lock, or transactions stacked behind one row. The slow log entry shows a large Lock_time relative to Query_time: the query didn't work slowly, it waited.

Why it happens. Long-running transactions (an app that opens a transaction, calls an external API, then commits), hot rows like counters that every request updates, UPDATE and DELETE statements without index support that lock far more rows than intended, or a migration waiting for a metadata lock behind an open transaction while every new query queues behind the migration.

How to detect it. While contention is happening, ask the sys schema who's blocking whom:

SELECT waiting_query, blocking_query, wait_age,
       waiting_pid, blocking_pid
FROM sys.innodb_lock_waits;

This joins InnoDB's lock-wait data into one row per blocked session, including the blocking statement. That's usually enough to identify the offending transaction pattern. For metadata locks, sys.schema_table_lock_waits does the same job.

Typical impact. The most dangerous cause, because it cascades. One blocked writer queues everything behind it, connections pile up toward max_connections, and a 200 ms hiccup becomes a full outage. Lock contention is behind a large share of "the database was down" incidents where the database was, technically, up.

---

Telling the five apart: one worked example

The causes overlap in how they feel ("checkout is slow") and differ in what they leave behind. So triage starts with a hypothesis per family and the one number that would confirm it.

01If it is a plan

Rows examined

High ratio of rows examined to rows sent, steady latency that grows with data.

02If it is memory

Disk tmp tables

Created_tmp_disk_tables climbing, EXPLAIN shows Using temporary.

03If it is locking

Lock time

Large Lock_time in the slow log, latency that swings wildly between runs.

Here's a real-shaped case (the numbers are illustrative). The order list page went from 300 ms to 2.5 seconds over a month. Three readings are possible:

• A plan problem. Predicts a high rows-examined-to-rows-sent ratio and steady latency.
• A temp table spill. Predicts disk temp tables on this digest and Using temporary or Using filesort in the plan.
• Lock waits. Predicts a large Lock_time and latency that varies run to run.

The slow log entry settles the first fork in one read:

# Query_time: 2.412  Lock_time: 0.000118  Rows_sent: 20  Rows_examined: 812344
SELECT * FROM orders WHERE tenant_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

Lock_time is a tenth of a millisecond, so locking is out. 812,344 rows examined to return 20 points straight at the plan. EXPLAIN confirms it: key is idx_status, rows is roughly 800K, and Extra shows Using where; Using filesort. The index on status alone matches most of the table because most orders are pending for some tenant, and the sort happens after the filter.

The fix matches the access pattern, equality columns first and the sort column last:

ALTER TABLE orders
  ADD INDEX idx_tenant_status_created (tenant_id, status, created_at);

Re-running EXPLAIN shows type: ref on the new index, rows near 20, and no filesort. The temp-table hypothesis also disappears, because the sort is now served by index order. One index, two causes closed, and the evidence for each step fits in a ticket.

Why the Thursday firefight never ends

01Thursday

App is slow

A Slack thread reaches the "is it the database?" stage.

02Ten minutes later

Restart

SHOW PROCESSLIST, a pod restart, and the symptom goes away.

03Next sprint

Regrowth

Tables grow, a deploy ships new query shapes, nobody reads the slow log.

04Next Thursday

Same thread

The same queries, a little slower, and a bigger instance on the bill.

Each cause regenerates. Tables keep growing past index tipping points, every deploy ships new query shapes, data drift turns in-memory temp tables into disk spills, and one new endpoint can introduce a hot row. A triage session fixes the queries that were slow that day. It doesn't watch the ones going slow next month at a rate of a few per sprint.

The traditional answer breaks for structural reasons, not effort:

The deeper difference is that slow queries are a rate problem. New ones appear every sprint, so any process that depends on someone remembering to look will lose to the backlog.

Without a DBA, the honest options are: make one engineer the part-time query person (and accept the context-switching cost), or make detection continuous so problems surface with evidence attached instead of in Slack threads. Start with the first. Part two of this series is a full DIY triage workflow using the slow log, mysqldumpslow, performance_schema, the sys schema and EXPLAIN ANALYZE, with copy-pasteable SQL for each step.

Make the watching continuous

Everything in this guide can run unattended. CloudThinker connects to MySQL with a read-only user, watches the slow log and performance_schema continuously, and surfaces new offenders with the evidence (query digest, rows examined, the plan, a proposed index) the day they appear, not the Thursday they take the app down. Changes go through Auto Mode, so you decide what runs on its own and what waits for approval. Part three shows how that works.

Try CloudThinker free with 100 premium credits and no card required, or continue with the DIY triage guide.