How to Fix Slow MySQL Queries: 6 Fixes That Actually Work

Quick answer: To fix slow MySQL queries, follow a three-phase workflow — find the slow query using the slow query log and pt-query-digest, diagnose it with EXPLAIN ANALYZE, then fix it by adding the right index, rewriting non-sargable clauses, or sizing your InnoDB buffer pool correctly. Most slow queries trace back to five root causes: a missing index, an unoptimizable query, a schema change that invalidated an index, table growth that killed index selectivity, or a buffer pool still running on defaults. The whole diagnostic loop takes under an hour once you know the sequence.

I still remember the night a single query took down a client's checkout page. Traffic was fine. The server was fine. One SELECT was eating 4.2 seconds, and every user paid for it. So if you're here to learn how to fix slow MySQL queries, you're in the right place — because I've been in that exact seat, at 2 a.m., staring at a terminal.

Here's the thing nobody tells you: slow MySQL queries are almost never mysterious. They're boring. Predictable, even.

That workflow is what I'm handing you today.

What Actually Makes a MySQL Query "Slow"?

Before you touch a single index, you need a definition. Because "slow" is not a feeling — it's a threshold you set. Too many developers open a config file and start changing values based on vibes, which is roughly as effective as percussive maintenance on a server rack. In MySQL, a query is slow when it exceeds your configured long_query_time value. That's it. Your API might tolerate 500ms; a reporting job might tolerate 30 seconds. Query performance is contextual, and your threshold should reflect your real users.

Now, why do queries cross that line?

According to Bytebase's practical guide, it usually comes down to four things: a missing index, a query the planner cannot optimize, a schema change that quietly invalidated an index, or a table that grew until its old indexes stopped being selective.

I'd add a fifth from my own consulting work — server memory that was never tuned past the defaults.

And the payoff for getting this right is not marginal. JusDB's engineering team documented a SaaS client whose P99 query latency sat at 4.2 seconds on a lookup that should have been milliseconds — the table had 12 indexes and the query wasn't using any of them. Within 48 hours, P99 was at 180ms. No schema redesign. No hardware upgrade. Configuration corrections plus one missing composite index.

Let me be blunt with you. Most MySQL performance tuning advice online is a listicle written by someone who has never watched a production database melt. What follows is the actual sequence: find, then diagnose, then fix.

Never skip a phase.

Phase 1: Find the Slow Query (Stop Guessing)

Here's where I see people go wrong immediately — they assume they know which query is slow. They're usually wrong. The MySQL slow query log exists precisely so you don't have to rely on intuition, and it's been sitting in your installation this whole time, probably switched off. Enabling it takes about ninety seconds and it will change how you think about your database. So let's turn it on properly, and let's talk about the gotchas that bite people the first week.

Enabling the Slow Query Log

Per the official MySQL 8.0 documentation, the log records statements that take longer than long_query_time seconds and examine at least min_examined_row_limit rows.

Both conditions matter. Miss that and you'll wonder why your log is empty.

Here's my starting config:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query_log_file';

For persistence, drop it in my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Three things you must know, and I mean must:

One. The default long_query_time is 10 seconds. Ten! That's an eternity. Nearly nothing gets logged at the default, which is why so many teams believe their database is healthy.

Two. By default, MySQL does not log queries that skip indexes, nor administrative statements like ALTER TABLE, ANALYZE TABLE, or OPTIMIZE TABLE. You enable those with log_queries_not_using_indexes and log_slow_admin_statements.

Three — and this one has burned me personally — enabling log_queries_not_using_indexes can make your log balloon fast. The docs recommend log_throttle_queries_not_using_indexes to impose a per-minute cap. Set it. Your disk will thank you.

One more detail for accuracy nerds: as of MySQL 8.0.14, the log timestamp marks when the statement began executing. Before 8.0.14, it marked when the statement was logged, which happens after completion. If you're correlating logs with an incident timeline, that difference matters enormously.

You can also send output to the slow_log table instead of a file using the log_output variable — handy when you'd rather query your logs than grep them.

Choosing Your Log Analysis Tool

