How to Fix Slow MySQL Queries: A Diagnosis-First Guide That Actually Works

Most articles about how to fix slow MySQL queries hand you a list of twenty fixes and wish you luck. Add an index. Avoid SELECT *. Turn on caching. The advice isn’t wrong, but the order is. You end up trying random fixes on a query you never actually measured, and when one of them happens to help, you don’t know why, so the next slow query sends you back to square one.

This guide does it the other way around. We measure first, find the one query that’s actually hurting, read what MySQL is doing under the hood, and then apply a single targeted fix. That’s how people who tune production databases for a living work, and it’s faster than guessing even though it feels slower at the start.

A quick promise about accuracy: everything here is tested against current MySQL behavior. A few popular tips floating around the web stopped being true years ago (the query cache is the big one, more on that below), and I’ll flag those as we go so you don’t waste an afternoon on advice that no longer applies.

Why Most “Fix Slow MySQL Queries” Advice Fails You

The listicle approach fails for one reason: it skips diagnosis. A slow query is a symptom, not a cause. Two queries that both take four seconds can be slow for completely different reasons. One is missing an index. The other has a perfect index that MySQL refuses to use because you wrapped the column in a function. The same fix applied to both helps one and does nothing for the other.

Here’s the mental model that changes everything. When MySQL runs a query, it builds an execution plan first: which tables to read, in what order, and which indexes to use. A query is slow when that plan forces MySQL to touch far more rows than it needs to hand back. Your job isn’t to “make the query fast.” Your job is to find out why the plan is bad and fix that specific thing.

So the real workflow has four steps, and skipping any of them is why people stay stuck:

• Measure which query is slow and how often it runs
• Inspect the execution plan to see why
• Fix the one root cause that plan reveals
• Verify the plan actually changed, not just hope it did

Everything below follows that order.

Step 1: Prove Which Query Is Actually Slow

You cannot fix what you haven’t measured. Before touching a single index, you need to know exactly which query is the problem and whether it’s one bad query or the same query running thousands of times.

Turn On the Slow Query Log

The slow query log records every query that runs longer than a threshold you set. It’s built into MySQL, costs almost nothing to enable, and you don’t need to restart the server.

-- See what's currently set
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- Turn it on for all connections, no restart needed
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

That last line, log_queries_not_using_indexes, is the one people skip and shouldn’t. It catches queries that run fast today on a small table but will fall over the moment that table grows. According to the MySQL Reference Manual, enabling it can make the log grow quickly, so use it during investigation and testing rather than leaving it on a busy production server forever.

Set long_query_time to Something Useful

The default threshold is 10 seconds. That’s almost useless. A query taking ten full seconds is already a fire. The queries quietly degrading your app sit between half a second and three seconds, and the default never sees them.

Start at 2 seconds on a live system. Once you’ve cleared the obvious offenders, drop it to 1 to find the next layer.

Read the Log Without Drowning in It

A raw slow query log on a busy site is thousands of lines. Reading it by hand is a waste of time. MySQL ships with mysqldumpslow, which groups similar queries and sorts them so the worst offenders rise to the top.

# Sort by total time consumed, show the top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

For anything serious, install pt-query-digest from the Percona Toolkit. It produces far more detail than mysqldumpslow, handles edge cases better, and ranks queries by total impact rather than single-run time. That distinction matters. A query that takes 0.3 seconds but runs 50,000 times a day is hurting you more than a 4-second report someone runs twice a week. The single-run view hides that. The aggregate view exposes it.

Use Performance Schema for the Live View

If you want to see what’s eating resources right now without sifting through a file, Performance Schema gives you a queryable summary:

-- Find tables soaking up the most read I/O since the last reset
SELECT object_schema, object_name, count_read
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_read > 0
ORDER BY count_read DESC
LIMIT 20;

This points you at the tables under the heaviest read pressure, which is usually where your missing index is hiding.

Step 2: Read the Execution Plan With EXPLAIN

This is where the real answer lives. EXPLAIN shows you how MySQL plans to run a query before it runs it. Put the word in front of any SELECT, INSERT, UPDATE, or DELETE and you get the plan back as a table.

EXPLAIN SELECT name, email FROM customers WHERE age > 30;

People glance at this output and move on. Don’t. Three columns tell you almost everything.

The Columns That Actually Matter

The type Column

This describes how MySQL accesses the rows, and it’s the single most important field. From best to worst:

If you see ALL on a table with any real size, MySQL is reading every single row to answer your query. That’s almost always a missing or unused index.

rows vs filtered

