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 no one whose job it was to look. And here's 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 way to detect it, and a predictable cost in latency and instance size.

This guide walks through all five — what each looks like in practice, one real detection command per cause, and what it typically costs you. It's part one of a three-part series: 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.

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; default is 10, far too high

On RDS or Cloud SQL you set the same variables through a parameter group or flags instead. 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.

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 linearly with table size: 80ms last quarter, 400ms 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 just as common as a missing one: an index on (status) doesn't help a query filtering on (status, tenant_id) nearly as much as 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 (which index was 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 single 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 that cause #1 produces at scale, but 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 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 neighbors: your fast, 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 readably:

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 required, 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 discover the instance was one size too big all along — commonly a 30–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 re-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.

Why it happens. The join column on one side has no index (foreign keys in application-managed schemas frequently don't), or 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 exposes 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;

In the output, compare estimated versus 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.

Typical impact. Bad joins are the queries that take down instances — one analytics query with an unindexed join can consume a full 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 UNIONs often need an internal temporary table. Small ones live in memory; once a result exceeds tmp_table_size/max_heap_table_size (or contains large TEXT/BLOB columns), MySQL writes 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 16MB of memory at launch doesn't at 10x the data — and the query text never changed, so nothing shows up in a 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–20% of Created_tmp_tables, you have spilling queries. 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–20x on the affected queries, plus IOPS charges on cloud volumes. Often fixable with a covering index that lets MySQL skip the temp table entirely — 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/DELETE statements without index support locking far more rows than intended, or a migration taking a metadata lock behind an open transaction.

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 exact blocking statement — usually all you need to identify the offending transaction pattern.

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

Why the Thursday firefight never ends

Each cause above 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.

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 via Slack threads. Start with the first — part two of this series is a full DIY triage workflow using the slow log, performance_schema, sys schema, and EXPLAIN, with copy-pasteable SQL for each step.

Make the watching continuous

Everything in this guide can run unattended. Tony, CloudThinker's database agent, connects to MySQL read-only, watches the slow log and performance_schema continuously, and surfaces new offenders with the evidence — query digest, rows examined, missing-index recommendation — the day they appear, not the Thursday they take the app down. No schema change happens without your approval. Try CloudThinker free — 100 premium credits, no card required — or continue with the DIY triage guide.