Why Your MySQL Queries Run Slow and How to Fix Common Index Issues

Introduction

MySQL performance questions often appear in interviews, such as "What happens after you press Enter on a URL?" This article explores the many core reasons a SQL statement can run slowly.

1. Classification of slow queries

We distinguish between two situations: occasional slowness and consistently slow execution.

2. Occasional slow queries

When a query is usually fast but sometimes lags, the problem is usually not the SQL itself but external factors.

1. Dirty‑page flushing

Updates are first written to the redo log in memory; later they are flushed to disk. If the redo log fills because the database is busy, it must pause other operations to write data to disk, causing the query to become slow.

2. Lock waiting

If the target table or a row is locked by another session, the query must wait for the lock to be released. You can inspect lock status with show processlist.

3. Consistently slow queries

If the same SQL statement is always slow, examine the statement itself and the table's indexing.

Assume a table t with a primary key id and two ordinary columns c and d:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

1. Missing or unused index

If column c has no index, a query like

select * from t where 100 < c and c < 100000;

must perform a full table scan.

Even when an index exists, using arithmetic or functions on the indexed column prevents the optimizer from using it:

select * from t where c - 1 = 1000;

The correct form that can use the index is:

select * from t where c = 1000 + 1;

Similarly, applying a function such as pow(c,2) disables index usage.

2. Index not chosen by optimizer

The optimizer predicts how many rows each access path will read. If it estimates that using an index will read many rows, it may prefer a full table scan.

Cardinality (index selectivity) is estimated via sampling; inaccurate samples can cause the optimizer to misjudge and skip a good index.

3. Forcing index usage

You can force the optimizer to use a specific index:

select * from t force index(a) where c < 100 and c < 100000;

Refresh statistics after structural changes:

ANALYZE TABLE t;

Conclusion

Two main scenarios cause slowness: (1) occasional delays due to dirty‑page flushing or lock contention, and (2) persistent delays caused by missing/unused indexes or the optimizer choosing the wrong index. Understanding these factors and using tools like show processlist, proper indexing, and ANALYZE TABLE can dramatically improve query performance.