The rows column estimates how many rows MySQL expects to examine. filtered is the percentage it expects to keep. When rows is large and filtered is tiny, MySQL is wading through a mountain of data to keep a handful. If rows says 800,000 and the query returns 5, you have a precision problem, and the fix is almost always a better index.

The Extra Column

Watch for two phrases here:

• Using filesort means MySQL is sorting rows in memory or on disk because no index supports your ORDER BY or GROUP BY. On large result sets this is expensive.
• Using temporary means MySQL built a temporary table to process the query, common with GROUP BY across joins. Also expensive.

Seeing Using index (without “temporary” or “filesort”) is the opposite: it means MySQL answered entirely from the index without touching the table. That’s the goal.

EXPLAIN ANALYZE for Real Numbers

Plain EXPLAIN shows estimates. From MySQL 8.0.18 onward, EXPLAIN ANALYZE actually runs the query and reports real timings per step, so you see where time genuinely goes instead of where the optimizer guessed it would.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending';

For deeper structure, EXPLAIN FORMAT=JSON gives cost estimates and nested detail that the table format hides. Reach for it when the standard output isn’t enough to explain a stubborn query.

The Handful of Root Causes Behind Almost Every Slow Query

Here’s the part the listicles get backwards. There aren’t twenty causes of slow queries. There are about six, and once you can recognize them in an EXPLAIN plan, fixing them is mechanical.

1. A Missing Index

The classic. type: ALL, a big rows number, and Extra shows nothing helpful. MySQL is scanning the whole table because there’s no index on the column you’re filtering by.

SHOW INDEX FROM orders;            -- see what exists
ALTER TABLE orders ADD INDEX idx_status (status);

2. A Function Wrapped Around an Indexed Column

This one is sneaky because the index exists, it just can’t be used. The moment you wrap an indexed column in a function, MySQL can no longer use the index on it.

-- Index on created_at is useless here
WHERE YEAR(created_at) = 2024
-- Rewrite as a range, and the index works again
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

Same logic applies to WHERE UPPER(email) = ..., date math on columns, and arithmetic on indexed fields. Move the work off the column and onto the constant.

3. SELECT * When You Need Three Columns

Pulling every column means more I/O, more network transfer, and it blocks covering indexes (explained below) from doing their job. Name the columns you actually use. It’s not glamorous advice, but on wide tables it’s a real win.

4. Joins on Un-Indexed or Mismatched Columns

Joins are where slow queries hide in plain sight. Two rules:

• Every column you join on should be indexed on both sides
• The joined columns should be the same data type. Joining a VARCHAR to an INT, or two strings with different collations, can quietly prevent index use and force a scan

5. Stale Table Statistics

Sometimes you add the perfect index, MySQL ignores it, and you start questioning reality. The optimizer makes decisions based on statistics about your data, and those can go stale after big inserts or deletes. Refresh them:

ANALYZE TABLE orders;

Then run EXPLAIN again. Often the plan flips to using your index immediately.

6. The Query Simply Returns Too Many Rows

No index can save a query that asks for half a million rows. If you’re paginating or building a dashboard, fetch what you show. Use LIMIT, and for deep pagination prefer a keyset (a WHERE id > last_seen_id approach) over enormous OFFSET values, which still scan everything they skip.

Indexing Done Right (The Part People Get Wrong)

“Add an index” is the most common advice and the most commonly misapplied. Indexing well is its own skill.

Composite Indexes and the Leftmost Prefix Rule

When a query filters on multiple columns, a single composite index usually beats several single-column indexes. But column order matters because of the leftmost prefix rule: MySQL can use a composite index only from the left.

An index on (status, created_at) helps queries that filter on status, or on status and created_at together. It does not help a query that filters on created_at alone. Put the column you always filter on first, and the more selective columns earlier.

-- Helps WHERE status = ... AND created_at ...
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at DESC);

Covering Indexes

If an index contains every column a query needs (both the filtered ones and the selected ones), MySQL answers straight from the index and never reads the table. That’s a covering index, and it’s why SELECT * quietly hurts you: it makes covering the query nearly impossible. When EXPLAIN‘s Extra says Using index, you’ve got one.

When NOT to Add an Index

Indexes aren’t free. Every index you add slows down INSERT, UPDATE, and DELETE, because MySQL has to maintain it on every write. It also costs disk space. So:

• Don’t index low-selectivity columns like a boolean is_active on their own
• Don’t pile on indexes a table with heavy write traffic doesn’t need
• Before dropping an index that looks unused, check whether it supports other queries, joins, or constraints first. “Unused for this query” is not “unused”

The MySQL 8.0 Reality Check

Here’s the advice you should ignore from older guides, and why.

