Why Is MySQL Query Slow? Common Causes and How to Diagnose Them

Intermittent slow queries

When a statement is usually fast but occasionally stalls, the delay is often caused by background InnoDB activity. Updates are first written to the redo log in memory; when the redo log becomes full the engine must pause other work to flush the log to disk, which blocks concurrent queries. Another common source is lock contention: if another session holds a table or row lock, the waiting query is blocked until the lock is released. The current sessions and lock waits can be inspected with SHOW PROCESSLIST.

Consistently slow queries

If the same SQL statement is slow on every execution, the root cause is usually related to index usage. Typical reasons include:

No index on the filtered column – the optimizer resorts to a full‑table scan. Example: SELECT * FROM t WHERE 100 < c AND c < 100000; Expression on an indexed column – applying arithmetic or functions to the indexed column prevents the optimizer from using the index. Example (index on c exists but not used):

SELECT * FROM t WHERE c - 1 = 1000;  -- does NOT use the index

Correct form: SELECT * FROM t WHERE c = 1000 + 1; Function call on the column – e.g. SELECT * FROM t WHERE POW(c,2) = 1000; forces a full scan.

Optimizer chooses the wrong index – even when an appropriate index exists, inaccurate statistics (cardinality) may lead the optimizer to estimate that scanning the index plus the primary key will read more rows than a full scan, so it prefers the full scan.

Index cardinality and statistics

The optimizer estimates row counts using the index cardinality, i.e., the number of distinct values. MySQL samples a subset of index entries instead of scanning the whole index; sampling errors can underestimate cardinality, causing the optimizer to skip the index. Statistics can be refreshed with ANALYZE TABLE.

Example table definition

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

Diagnosing and fixing index issues

SHOW INDEX FROM t;

– displays existing indexes and their cardinality. ANALYZE TABLE t; – recomputes index statistics. EXPLAIN SELECT * FROM t WHERE c < 100 AND c < 100000; – shows the execution plan and whether an index is used. SELECT * FROM t FORCE INDEX(a) WHERE c < 100 AND c < 100000; – forces the optimizer to use a specific index. SHOW PROCESSLIST; – detects lock waits that may cause intermittent latency.

Remediation steps

Create missing indexes on columns used in range predicates.

Avoid applying arithmetic or functions to indexed columns; rewrite predicates so the column appears alone on the left side of the comparison.

Keep index statistics up‑to‑date with ANALYZE TABLE after large data modifications.

If the optimizer still chooses a full scan, consider using FORCE INDEX or adjusting optimizer hints.

Monitor redo‑log usage and lock contention; mitigate by tuning InnoDB log size or reducing long‑running transactions.

By ensuring appropriate indexes, writing predicates that can leverage those indexes, and maintaining accurate statistics, most slow‑query problems—both intermittent and persistent—can be resolved.