How to Fix Slow MySQL Queries: A Complete Performance Guide

Slow MySQL queries are almost always caused by missing indexes, undersized buffer pools, or poorly written SQL. To fix them, start by enabling the slow query log to identify problem queries, run EXPLAIN to see how MySQL executes them, then add indexes, rewrite queries, or tune server settings based on what you find. Most performance problems can be resolved without changing your application’s core logic.

Key Takeaways

• Enable the slow query log first — you can’t fix what you can’t see
• EXPLAIN and EXPLAIN ANALYZE reveal exactly why a query is slow
• Missing indexes are the single most common cause of slow MySQL queries
• Set innodb_buffer_pool_size to 60–75% of available RAM on dedicated database servers
• Composite indexes on multi-column WHERE clauses can cut query time from seconds to milliseconds
• Batch large UPDATE and DELETE operations with LIMIT to reduce lock contention
• MySQL 8.4 LTS (released April 2026) fixed several optimizer bugs affecting prepared statements and partitioned tables — upgrading can help
• Avoid SELECT * and functions on indexed columns in WHERE clauses
• For write-heavy workloads, consider whether MySQL 8.4 or an alternative like MariaDB fits better
• Monitoring tools like Percona Monitoring and Management (PMM) make ongoing slow query detection much easier

Why Are MySQL Queries Slow in the First Place?

Slow MySQL queries usually trace back to a handful of root causes. Understanding them before jumping to fixes saves a lot of guesswork.

The most common causes include:

• No index on the column being filtered or joined — MySQL scans every row instead of jumping to the right data
• Buffer pool too small — MySQL reads data from disk instead of RAM, which is orders of magnitude slower
• Poorly written SQL — subqueries that run once per row, SELECT * pulling unnecessary columns, or functions applied to indexed columns that prevent index use
• Table locks and lock contention — large bulk operations blocking other queries
• Outdated statistics — MySQL’s query optimizer makes bad plans when table statistics are stale
• Optimizer bugs — older MySQL versions (including 8.0, which reached end-of-life in April 2026) had known issues with prepared DELETE/UPDATE statements and partitioned table scans that were fixed in 8.4 LTS

Quick rule: If a query suddenly got slow after a table grew, the problem is almost always a missing index. If it was always slow, look at query structure and server config.

How to Find Slow Queries Before You Fix Them

You can’t fix slow MySQL queries without knowing which ones are slow. The slow query log is the right starting point.

Enable the slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries taking more than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Once you have a log, use mysqldumpslow or pt-query-digest (from Percona Toolkit) to group and rank queries by total time consumed. This tells you which queries to fix first — not just which ones are slow, but which ones hurt the most.

Also check:

• SHOW PROCESSLIST for currently running queries
• SHOW STATUS LIKE 'Handler_read%' — high Handler_read_rnd_next values signal full table scans
• Performance Schema tables in MySQL 8.x for detailed execution stats

For ongoing monitoring, Percona Monitoring and Management (PMM) provides dashboards that surface slow query patterns automatically, which is especially useful on production servers where you can’t watch logs manually.

How to Use EXPLAIN to Diagnose Slow MySQL Queries

EXPLAIN shows MySQL’s execution plan — how it intends to run a query. It’s the most direct way to understand why a query is slow and what to do about it.

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

Key columns to read:

Column	What to look for
type	ALL means full table scan (bad). ref, eq_ref, const are good.
key	Shows which index is being used. NULL means no index.
rows	Estimated rows examined. Lower is better.
Extra	Using filesort or Using temporary signals performance issues.

Use EXPLAIN ANALYZE in MySQL 8.x to get actual runtime data, not just estimates. This is especially useful when the optimizer’s estimates are far off from reality.

Common mistake: Running EXPLAIN on a simplified version of the query. Always run it on the exact query that’s slow, with real parameter values if possible.

How to Fix Slow MySQL Queries with Indexes

Adding the right index is the highest-impact fix for most slow MySQL queries. An unindexed scan on 800,000 rows can drop to examining just 450 rows after a proper index is added — that’s the kind of improvement that turns a 5-second query into a sub-second one.

Single-column indexes