The query cache is gone. Plenty of articles still tell you to set query_cache_type and query_cache_size to speed things up. Don’t. MySQL deprecated the query cache in 5.7.20 and removed it entirely in MySQL 8.0. Those settings do nothing on a modern install, and on older versions the cache often hurt write-heavy workloads more than it helped because every write to a table invalidated all cached queries for it. If a tutorial leads with the query cache, it’s out of date, and you should be skeptical of the rest of it.

A few more current-version notes worth keeping straight:

A Repeatable Workflow You Can Run Every Time

Tape this to your monitor. It works for every slow query, every time.

The discipline is in step 5: change one thing, then verify. Changing five things at once means you’ll never know which one worked, and you’ll carry useless indexes forever.

When the Query Isn’t the Problem

Sometimes the query is fine and something else is choking it. Worth ruling out before you keep rewriting SQL:

• Lock contention. A query can look slow because it’s waiting on a lock held by another transaction, not because it’s doing heavy work. A high Lock_time in the slow log is the tell. You can inspect blocking and waiting transactions through Performance Schema’s data lock tables.
• Server resources. If CPU, RAM, or disk I/O is maxed out, every query suffers. The InnoDB buffer pool being too small for your working set is a frequent culprit, since it forces MySQL to hit disk instead of memory.
• The application layer. The N+1 problem, where your code runs one query per row in a loop instead of one query total, makes a fast query catastrophic at scale. That’s an application fix, not a database one. If you’re working in a stack like Laravel, the same principle applies to whichever database you’ve paired it with, and our walkthrough on using MongoDB with Laravel covers how query patterns shift when you change engines.

For WordPress and other PHP applications specifically, the database is usually only one layer of the performance stack. Caching in front of MySQL takes a huge load off it. Setting up an object cache so repeated queries never hit the database twice is one of the highest-return changes you can make, and our guides on installing and configuring Redis cache for WordPress and using a persistent object cache in WordPress walk through exactly that. Pair it with PHP OPcache enabled and the broader steps in our guide on how to speed up WordPress, and you’ve addressed the layers above the database that often get blamed on it. If you’re building search or filtering features, the patterns in how to create a searchable database in WordPress lean directly on the indexing rules above.

Tools Worth Knowing

Frequently Asked Questions

Why is my MySQL query so slow?

Almost always because the execution plan forces MySQL to examine far more rows than it returns. The usual reasons are a missing index, a function wrapped around an indexed column, a join on un-indexed columns, or stale statistics. Run EXPLAIN on the query and look at the type and rows columns to find out which.

How do I find slow queries in MySQL?

Enable the slow query log with long_query_time set to 1 or 2 seconds, let it collect for a while, then aggregate the results with pt-query-digest so the highest-impact queries surface to the top. Performance Schema gives you a live alternative if you don’t want to parse a log file.

What is a good long_query_time value?

Start at 2 seconds on production, drop to 1 once the obvious offenders are cleared, and use 0 in development to capture everything. The default of 10 seconds is too high to catch the queries that actually degrade most applications.

Does indexing speed up MySQL queries?

Yes, for reads, often dramatically, because it lets MySQL jump straight to matching rows instead of scanning the whole table. But indexes slow down writes and use disk space, so add them based on what your EXPLAIN plans show, not on every column you can think of.

Is the MySQL query cache still available?

No. It was deprecated in MySQL 5.7.20 and removed completely in 8.0. Any guide still recommending query_cache_size is out of date. Use an application-level cache (such as Redis) instead to keep repeated queries off the database.

My index exists but MySQL won’t use it. Why?

Three common reasons: a function is wrapped around the indexed column in your WHERE clause, the statistics are stale (run ANALYZE TABLE), or your composite index doesn’t lead with the column you’re filtering on (the leftmost prefix rule). Check the query’s EXPLAIN output against all three.

Final Word

Fixing slow MySQL queries isn’t about memorizing twenty tricks. It’s about a habit: measure which query is slow, read the execution plan to see why, change one thing, and confirm the plan improved. Do that and you’ll fix problems faster than someone armed with a much longer list of tips, because you’ll be treating the cause instead of guessing at symptoms.

Start with the slow query log today, set the threshold somewhere sane, and run EXPLAIN on the worst offender it finds. Nine times out of ten, the fix is one of the six root causes above, sitting right there in the plan, waiting for you to read it.

Sources

• MySQL 8.0 Reference Manual: The Slow Query Log
• MySQL 8.0 Reference Manual: EXPLAIN Output Format
• MySQL 8.0 Reference Manual: Query Cache Removal
• Percona: How to Find and Tune a Slow MySQL Query
• Prisma Data Guide: Optimizing Slow Queries with EXPLAIN