Raw log files are unreadable past a few hundred entries. Trust me on this. Three tools do the heavy lifting, and picking the wrong one wastes an afternoon. Here's how they actually compare when you're standing in front of a production incident:

MySQL ships with mysqldumpslow, which groups similar statements by abstracting literal numbers and strings. Fine for a quick look.

But the real tool is pt-query-digest from Percona Toolkit. It groups queries by fingerprint — the abstracted form with literals stripped and whitespace normalized — so a thousand variations of the same statement collapse into one line item.

pt-query-digest /var/log/mysql/slow.log > report.txt

Now here's the pro move that took me years to learn.

Sort by total time, not average:

pt-query-digest --group-by fingerprint --order-by Query_time:sum --limit 10 slow.log

Why? Because Percona's community forums are full of teams whose real problem was thousands of fast queries, not a handful of slow ones. A 40ms query running 50,000 times per minute will quietly destroy you while your "slowest query" report shows nothing alarming.

Prefer SQL to shell scripts? The Performance Schema gives you the same intelligence:

SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

(Related reading on this site: our guide to database monitoring and observability covers alerting on these metrics before users notice.)

Phase 2: Diagnose With EXPLAIN

You've got a culprit. Excellent. Now resist — and I cannot stress this enough — the urge to immediately add an index. Query optimization without diagnosis is just superstition with extra steps. EXPLAIN shows you precisely how MySQL intends to execute your statement: which tables it reads, in what order, and which indexes it plans to use. This single command separates engineers who fix databases from engineers who poke at them hopefully.

Reading the Output

EXPLAIN SELECT * FROM orders 
WHERE customer_id = 12345 
AND order_date BETWEEN '2026-01-01' AND '2026-12-31';

Four columns carry most of the signal: type, key, rows, and Extra.

That rows versus returned-rows ratio is my favorite diagnostic. Examining 800,000 rows to return 12? You've found your problem.

The EXPLAIN Type Decision Tree

This is the table I wish someone had handed me a decade ago. Find your type value, read across, apply the fix.

And here's the Extra column companion:

The Three EXPLAIN Formats Worth Knowing

EXPLAIN ANALYZE — available since MySQL 8.0.18 — actually runs the query and reports real timings instead of estimates. It's most useful exactly when the optimizer's estimates are misleading, which is more often than you'd hope.

EXPLAIN FORMAT=JSON adds cost estimates and metadata the tabular output hides.

EXPLAIN FORMAT=TREE renders a readable execution tree — genuinely necessary once you're debugging four-table joins.

Quick reality check, though: a full table scan on a 500-row lookup table is completely fine. A full scan on eight million rows is a fire. Context, always.

Phase 3: How to Fix Slow MySQL Queries at the Query Level

Right. You know what's slow and you know why. Now we fix it — and I'm going to walk you through this in the order I actually apply these fixes on client work, because sequence matters. Cheap wins first, structural surgery last. Roughly 70% of the database performance problems I've diagnosed were solved in this section alone, usually within an hour, usually with a single well-chosen index.

Step 1: Add the Missing Index

Missing indexes cause more slow queries than every other factor combined.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

Index the columns appearing in your WHERE, JOIN, ORDER BY, and GROUP BY clauses. Then re-run EXPLAIN to confirm the optimizer actually took the bait — it doesn't always.

Step 2: Master the Leftmost Prefix Rule

This concept is non-negotiable, so let me slow down.

The MySQL manual states it plainly: with a three-column index on (col1, col2, col3), you get indexed lookups on (col1), on (col1, col2), and on (col1, col2, col3). MySQL cannot use that index when your referenced columns don't form a leftmost prefix.

What This Looks Like in Practice

Given CREATE INDEX idx ON orders (status, customer_id, created_at):

✅ WHERE status = 'shipped' — uses it ✅ WHERE status = 'shipped' AND customer_id = 42 — uses it ❌ WHERE customer_id = 42 — cannot use it ❌ WHERE created_at > '2026-01-01' — cannot use it

The Two Column-Order Rules

