How to Fix Slow MySQL Queries: Real Fixes That Work 2026

Introduction

A query that used to run in a few milliseconds now takes seconds. Your app feels sluggish, your dashboard hangs, and your logs are full of warnings you don’t have time to read one by one.

You’ve probably already tried restarting the server or bumping up memory. That usually buys you a few hours of relief, then the slowdown comes right back, because the real cause was never fixed.

This guide shows you how to fix slow MySQL queries by finding the actual bottleneck first: missing indexes, bad joins, full table scans, or a query that was never built to scale. Each fix below is something you can apply today.

Quick Answer

Quick Answer: Slow MySQL queries usually happen because of missing indexes, full table scans, or queries pulling more data than needed. To fix it: run EXPLAIN on the query, add an index on the columns used in WHERE and JOIN clauses, and limit the columns and rows you select. Most people see results as soon as the right index is added.

Why Is My MySQL Query Taking Too Long to Run

Why It Happens

Most slow queries come down to one thing: MySQL is scanning far more rows than it needs to. This happens when a query filters or joins on a column that has no index, so the database checks every single row instead of jumping straight to the right one.

The Fix

1. Run EXPLAIN in front of your query to see the execution plan.
2. Look at the rows column. A high number here means MySQL is scanning too much data.
3. Check the type column. Values like ALL mean a full table scan is happening.
4. Add an index on the column shown as the problem in the key field (or missing from it).

Result

Once the right index is in place, MySQL jumps straight to the matching rows instead of reading the whole table. Query time often drops from seconds to milliseconds.

Common Mistakes: Adding an index to every column “just in case.” Extra indexes slow down writes and waste storage, so only index columns you actually filter, join, or sort by.

Why Does My MySQL Query Slow Down as the Table Grows

Why It Happens

A query can run fine on a small table and crawl once that table hits a few million rows. This is usually a sign the query was never designed to scale, often because it relies on SELECT *, a non-indexed join, or a filter that has to check row by row.

The Fix

1. Replace SELECT * with only the columns you actually need. Pulling unused columns wastes memory and I/O.
2. Check every JOIN condition has an index on both sides of the join.
3. Add a LIMIT clause if you don’t need every matching row at once.
4. Break large batch updates or deletes into smaller chunks instead of one massive query.

Result

Your query stays fast even as the table grows, because it’s reading less data and doing less work per row.

[RELATED POST: https://ahmadflow.com/]

How Do I Find the MySQL Slow Query Log and Use It

Why It Happens

Without logging, you’re guessing which query is causing the slowdown. The MySQL slow query log records every query that takes longer than a set time threshold, giving you a real list of offenders instead of a hunch.

The Fix

1. Enable it in your MySQL config file:

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log

2. Restart MySQL for the change to apply.
3. Let it run for a day under normal traffic.
4. Review the log with mysqldumpslow to see which queries appear most often and take the longest.

Result

You get a ranked list of the exact queries slowing your app down, instead of trying to fix problems you’re only guessing at.

Pro Tip: Set long_query_time low at first (like 0.5 seconds) to catch smaller issues before they turn into bigger ones.

Why Is My MySQL Index Not Being Used

Why It Happens

You added an index, but the query is still slow. This usually happens because the query doesn’t match the index the way MySQL expects, such as using a function on the indexed column, mismatched data types, or filtering on a column that isn’t the first one in a multi-column index.

The Fix

1. Avoid wrapping the indexed column in a function, like WHERE YEAR(created_at) = 2024. This blocks index use. Filter with a date range instead.
2. Match data types exactly. Comparing a string column to a number can silently skip the index.
3. For multi-column indexes, make sure your WHERE clause uses the columns in the same order the index was created in.
4. Re-run EXPLAIN after each change to confirm the index is now being picked up.

Result

MySQL starts using the index the way it was meant to, and query time drops back down without needing any new indexes.

Why Do My MySQL Queries Slow Down Under Heavy Traffic

Why It Happens

A query can run fast alone but slow to a crawl under real traffic. This is usually a sign of lock contention, too many open connections, or a server buffer pool that’s too small for your working data set.

The Fix

1. Check SHOW PROCESSLIST to see if queries are stuck waiting on locks.
2. Review your connection pool settings. Too many idle connections waste memory that could go to query processing.
3. Increase innodb_buffer_pool_size so more of your frequently used data stays in memory instead of being read from disk each time.
4. Cache repeat, read-heavy queries at the app level instead of hitting the database every time.

Result

Your database handles traffic spikes without queries piling up behind each other, and average response time stays steady even during busy periods.

FAQ

Why is my MySQL query not working correctly under load?

If the query returns correct results but slows down under traffic, it’s likely a locking or connection issue, not a logic problem. Check SHOW PROCESSLIST for locked queries and review your buffer pool size. Fixing resource limits usually solves this faster than rewriting the query itself.

How do I fix a slow MySQL query with a JOIN?

Make sure both columns in the join condition have an index. Run EXPLAIN to confirm MySQL is using an index-based join instead of scanning both tables. If one side is missing an index, add it there first, since that’s the most common cause of slow joins.

What causes a MySQL query to run slow only sometimes?

Inconsistent slowness usually points to caching gaps, table locks from other queries, or a buffer pool that’s too small to hold your working data. Check the slow query log over time instead of testing the query once, so you catch the pattern instead of a single snapshot.

How do I fix slow MySQL queries without adding new indexes?

Reduce the data the query has to touch. Select only needed columns, add a LIMIT, and avoid wrapping filtered columns in functions. These changes often cut query time significantly even before you touch indexing at all.

Why does EXPLAIN show a full table scan even with an index?

This usually means the query doesn’t use the index the way MySQL expects, often due to a function wrapped around the column or a data type mismatch. Rewrite the filter to match the index directly, then confirm with EXPLAIN again.

How often should I check the MySQL slow query log?

Review it weekly if your traffic is steady, or daily during periods of growth or new feature launches. New code often introduces new slow queries, so regular checks catch problems before users start noticing them.

Conclusion

A slow database doesn’t mean you need a bigger server. Most of the time, it means one or two queries are scanning far more data than they should. Start with EXPLAIN on your slowest queries, add indexes where they’re missing, and turn on the slow query log so you catch problems early instead of after they pile up.

If you only do one thing today, run EXPLAIN on the query that’s bothering you most. That single step usually shows you exactly why it’s slow. Once you know how to fix slow MySQL queries at the root cause, the fix itself is often quick, and your app gets its speed back for good.