MySQL Slow Queries: Symptoms, Diagnosis & Fixes

One statement reading a million rows it didn't need to — and the whole app waits on it.

What It Is

A slow query is a single SQL statement that takes far longer than it should — seconds, sometimes many seconds — to hand back its result. On its own that sounds harmless. The trouble is that almost nothing on a server box runs alone. Your database — MariaDB or its MySQL ancestor — sits behind a web app that fires off queries on nearly every page load. When one goes slow, the request waiting on it goes slow too, the visitor staring at a spinner, and behind them the next request, each holding a connection open while it waits its turn. A query that takes four seconds doesn't cost you four seconds; on a busy page it costs four seconds multiplied across everyone who showed up in those four seconds. That's how one bad statement takes down a whole site.

The good news, and the reason this page is worth your time: a slow query is almost never a mystery, and the fix is almost never "buy a bigger server." The database can tell you, in precise detail, which queries are slow and why — and nine times out of ten the why is the same single, fixable thing. By the end of this page you'll switch on the database's own confession log, catch a slow query in the act, ask it to explain itself, recognise the one pattern behind most slowdowns, and fix it — usually with one line. We lead with finding and fixing, and save the satisfying why it's slow for the end.

How You Notice

A slow query rarely announces itself by name. It shows up as a cluster of secondary symptoms, and learning to recognise the cluster is half the battle. Here's each one, with how to see it on your own box right now:

Pages hang or time out. A particular page — a search, a report, a dashboard — takes five, ten, twenty seconds, or returns a gateway timeout. The slow page is usually the one running the slow query, a useful clue before you've touched the database at all.
The database is pegging a core. In top the mysqld (or mariadbd) process sits near the top eating CPU. A query sifting through a million rows to return ten burns real CPU doing it. Sustained, that's high load.
Or the disk is the bottleneck instead. If the table is bigger than the memory the database can cache it in, the slow query reads pages off the disk rather than out of RAM. Now the wa (I/O wait) figure in top climbs while the CPU sits idle — the box isn't busy, it's waiting on the drive to feed it rows. Sustained, that's high I/O wait, and a full-table scan over a cold, oversized table is a classic way to trigger it.
A pile-up in the process list. Connect to the database and ask what it's doing right now:
```
SHOW FULL PROCESSLIST;
```
Each row is one connection; Time is seconds the statement has run, State is what it's doing. One connection at Time: 8, State: Sending data, running a big SELECT — that's your slow query, caught live. The real tell is a whole column of connections running the same query with climbing Time values: the pile-up. The slow query came in, and while it ground away every later request for that page queued behind it — the app didn't break, it's waiting in a neat, terrifying little line.
The slow query log filling up. Once you've turned it on (in a moment), the slow query log grows every time a query crosses your threshold. A log gaining megabytes by the hour is the database telling you, query by query, where it hurts. An empty one is good news.

Any one of these means: stop guessing and ask the database directly. It keeps better records about its own performance than any other software on your box — you just have to switch the recorder on.

How I Diagnose It

Diagnosis is three moves, in order: switch on the log so the database writes down every slow query, read the log to find the worst offenders, and ask one query to explain itself so you can see why it's slow. The third move is where the magic is.

Switch On the Slow Query Log

The database can log every statement that runs longer than a threshold you choose. It's off by default. Turn it on live, no restart needed:

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

That records any query taking longer than long_query_time seconds — here 1 — into that file. (The threshold accepts fractions: 0.5 catches half-second queries, useful when the culprit is slow-ish but frequent rather than catastrophic-but-rare.) One more switch worth flipping:

SET GLOBAL log_queries_not_using_indexes = 'ON';

This logs queries that ran without an index even when they were fast — because a query scanning a small table is quick today and a time bomb the day that table grows. An early-warning channel for exactly the problem this page is about.

Note

SET GLOBAL changes last only until the database restarts. To make the slow query log survive a reboot, put the same settings — slow_query_log = 1, long_query_time = 1, slow_query_log_file = /var/log/mysql/slow.log — under the [mysqld] section of the config file (/etc/mysql/my.cnf or a file under /etc/mysql/mariadb.conf.d/). The live SET is for catching a fire now; the config line is so you never have to scramble for it again.

Read the Log

Each slow query lands in the log as a little block: who ran it, how long it took, how many rows it examined, and the SQL itself.

# Time: 2026-06-12T09:14:02.481113Z
# User@Host: webapp[webapp] @ localhost []
# Query_time: 4.882013  Lock_time: 0.000091 Rows_sent: 12  Rows_examined: 1048576
SET timestamp=1749718442;
SELECT * FROM orders WHERE customer_email = 'someone@example.com';

Read the third line and you've half-solved it. Query_time: 4.88 — nearly five seconds. Rows_sent: 12 — it returned twelve rows. Rows_examined: 1048576 — it looked at a million rows to find those twelve. That ratio, examined versus sent, is the most diagnostic number in the whole log. Sending twelve rows is fine; reading a million to find them is the problem. The database had to inspect every row because it had no faster way to find the matching ones — the fingerprint of a missing index, which you'll confirm in the next step.