Add an index to any column that appears in a WHERE, JOIN ON, or ORDER BY clause and currently shows NULL under key in EXPLAIN:

ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

Composite indexes for multi-column filters

When queries filter on multiple columns, a composite index outperforms separate single-column indexes. The column order matters: put the most selective column first, then match the order of your WHERE clause.

-- Query: WHERE created_at > '2026-01-01' AND status = 'pending'
ALTER TABLE orders ADD INDEX idx_created_status (created_at, status);

A real-world example: adding a composite index on (created_at, status) on an orders table reduced query time from 5 seconds to 0.01 seconds.

What breaks indexes (avoid these)

• WHERE YEAR(created_at) = 2026 — wrapping a column in a function prevents index use
• WHERE status != 'cancelled' — negative conditions often can’t use indexes efficiently
• WHERE name LIKE '%smith' — leading wildcards skip the index

Choose composite indexes if your query filters on two or more columns together consistently. Use single-column indexes if columns are filtered independently across different queries.

How to Fix Slow MySQL Queries Caused by Server Configuration

Even perfect indexes won’t help if MySQL is constantly reading from disk because the buffer pool is too small. Server-level tuning is often the fastest win on under-configured servers.

innodb_buffer_pool_size

This is the most important InnoDB setting. It controls how much data MySQL keeps in RAM. Set it to 60–75% of total RAM on a dedicated database server.

# In my.cnf or my.ini
innodb_buffer_pool_size = 12G  # Example for a 16GB dedicated server

According to Percona’s 2026 MySQL Ecosystem Performance Benchmark Report, buffer pool size adjustments alone caused up to 25x throughput variation in Percona 8.4 — making this the single most impactful configuration change for slow OLTP queries.

Other high-impact settings

Setting	Recommended starting point	Why it matters
innodb_log_file_size	1–2GB	Larger logs reduce checkpoint frequency
query_cache_size	0 (disabled)	Query cache causes contention in MySQL 8.x; it’s removed entirely
innodb_io_capacity	Match your disk IOPS	Helps InnoDB flush pages efficiently on SSDs
max_connections	Set to actual need	Too high wastes RAM; too low causes connection errors

Edge case: On shared servers (where MySQL shares RAM with other services), use 40–50% of RAM instead of 60–75% to avoid swapping, which is far worse than a smaller buffer pool.

How to Fix Slow UPDATE and DELETE Queries

Large UPDATE and DELETE statements are a common source of slow MySQL queries and lock contention. A single statement touching millions of rows can block the entire table for minutes.

The fix: batch your operations.

Instead of:

DELETE FROM logs WHERE created_at < '2025-01-01';

Do this:

DELETE FROM logs WHERE created_at < '2025-01-01' LIMIT 1000;
-- Repeat in a loop until 0 rows affected

Batching with LIMIT 1000 reduces redo log pressure and keeps locks short, so other queries aren’t blocked. The same pattern applies to bulk UPDATE statements.

Also make sure the WHERE clause column is indexed. An unindexed DELETE or UPDATE does a full table scan on every batch, which compounds the problem.

For UPDATE queries specifically:

1. Run EXPLAIN to confirm the WHERE column has an index
2. If the query touches more than 10% of the table, consider whether a full table rebuild (with CREATE TABLE ... SELECT) is faster
3. Avoid UPDATE inside loops in application code — batch at the SQL level instead

Should You Upgrade MySQL or Switch Databases?

Sometimes the fix for slow MySQL queries isn’t a query change — it’s the MySQL version or the database engine itself.

Upgrade to MySQL 8.4 LTS if you’re on 8.0. MySQL 8.0 reached end-of-life in April 2026. MySQL 8.4 fixed optimizer bugs with prepared DELETE/UPDATE statements and Batched Key Access issues on partitioned tables. If your slow queries involve these patterns, upgrading alone may resolve them.

Consider MariaDB if you’re running low-concurrency workloads and want a drop-in replacement. Independent benchmarks show MariaDB running 13–36% faster than MySQL 8.0 in these scenarios, with more consistent scaling over time.