Put your most selective column first when all conditions are equality. Put range-filtered columns last, always.

Caveat worth mentioning: some planners support skip-scan optimizations that work without the leftmost column, but it's version-dependent and reliably slower than a proper prefix. Don't design around it.

Step 3: Build a Covering Index

A covering index contains every column your query needs, so MySQL answers entirely from the index and never touches the table rows. That eliminates the key-lookup step completely.

CREATE INDEX idx_covering ON orders (customer_id, status, total_amount);

SELECT status, total_amount FROM orders WHERE customer_id = 42;

Look for Using index in your Extra column. That's the confirmation.

(More depth in our complete MySQL indexing strategies guide.)

Step 4: Rewrite Queries the Optimizer Hates

Some queries can't be indexed out of trouble because they're written in ways that defeat indexing entirely.

Never Wrap an Indexed Column in a Function

WHERE YEAR(created_at) = 2026 throws your index away. This is what's called a non-sargable query. Use a range instead: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'.

Kill SELECT *

And not for the reason you've heard. JusDB's team makes the sharper point: naming columns explicitly lets the optimizer consider covering indexes. Bonus — oversized VARCHAR columns cause memory bloat because temp tables and sort buffers allocate the full declared length, even though InnoDB only stores actual length on disk.

Fix Your Pagination

LIMIT 20 OFFSET 500000 forces MySQL to walk half a million rows before discarding them. Use keyset pagination: WHERE id > 500000 ORDER BY id LIMIT 20.

Eliminate the N+1 Query Problem

If your ORM fires one query for a list and then one more per row, you don't have a slow query — you have 501 fast ones. Eager-load with a JOIN or an IN clause instead. pt-query-digest sorted by Query_time:sum will expose this instantly.

Step 5: Investigate Index Decay

Here's the scenario that fools senior engineers.

"It was fast last year."

Bytebase describes it perfectly: an index on status holding values like pending/shipped/cancelled has terrible cardinality. At 1,000 rows nobody noticed. At 10 million rows, that index points at three million matching rows and the optimizer sensibly ignores it.

Table growth is the single most common reason a once-fast query goes slow.

Two related traps. Dropping an index removes an optimization existing queries silently depend on — check sys.schema_unused_indexes and your slow log before you drop anything. And changing a column type, INT to BIGINT or a longer VARCHAR, can shift selectivity enough to break plans. Re-run EXPLAIN afterward.

Refresh stale statistics with ANALYZE TABLE orders;.

Step 6: Stop Over-Indexing

Indexes are not free, and this is the mistake I see most often in the second month after someone reads an article like this one.

Every index consumes storage, occupies buffer pool memory that data pages could be using, and must be updated on every INSERT, UPDATE, and DELETE. Remember that JusDB case study — the table had 12 indexes and the query used none of them. Index sprawl was part of the disease, not the cure.

Audit quarterly:

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

The goal isn't the most indexes. It's the right ones.

Phase 4: Tune the Server (The Skipped Chapter)

Almost every article on this topic stops at indexes, and that's a genuine shame — because the single highest-leverage change available to you isn't a query rewrite at all. It's one memory setting most teams never touch. If your InnoDB buffer pool is running at its factory default, no amount of clever indexing will save you, since MySQL will keep reaching for the disk when it should be reading from RAM.

Size the InnoDB Buffer Pool

The MySQL manual recommends configuring innodb_buffer_pool_size to 50–75% of system memory. Practitioners generally push toward 70–80% on dedicated servers.

The default is 128MB. Let that sink in.

innodb_buffer_pool_size = 12G   # ~75% of 16GB
innodb_buffer_pool_instances = 8

Check your hit ratio:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Compare Innodb_buffer_pool_read_requests (logical reads) against Innodb_buffer_pool_reads (actual disk hits). Below 99%? Your working set has outgrown memory.

Don't overshoot, though. Leave 1–2GB for the OS and per-thread buffers, or you'll trigger swapping — which is dramatically worse than a small buffer pool.

Stop Using the Query Cache