On a busy server the log is too long to read by eye, so rank it. The tool that ships alongside mysqldump is its sibling mysqldumpslow, which groups identical queries (ignoring the literal values) and sorts them by total time:

mysqldumpslow -s t /var/log/mysql/slow.log

-s t sorts by total time spent, so the query at the top costs you the most aggregate misery — often not the single slowest run, but a merely-slow query run ten thousand times. Fix the query that wastes the most time in total, not the one that looks scariest in isolation.

Pro Tip

The query that hurts most is rarely the slowest single run — it's total_time = per_run × frequency. A 6-second report someone runs twice a day is a rounding error next to a 0.2-second query on your busiest page firing forty times a second. Always rank by total time (mysqldumpslow -s t), then fix top-down. You'll often clear most of your database load by fixing two or three queries.

Ask the Query to Explain Itself

Now the centrepiece. Take the worst offender from the log and put the word EXPLAIN in front of it. The database won't run the query — it'll hand you its plan for running it, the strategy it would use to find your rows:

EXPLAIN SELECT * FROM orders WHERE customer_email = 'someone@example.com';

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1048576 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

This one row is the diagnosis, and a few columns tell you everything:

type = ALL. The access type — how the database reaches the rows — and ALL is the worst there is: a full table scan, reading every row in the table and checking each against your WHERE. Best to worst, the values run roughly const → eq_ref → ref → range → index → ALL. Anything but ALL means an index is helping; ALL means none is.
rows = 1048576. The estimate of how many rows it'll examine. A million. Cross-reference it with Rows_examined from the slow log and the story is airtight: it really is reading the whole table.
key = NULL. Which index it chose. NULL means none — no usable index, so it fell back to brute force.
Extra = Using where is benign here (just filtering), but two other values in this column are red flags: Using filesort (an extra sort pass, expensive on big sets) and Using temporary (it built a temp table to answer, common with GROUP BY on unindexed columns).

The reading is unambiguous: type: ALL, key: NULL, rows: 1048576 — a full scan of a million-row table on every lookup, because the filtered column customer_email has no index. The fix writes itself.

When the plan looks reasonable but the query is still slow, EXPLAIN ANALYZE (MySQL 8.0.18+, MariaDB 10.1+) goes one better: it actually runs the query and reports the real time and row counts at each step next to the estimates — invaluable when the optimiser's row estimate is wildly wrong, usually because the table's statistics are stale (an ANALYZE TABLE orders refreshes them).

How to Fix It

The right fix depends on what EXPLAIN showed you — but for the overwhelmingly common case, the full table scan, the fix is one line and the effect is dramatic.

Add the Right Index — the #1 Fix

An index is a sorted lookup structure the database keeps alongside the table — picture the index at the back of a book. Without one, finding every mention of a word means reading the book cover to cover (the full scan). With one, you flip to the index and jump straight to the pages. That's exactly the difference between type: ALL over a million rows and a direct lookup of a handful.

Our slow query filtered on customer_email, which had no index. So build one:

ALTER TABLE orders ADD INDEX idx_customer_email (customer_email);

(Equivalently, CREATE INDEX idx_customer_email ON orders (customer_email); — same thing.) Now re-run the EXPLAIN:

+----+-------------+--------+------+--------------------+--------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys      | key                | key_len | ref   | rows | Extra |
+----+-------------+--------+------+--------------------+--------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_customer_email | idx_customer_email | 768     | const |   12 | NULL  |
+----+-------------+--------+------+--------------------+--------------------+---------+-------+------+-------+

