Why Is MySQL So Slow? A Diagnosis Checklist That Finds the Cause

"MySQL is slow" is a symptom, not a diagnosis. The slowness could be one bad query, a missing index, locks queuing behind each other, disk the buffer pool is too small to avoid, or something that is not the database at all. The fastest way out is not to guess, it is to work through the causes in order of how often they turn out to be true. This checklist does that.

First: is it actually the database?

Before you tune anything, confirm the database is the bottleneck. Time a slow request end to end, then run its main query directly against MySQL. If the query is fast on its own but the page is still slow, the problem lives in the application, the network, or an external call, and tuning MySQL will not help. If the query itself is slow, keep going.

Step 1: Find the slow queries

You cannot fix what you have not measured. Turn on the slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

After an hour of normal traffic, summarise the worst offenders:

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

For a live view of what costs the most in total, not just per run:

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

Step 2: Check for missing indexes

This is the most common cause by a wide margin. Take your slowest query and ask MySQL how it runs it:

EXPLAIN SELECT * FROM invoices WHERE status = 'overdue';

Read two columns:

type of ALL means a full table scan: MySQL is reading every row. That is your missing index.
rows is roughly how many rows it examines. If it examines 500,000 to return 12, an index will change that.

Add the index the query needs and re-check:

CREATE INDEX idx_invoices_status ON invoices (status);

The complete MySQL optimization guide covers how to choose the right index, including composite indexes and the left-prefix rule, so you add the one that helps rather than one more that does not.

Step 3: Look for lock contention

If queries are individually fast but still slow in production, they may be waiting on each other. Check what is blocking what:

SELECT * FROM sys.innodb_lock_waits;

Long transactions that hold locks, or writes fighting over the same rows, make everything behind them wait. The fix is usually shorter transactions and the right indexes, so writes lock fewer rows.

Step 4: Check CPU, memory, and disk

Now look at the machine, in this order:

CPU pinned near 100%? That is usually a query problem, not a hardware one. Work through why MySQL uses 100% CPU.
Buffer pool too small? If innodb_buffer_pool_size is far below your hot data size, MySQL constantly reads from disk. On a dedicated server, set it to roughly 70 to 80% of RAM.
Disk I/O waiting? High read waits with a small buffer pool point the same way: data does not fit in memory. Bigger buffer pool first, faster disk second.

Step 5: Rule out a bad plan

A query that was fast for months can turn slow when the data grows or statistics go stale, because MySQL picks a worse plan. Refresh the statistics:

ANALYZE TABLE invoices;

Then EXPLAIN again and confirm it now uses the index you expect.

The short version

Most MySQL slowness resolves in the first two steps: find the slow query, give it the index it needs. When it does not, the order above, locks, then memory and disk, then plans, finds the rest without guesswork.

Frequently asked questions

How do I know if the database is what's making my app slow?

Time a slow request end to end and see how much of it is spent in the database. If the query itself is fast when you run it directly but the page is still slow, the bottleneck is elsewhere, in the application, the network, or an external API. If the query is slow on its own, the database is the place to look.

What's the single most common reason MySQL is slow?

A missing index. Without one, MySQL reads every row in the table to answer a query, which is fine at ten thousand rows and painful at ten million. EXPLAIN showing a type of ALL confirms a full table scan.

Can MySQL be slow even when CPU and memory look fine?

Yes. Slowness from locking, disk I/O waits, or an undersized buffer pool can leave CPU and memory looking calm while queries queue behind each other. Look at query time and lock waits, not just server-level graphs.

Why did MySQL get slow all of a sudden?

The usual triggers are data growth crossing a threshold where a query stops fitting in memory, a new deploy that added an unindexed query, stale table statistics producing a bad plan, or a spike in traffic. Compare what changed against when the slowness started.

Worked the checklist and the cause is still hiding? Diagnosing slowness under real production load, and fixing it so it stays fixed, is what I do. See MySQL performance tuning, or start with the full MySQL optimization guide.