12 Common MySQL Slow‑Query Causes and How to Fix Them

Introduction

Hello, I’m SanYou. In daily development we often encounter MySQL slow queries. Below are 12 common causes and their solutions.

1. No Index on WHERE Columns

When a query lacks an index, MySQL performs a full‑table scan. Add an index on the columns used in the where clause to avoid this.

select * from user_info where name='example';

// Add index
alter table user_info add index idx_name (name);

2. Index Not Effective

2.1 Implicit Type Conversion

If a column is a string (e.g., userId varchar(32)) but the query supplies a number, MySQL converts types and the index becomes unusable. select * from user where userId=123; Wrap the number in quotes to keep the index.

2.2 OR Conditions

Using or with columns that lack indexes forces a full‑table scan. Even if one column has an index, the optimizer may ignore it.

2.3 LIKE Wildcards

Only patterns that start with a literal prefix (e.g., like 'abc%') can use an index. Patterns starting with % cause index loss.

2.4 Left‑most Prefix Rule

For a composite index (a,b,c), the query must reference the leftmost columns in order; otherwise the index is ignored.

2.5 Built‑in Functions

Applying functions such as DATE_ADD() to an indexed column disables the index. Move the function to the constant side.

select * from user where login_time = DATE_ADD('2022-05-22 00:00:00', INTERVAL -1 DAY);

2.6 Column Arithmetic

Expressions like age+1 prevent index usage; compute the value in application code instead.

2.7 != or <> Conditions

Using != or <> may cause the optimizer to skip the index, especially when many rows match.

2.8 IS NULL / IS NOT NULL

These predicates can use an index, but combining them with or often disables it.

2.9 Charset Mismatch in Joins

If joined columns have different character sets (e.g., utf8mb4 vs utf8), the optimizer may fall back to a full scan.

2.10 Wrong Index Chosen

When multiple indexes exist, MySQL may pick a sub‑optimal one. Use FORCE INDEX, rewrite the query, or redesign indexes.

3. Deep Pagination (LIMIT Offset)

Large offsets cause MySQL to scan and discard many rows, increasing I/O and back‑table lookups.

3.1 Why It Slows Down

Scanning offset + n rows and then discarding the first offset rows is costly.

3.2 Optimization

Use a bookmark (store the last id) and query where id > last_id limit n, or apply the delayed‑join technique to fetch primary keys first and then join.

select id, name, balance from account where id > 100000 limit 10;

select a.id, a.name, a.balance from account a inner join (select id from account where create_time > '2020-09-19' limit 100000,10) b on a.id = b.id;

4. Very Large Single Tables

When a table reaches tens of millions of rows, the B+‑tree height grows, increasing disk I/O.

A 2‑level B+‑tree can store ~18k rows; a 3‑level tree can store ~22 million rows. Beyond that, performance degrades.

Solution: consider sharding (horizontal partitioning) or archiving old data.

5. Excessive Joins or Subqueries

More than three tables in a join often leads to complex execution plans. Prefer joins over subqueries and keep the join count low. Use EXPLAIN to verify index usage.

6. Too Many IN Elements

IN lists larger than ~500 items should be batched; otherwise the optimizer may generate a massive temporary set and cause timeouts.

select user_id, name from user where user_id in (1,2,3,...500);

7. Dirty Pages and Flush

A dirty page is a memory page whose content differs from the on‑disk page. Updates write to the redo log and later flush dirty pages to disk. Flush occurs when the redo log is full, memory pressure forces eviction, the system is idle, or during shutdown.

8. ORDER BY File‑Sort

When the result set cannot be retrieved in the required order from an index, MySQL performs a file‑sort, which may spill to disk if the data exceeds sort_buffer_size. Optimizations include creating covering indexes, adjusting max_length_for_sort_data and sort_buffer_size, or rewriting the query to use an ordered index.

9. Lock Waits

Queries may block when rows or tables are locked by other sessions. Use SHOW PROCESSLIST to diagnose.

10. DELETE … IN Subquery Not Using Index

MySQL can transform SELECT … IN (subquery) into a semi‑join that uses indexes, but it does not apply the same rewrite for DELETE … IN (subquery), leading to full scans.

11. GROUP BY Using Temporary Table

GROUP BY creates an in‑memory temporary table, inserts each distinct grouping key, and then sorts it. Large groups may overflow to disk, causing Using temporary and Using filesort in the execution plan. Adding an index on the grouping column, using ORDER BY NULL, or forcing SQL_BIG_RESULT can mitigate the cost.

12. Hardware or Network Limitations

Insufficient CPU, memory, I/O bandwidth, or network latency can degrade query performance. Ensure production and test environments have comparable configurations.

Conclusion

Aligning MySQL configuration, proper indexing, query rewriting, and resource planning are essential to avoid slow queries.