Look what moved. type went ALL → ref (an indexed lookup), key went NULL → idx_customer_email (it's using the index), and rows collapsed from 1048576 to 12. It no longer reads the whole table; it walks straight to the twelve matching rows. A query that took 4.88 seconds now takes a millisecond — one line, three orders of magnitude. The single most satisfying fix in database work.

The rule of thumb: any column you regularly filter on (WHERE), join on (JOIN ... ON), or sort by (ORDER BY) wants an index. When a query filters on several columns at once, a composite index on them together (ADD INDEX idx_cust_status (customer_id, status)) beats two single-column indexes — but column order matters, because a composite index is used only left-to-right (an index on (customer_id, status) helps a query filtering on customer_id alone, but not one filtering on status alone).

Warning

Indexes aren't free, so don't reflexively index every column. Each index is a second structure the database must keep updated on every INSERT, UPDATE, and DELETE — so over-indexing a write-heavy table slows down your writes and wastes disk to speed up reads you may not even run. Index the columns your slow log proves you query on, not every column you might. The slow query log tells you which indexes you actually need; add those, and resist the rest.

Rewrite the Query

Sometimes the index exists and the query still won't use it, because the query is written in a way that defeats it. The four classics:

SELECT * when you need three columns. Fetching every column drags back data you'll throw away, and can stop the database from answering entirely from the index (a "covering index", where the index holds every column the query needs, so the table is never touched). Name the columns you want.
A function around an indexed column. WHERE YEAR(created_at) = 2026 can't use an index on created_at, because the index stores raw dates, not the result of YEAR() on each — so it computes the function for every row, a full scan in disguise. Leave the column bare instead: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'. (Same trap with UPPER(email) or arithmetic on a column.)
A leading wildcard in LIKE. WHERE name LIKE '%smith' can't use an index, since an index is sorted from the front of the string and a leading % leaves no prefix to seek to. LIKE 'smith%' (wildcard on the end) uses it fine. To search the middle of text, that's what a full-text index is for.
N+1 from the app. Not one slow query but a thousand fast ones: the app fetches 100 orders, then loops firing a separate query for each order's customer. The slow log shows the same little query a hundred times in a row. The fix lives in the app — fetch them in one JOIN or one IN (...) lookup. Your ORM almost certainly has an "eager loading" option for exactly this; it's the most common self-inflicted database wound there is.

Schema, Cache, and Only Then the Threshold

A few fixes beyond the query itself:

Cache the expensive-but-rarely-changing result. A dashboard running a heavy aggregate on every page load doesn't need numbers fresh to the millisecond. Compute it once a minute and serve the cached value — sixty runs an hour instead of sixty a second.
Fix the schema. Numbers stored as text, or VARCHAR columns far wider than the data needs, bloat the table and its indexes and slow every scan. Right-sized columns are faster columns.
Raise long_query_time — last, and only after fixing the real offenders. When the log is noisy it's tempting to bump the threshold from 1 to 5 so the noise stops. Resist that until the genuinely slow queries are gone, or you've done nothing but unplug the smoke alarm. The threshold tunes signal; it doesn't silence the problem.

How to Avoid Them

You can't make every query fast by accident, but you can make slow ones rare and loud:

Index as you design the schema. When you create a table you already know what you'll filter and join on — the foreign keys, the email lookups, the status filters. Add those indexes up front; it's far cheaper than diagnosing a scan in production months later under load.
EXPLAIN every new query before it ships. A reflex: any query that runs in a hot path gets an EXPLAIN before it's merged. See type: ALL and a big rows, and you've caught the problem at your desk instead of at 3 a.m. It takes ten seconds.
Leave the slow query log on, and actually read it. A threshold of 1 second (or lower) with log_queries_not_using_indexes enabled turns the database into its own early-warning system. The single most valuable habit isn't any one command — it's noticing the trend: a query that was 0.3 seconds last month and 2 seconds this month is a table outgrowing its indexes, and the log catches it while it's still annoying rather than fatal.

The deepest version of all three is the same idea: a database will happily tell you everything about its own performance, but only if something is listening. Make something listen.

How a Database Actually Finds Your Rows

Now the part you don't need in an emergency — but that turns the whole EXPLAIN table from symbols into something you can reason about. Why is a full scan slow and an indexed lookup fast? It comes down to one idea from the 1970s.

A table on disk is essentially an unordered heap of rows. To find every row where customer_email = 'someone@example.com', a database with no help has one option: start at the first row, check it, move on, all the way to the last. A million rows, a million checks. This is linear — double the table, double the work — and it's why type: ALL with a big rows count is the universal signature of slow. An index changes the shape of the search. The index on customer_email is a separate structure, almost always a B-tree — a wide, shallow, sorted tree holding every email value in order, each paired with a pointer to its row. Because it's sorted, finding a value isn't a walk through a million entries; it's a series of "is the value before or after this one?" decisions, each throwing away half of what's left. That's logarithmic search: in a million sorted entries you reach any value in about twenty comparisons, not a million. A B-tree holding billions of rows is typically only three or four levels deep, so even an enormous index is a handful of steps from root to row. That collapse from a million steps to twenty is exactly the rows: 1048576 → rows: 12 you watched happen. The index didn't make the database faster; it let it stop doing nearly all the work.

This is also why the rewrite traps break indexing. A leading-wildcard LIKE '%smith' is unindexable because the B-tree is sorted from the front of each string — "any prefix" gives it nothing to seek to. A function like YEAR(created_at) defeats the index because the tree stores the raw dates in order and YEAR() scrambles that order. Every indexing rule here is really one fact in a costume: an index helps only when the query lets the database exploit the sorted order. Once you can see the B-tree, you don't memorise the rules — you derive them. One last layer earns the database its keep: the query optimiser, the component that decides whether to use an index, which one, and in what order to join tables. It doesn't follow your query literally — it weighs many possible plans against statistics it keeps (how many distinct values a column has, how big each table is) and picks the cheapest; the rows estimate in EXPLAIN is it showing its work. When it picks a bad plan the cause is usually stale statistics: the table grew but the optimiser's notes still describe the old, smaller one, so it mis-estimates and scans over a perfectly good index. The cure is one command — ANALYZE TABLE orders — to re-survey the table. A query that has a good index and won't use it is often just an optimiser reading an out-of-date map; hand it a fresh one and it finds the road itself.