How to Fix Slow MySQL Queries Without Guessing

Slow MySQL queries rarely announce themselves with a flashing error. They show up as timeouts in production, dashboards that crawl, and engineers staring at EXPLAIN output at midnight. The good news: most slow queries follow predictable patterns, and you can fix them systematically instead of throwing indexes at the wall.

Start with evidence, not assumptions

Before you rewrite SQL or buy a bigger instance, capture what is actually slow.

1. Enable the slow query log (even temporarily in staging). Set long_query_time to something realistic—often 0.5–2 seconds depending on your SLA.
2. Use Performance Schema or tools like pt-query-digest to rank queries by total time, not just average latency. A query that runs 50 ms but executes a million times can hurt more than one 5-second monster.
3. Record context: row counts, table sizes, replication lag, and whether the slowness is new or gradual.

If you skip this step, you will optimize the wrong query and wonder why nothing improved.

Read EXPLAIN like a checklist

EXPLAIN (or EXPLAIN ANALYZE on MySQL 8.0.18+) tells you how MySQL plans to run your query. Focus on these columns:

A query can be "slow" because it examines millions of rows to return ten. Your goal is to reduce rows examined, not just rows returned.

Example: accidental full scan

SELECT id, email, created_at
FROM users
WHERE LOWER(email) = 'alice@example.com';

If email is indexed but you wrap it in LOWER(), MySQL often cannot use the index. Fixes include storing a normalized column, using a functional index (MySQL 8.0+), or ensuring consistent casing at write time.

Run:

EXPLAIN ANALYZE
SELECT id, email, created_at
FROM users
WHERE email = 'alice@example.com';

Compare the rows examined before and after.

Index design that matches how you filter

Indexes are not free—they slow writes and consume disk—but the right ones are the highest-leverage fix for slow reads.

Composite index column order matters. Put equality filters first, then range filters, then columns needed for sorting if you want an index-only plan.

Bad pattern:

-- Index on (status, created_at) but query filters created_at range first
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND status = 'shipped';

Better: align the index with the query shape, e.g. (status, created_at) if status is selective and always present.

Covering indexes include all columns the query needs so MySQL can satisfy it from the index alone:

CREATE INDEX idx_orders_status_created_covering
ON orders (status, created_at, id, customer_id);

Use covering indexes selectively—over-indexing every table bloats storage and hurts inserts.

Rewrite queries before you scale hardware

Some slowness is logic, not infrastructure.

Pagination without OFFSET pain

LIMIT 100000, 20 forces MySQL to scan and discard 100,000 rows. Keyset pagination is usually faster:

SELECT id, title, created_at
FROM posts
WHERE created_at < '2026-06-01'
ORDER BY created_at DESC
LIMIT 20;

Pass the last seen created_at (and id as a tiebreaker) on each page.

JOIN order and selective filters

Push the most selective filters early. A JOIN that multiplies rows before filtering creates a hidden explosion. Sometimes splitting one big query into two smaller ones—with an application-side merge—reduces total work.

Avoid SELECT *

Fetching wide rows increases I/O and memory. Select only columns you need, especially on tables with TEXT/JSON blobs.

Schema and data issues that look like query problems

• Wrong column types: storing dates as VARCHAR prevents sensible range scans.
• Missing foreign keys (or inconsistent types between FK columns) can confuse optimizers and ORMs.
• Stale statistics: run ANALYZE TABLE after large bulk loads or migrations.
• Fragmentation: occasional OPTIMIZE TABLE on heavily deleted tables can help on some engines/setups—measure first.

Caching and read replicas (after you fix the query)

Caching query results in Redis or application memory helps read-heavy, eventually-consistent workloads. Read replicas offload reporting queries—but replicas still execute the same SQL. A slow query on a replica can lag replication and serve stale data longer.

Fix the query first; then add caching where the access pattern is truly repetitive.

Spot checks that reveal hidden regressions

Two queries that look identical to application code can perform wildly differently after a migration.

Implicit type conversion is a classic trap. Comparing a VARCHAR column to a numeric literal without quotes can force a full scan:

-- Bad if order_id is VARCHAR
SELECT * FROM orders WHERE order_id = 12345;

-- Better
SELECT * FROM orders WHERE order_id = '12345';

OR conditions across columns often defeat composite indexes. Rewriting to UNION ALL of two indexed lookups can be faster when each branch is selective:

SELECT id, status FROM tickets WHERE customer_id = 42 AND status = 'open'
UNION ALL
SELECT id, status FROM tickets WHERE assignee_id = 7 AND status = 'open';

Subqueries in SELECT lists execute per row. Join or pre-aggregate instead:

-- Often slow on large tables
SELECT o.id, (SELECT COUNT(*) FROM line_items li WHERE li.order_id = o.id) AS items
FROM orders o;

-- Usually faster
SELECT o.id, COUNT(li.id) AS items
FROM orders o
LEFT JOIN line_items li ON li.order_id = o.id
GROUP BY o.id;

Run these comparisons in staging with production-like row counts—a query that flies on 10k rows may collapse at 10 million.

Monitoring so slow queries do not return quietly

Set alerts on:

• p95/p99 query latency from Performance Schema or your APM's database span.
• Replication lag on read replicas (often the first symptom of heavy reporting SQL).
• InnoDB buffer pool hit rate—sustained drops can mean scans are blowing cache.

Schedule a weekly review of the slow log's top ten by total time. When a new deploy coincides with a latency spike, diff the ORM SQL or migration scripts first—schema changes without matching index updates are a frequent culprit.

A practical workflow you can repeat

1. Identify top offenders by total time, not gut feel.
2. Run EXPLAIN ANALYZE on each.
3. Fix sargability (functions on indexed columns, implicit casts).
4. Add or adjust indexes to match filter + sort patterns.
5. Rewrite pagination, JOINs, and SELECT lists where needed.
6. Re-measure under realistic load.
7. Document the change so the next engineer does not "optimize" it back.

FAQ

Should I always add an index when a query is slow?
No. Indexes help selective lookups; they hurt write-heavy tables and can be ignored if the optimizer chooses a full scan anyway (e.g., when most rows match).

Is ORM-generated SQL the problem?
Sometimes. ORMs can emit N+1 queries or hide missing eager loads. Enable SQL logging in staging and trace the exact statements.

When is partitioning worth it?
When tables are huge and queries consistently hit a partition key (time ranges, tenant id). It is not a substitute for indexes.

Does upgrading MySQL version help?
Newer versions improve the optimizer and add features like invisible indexes and better JSON support—but they do not replace sound schema design.

How do I test an index before committing in production?
MySQL 8 supports invisible indexes: create the index, mark it invisible, verify plans with EXPLAIN, then make it visible—or drop it if there is no win.

What if only one environment is slow?
Compare row counts, buffer pool size, and whether staging lacks representative data. Also check connection pool settings—waiting for a connection feels like a slow query.

Slow MySQL queries are fixable when you treat them like debugging: measure, hypothesize, change one thing, measure again. The teams that struggle are the ones that skip EXPLAIN and buy bigger boxes instead.

Comments

Loading comments…