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

1. No Index on WHERE Columns

When a query lacks an index on the columns used in the WHERE clause, MySQL must perform a full table scan. Adding an appropriate index to those columns eliminates the scan.

select * from user_info where name='捡田螺的小男孩公众号';

Correct usage with an index:

alter table user_info add index idx_name (name);

2. Index Not Effective

Even when an index exists, it may not be used in certain scenarios. The most common cases include implicit type conversion, OR conditions, leading wildcards in LIKE, violating the left‑most prefix rule of composite indexes, using functions on indexed columns, arithmetic on indexed columns, and inequality operators.

2.1 Implicit Type Conversion

Creating a table where userId is a VARCHAR but querying with a numeric literal forces MySQL to convert types, causing the index to be ignored.

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  userId varchar(32) NOT NULL,
  age varchar(16) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid (userId) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When the first statement omits single quotes, MySQL performs an implicit conversion to a floating‑point number, so the index is not used.

2.2 OR Conditions

If an OR combines an indexed column with a non‑indexed column, MySQL may fall back to a full table scan.

When both columns have indexes, the optimizer may still choose not to use them; use EXPLAIN to verify and consider splitting the query.

2.3 LIKE Wildcards

Indexes are used only when the pattern does not start with a leading %. For example:

explain select * from user where userId like '%123';

Placing the wildcard at the end preserves index usage:

explain select * from user where userId like '123%';

Use a covering index

Move the % to the right side of the pattern

2.4 Composite Index Left‑Most Rule

A composite index (a,b,c) can be used as (a), (a,b), or (a,b,c). Querying only on name when the index is on (user_id, name) will not use the index.

CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id varchar(32) NOT NULL,
  age varchar(16) NOT NULL,
  name varchar(255) NOT NULL,
  PRIMARY KEY (id),
  KEY idx_userid_name (user_id, name) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.5 Functions on Indexed Columns

Using MySQL functions such as DATE_ADD() on an indexed column disables the index. Move the function to the constant side:

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

2.6 Arithmetic on Indexed Columns

Performing arithmetic (e.g., age+1) on an indexed column prevents index usage; compute the value in application code instead.

2.7 Inequality Operators (!=, <>, NOT IN)

Using != or <> on indexed columns often leads the optimizer to skip the index, especially when the estimated row count is high.

2.8 IS NULL / IS NOT NULL

Indexes work with IS NOT NULL but may be ignored when combined with OR conditions.

2.9 Mismatched Character Sets in Joins

If joined columns have different character sets (e.g., utf8mb4 vs utf8), MySQL cannot use the index and falls back to a full scan.

2.10 Optimizer Chooses Wrong Index

When multiple indexes exist, MySQL may pick a sub‑optimal one. Remedies include using FORCE INDEX, rewriting the query, adjusting business logic, or creating a better index.

Use FORCE INDEX Rewrite the SQL to guide the optimizer

Improve business logic

Create a more suitable index or drop the misleading one

3. Deep Pagination with LIMIT

Using LIMIT offset, count with a large offset forces MySQL to scan offset + count rows, discarding the first offset rows, which is inefficient.

select id, name, balance from account where create_time > '2020-09-19' limit 100000,10;

Two main reasons for slowdown:

The engine scans offset + count rows before discarding the offset.

Each scanned row may require a row‑lookup (back‑table), increasing I/O.

3.2 Optimization Strategies

Bookmark Method : Record the last primary‑key value and query with WHERE id > last_id LIMIT 10, which uses the primary‑key index.

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

Delayed Join Method : First fetch primary‑key ids using the secondary index, then join back to the table.

select acct1.id, acct1.name, acct1.balance FROM account acct1 INNER JOIN (
  SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10
) AS acct2 ON acct1.id = acct2.id;

4. Very Large Single Tables

When a table reaches tens of millions of rows, the B‑Tree height grows, increasing disk I/O for each lookup.

InnoDB pages are 16 KB. With a leaf‑node row size of 1 KB, a height‑2 tree stores about 18 720 rows; a height‑3 tree can hold roughly 21 902 400 rows, which explains why most tables stay at height 1‑3.

Beyond tens of millions, consider sharding (horizontal or vertical) while being aware of transaction, cross‑database, sorting, pagination, and distributed‑ID challenges.

5. Excessive JOINs or Subqueries

Prefer JOIN over subqueries and keep the number of joined tables ≤ 3. Use indexed columns for join conditions. For more tables, fetch data in separate queries and assemble in application code using maps.

MySQL join algorithms:

Index Nested‑Loop Join : Uses the index of the driven table.

Block Nested‑Loop Join : Loads the driven table into join_buffer when no index is available.

6. Too Many Elements in IN Clause

Even with an indexed column, an IN list with thousands of elements degrades performance. Keep the list ≤ 500 items or batch the queries.

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

For programmatic checks:

if (userIds.size() > 500) {
    throw new Exception("单次查询的用户Id不能超过200");
}

7. Dirty Pages (InnoDB)

7.1 What Is a Dirty Page?

A dirty page is a memory page whose contents differ from the on‑disk page. Updates modify the memory page and write a redo log; the actual disk page is flushed later.

7.2 Update Execution Flow

Engine fetches the row (reading from disk if necessary).

Executor updates the column value.

Engine writes the new row to memory and records the change in the redo log (prepare state).

Binary log is generated for replication.

Transaction commit changes the redo log state to committed.

7.4 When Are Dirty Pages Flushed?

Redo‑log buffer is full.

Buffer pool needs to evict a page and the page is dirty.

System is idle (background flush).

Server shutdown.

7.5 Impact on Performance

Flushing a full redo log stalls all writes; evicting many dirty pages adds latency to reads.

8. ORDER BY and Filesort

ORDER BY triggers a filesort when the required order is not satisfied by an index. MySQL may perform row‑id sorting (requiring a back‑table lookup) or full‑field sorting.

Example:

select name, age, city from staff where city='深圳' order by age limit 10;

Optimization: create an index that matches the ORDER BY columns, or increase sort_buffer_size to keep sorting in memory.

9. Lock Waits

If a simple SELECT hangs, it may be blocked by a row or table lock. Use SHOW PROCESSLIST to inspect the waiting state.

10. DELETE … IN Subquery Not Using Index

In MySQL 5.7, a DELETE with an IN (SELECT …) often results in a full scan, while the equivalent SELECT is rewritten by the optimizer into a semi‑join that can use the index.

delete from account where name in (select name from old_account);

EXPLAIN of the SELECT shows a semi‑join transformation:

select `test2`.`account`.`id` AS `id`, `test2`.`account`.`name` AS `name`, ...
from `test2`.`account`
semi join (`test2`.`old_account`)
where (`test2`.`account`.`name` = `test2`.`old_account`.`name`)

Since the optimizer does not apply the same rewrite to DELETE, the statement falls back to a full scan.

11. GROUP BY Using Temporary Tables

GROUP BY creates an in‑memory temporary table, inserts each row, aggregates counts, then sorts the result. If the temporary table exceeds tmp_table_size, it is written to disk, causing slowdown.

explain select city, count(*) as num from staff group by city;

Optimizations:

Add an index on the GROUP BY columns.

Use ORDER BY NULL to suppress sorting.

Prefer in‑memory temporary tables (adjust tmp_table_size).

Consider SQL_BIG_RESULT for large aggregations.

12. Hardware or Network Bottlenecks

Insufficient CPU, memory, I/O bandwidth, or network latency can degrade query performance. Align test‑environment settings with production to avoid misleading benchmark results.

References

MySQL实战45讲 – https://time.geekbang.org/column/article/72775?cid=100020801