It's gone. Removed in MySQL 8.0 entirely, because it became a bottleneck under concurrency thanks to constant invalidation. Use Redis or Memcached outside the database instead — and if your read volume is the real constraint, look at read replicas and connection pooling before you buy a bigger box.

Your Diagnostic Checklist

Bookmark this sequence:

1. Enable the slow query log at long_query_time = 1

2. Run pt-query-digest sorted by Query_time:sum

3. EXPLAIN ANALYZE the top offender

4. Check type, key, rows, and Extra against the decision tree above

5. Add or reorder the index

6. Re-run EXPLAIN to verify adoption

7. Confirm buffer pool sizing and hit ratio

8. Audit for unused indexes

9. Re-measure — always re-measure

Wrapping Up: Your Next 30 Minutes

Look, I know how this goes. You read a guide like this, you feel motivated, and then Monday arrives with four Jira tickets and the database gets to keep being slow. So let me make this concrete for you.

You don't need a full afternoon. You need thirty minutes.

Start by enabling your slow query log at long_query_time = 1. That takes ninety seconds and it's the highest-leverage thing you'll do all week. Then walk away — let it collect data while you do actual work. Come back tomorrow, run pt-query-digest sorted by Query_time:sum, and look at the top three results.

I'd bet money on what you find. One query you didn't suspect, running far more often than you realized, missing an index on a column you'd have sworn was already covered.

Here's what I want you to take away from all of this.

Learning how to fix slow MySQL queries isn't about memorizing configuration values. It's about building a repeatable diagnostic habit — measure, don't guess. Every engineer who's genuinely good at MySQL performance tuning got there the same way: they stopped trusting their intuition about which query was slow and started reading the evidence instead.

The tools aren't hard. The EXPLAIN decision tree in this post covers maybe 90% of what you'll encounter in production. The leftmost prefix rule takes ten minutes to internalize and then serves you for a decade. And checking whether your InnoDB buffer pool is still sitting at the 128MB default? That's one query.

Also worth remembering — this isn't purely a backend concern. Slow queries inflate your server response time, which flows straight into Core Web Vitals and shapes how well your pages rank on Google. That 4-second query is costing you more than patience.

So: enable the log. Pick one query. Run EXPLAIN ANALYZE.

Then fix it, re-measure, and do it again next month.

That's the whole discipline. Everything else is detail.

Got a query that's still fighting you after all this? Drop the EXPLAIN output in the comments — I read every one, and half the time the answer is visible in the type column.

Frequently Asked Questions

Why is my MySQL query suddenly slow when it was fast before?

Table growth, almost always. An index that was selective at 1,000 rows becomes useless at 10 million if its column has low cardinality. Schema changes are the second cause — altering a column type or dropping an index can silently invalidate a plan. Run EXPLAIN and compare against ANALYZE TABLE output.

What is a good long_query_time value?

Start at 1 second in production. The MySQL default of 10 seconds is far too permissive and hides most real problems. In development, setting it to 0 captures everything — useful for short bursts, but it floods your disk, so revert quickly.

Does adding more indexes slow down MySQL?

Yes. Every index must be updated on writes and consumes buffer pool memory. Over-indexing degrades INSERT and UPDATE throughput and can confuse the optimizer. Audit with sys.schema_unused_indexes and drop what isn't earning its keep.

How do I know if my index is actually being used?

Run EXPLAIN and check the key column. If it shows NULL, no index was chosen. If type shows ALL, you're doing a full table scan. For confirmation under real conditions, use EXPLAIN ANALYZE on MySQL 8.0.18+.

Should I increase RAM or fix my queries first?

Fix the queries first — it's free and the gains are usually larger. But check innodb_buffer_pool_size before you start. If it's still at the 128MB default, that one change will outperform a week of query tuning.

What's the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the optimizer's intended plan using estimates. EXPLAIN ANALYZE actually executes the query and reports real row counts and timings. Use the latter whenever the estimates look suspicious.

Learning how to fix slow MySQL queries isn't about memorizing tricks. It's about refusing to guess.

Enable your slow log today. Pick one query. Run it through EXPLAIN.

You'll be surprised how quickly the fog lifts.