Consider PostgreSQL 17 if write performance is the bottleneck. PostgreSQL 17 outperforms MySQL 8.4 by roughly 4.9x on single-row inserts in benchmark testing and shows significantly lower median latency across standard workloads. For write-heavy applications where MySQL tuning hasn’t helped, migration is worth evaluating.

Choose to stay on MySQL 8.4 if your workload is read-heavy, your team knows MySQL well, and you’re willing to tune indexes and buffer pool settings. Switch databases only after exhausting MySQL-level fixes.

Common Mistakes That Make MySQL Queries Slower

Even experienced developers make these errors. Knowing them helps avoid regressions after fixing slow queries.

• Adding indexes to every column — too many indexes slow down INSERT, UPDATE, and DELETE operations and waste disk space
• Using SELECT * — pulls unnecessary columns, increases I/O, and prevents covering index optimizations
• Ignoring query cache behavior — MySQL 8.x removed the query cache; application-level caching (Redis, Memcached) is the right approach now
• Not updating table statistics — run ANALYZE TABLE orders; after large data changes so the optimizer has accurate row counts
• Fixing the wrong query — always rank by total time impact, not just individual query duration; a query running 10ms but called 100,000 times per hour matters more than a 2-second query called once a day

Conclusion: A Practical Action Plan for Fixing Slow MySQL Queries

Fixing slow MySQL queries doesn’t require a complete overhaul. Most problems respond to a focused, step-by-step approach.

Start here:

1. Enable the slow query log and identify the top 5 queries by total time consumed
2. Run EXPLAIN on each problem query and look for full table scans, missing indexes, and file sorts
3. Add or fix indexes — composite indexes for multi-column filters, single indexes for standalone columns
4. Tune innodb_buffer_pool_size to 60–75% of RAM if you haven’t already
5. Batch large UPDATE/DELETE operations using LIMIT to reduce lock contention
6. Upgrade to MySQL 8.4 LTS if still on 8.0, especially if queries involve prepared statements or partitioned tables
7. Set up ongoing monitoring with PMM or a similar tool so slow queries don’t silently return

The pattern is always the same: measure first, identify the root cause, apply the targeted fix, then verify with EXPLAIN again. Skipping the measurement step is where most optimization efforts go wrong.

FAQ

Q: What is a slow query in MySQL?
A slow query is any query that takes longer than the threshold set in long_query_time (default is 10 seconds, but 1–2 seconds is a more useful threshold for most applications).

Q: How do I enable the MySQL slow query log?
Run SET GLOBAL slow_query_log = 'ON'; and SET GLOBAL long_query_time = 1; in a MySQL session, or add these settings to your my.cnf file for persistence across restarts.

Q: What does EXPLAIN show in MySQL?
EXPLAIN shows the query execution plan: which indexes are used, how many rows are examined, and whether MySQL uses temporary tables or file sorts. It’s the primary tool for diagnosing slow queries.

Q: How much RAM should innodb_buffer_pool_size use?
Set it to 60–75% of total RAM on a dedicated MySQL server. On a shared server, use 40–50% to avoid memory pressure on other processes.

Q: Can adding indexes make MySQL slower?
Yes. Too many indexes slow down write operations because MySQL must update every index on each INSERT, UPDATE, or DELETE. Only add indexes that are actually used by your queries.

Q: What’s the fastest way to fix a slow full table scan?
Add an index to the column(s) in the WHERE clause. Confirm the scan is happening by running EXPLAIN and checking if type shows ALL.

Q: Is MySQL 8.0 still safe to use in 2026?
MySQL 8.0 reached end-of-life in April 2026, meaning it no longer receives security patches or bug fixes. Migrating to MySQL 8.4 LTS is strongly recommended.

Q: What’s the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the optimizer’s estimated plan. EXPLAIN ANALYZE actually runs the query and shows real execution times and row counts, which is more accurate for diagnosing problems.

Q: Should I use a composite index or multiple single indexes?
Use a composite index when your queries consistently filter on the same combination of columns. Use separate single indexes when columns are filtered independently across different queries.

Q: How do I fix lock contention from large DELETE or UPDATE queries?
Batch the operation using LIMIT (e.g., DELETE ... LIMIT 1000) in a loop. This keeps individual transactions small and releases locks between batches.