Diagnosing and Resolving MySQL Optimizer Mis‑selection of Indexes

1 Background

New teammates started discussing MySQL optimizer internals, prompting the author to share a recent production incident where a slow query triggered an alert. The investigation process is documented for reference.

2 Process and Analysis

2.1 Problem Discovery

In an afternoon in 2020, a flood of slow‑query alerts appeared for a red‑packet page. Log analysis and code review pointed to a specific SQL statement. The MySQL version was 5.7 with the default InnoDB engine.

最终确定的原因是MySQL查询过程中，优化器没有选择最优的索引导致的。

2.2 Problem Localization

The involved table (named zz_test_table ) has two indexes: idx_over_at on the over_at column and the primary key on bonus_id . Although the query is simple, its execution time exceeded three seconds, which is unacceptable for an app‑facing API.

Running EXPLAIN showed that MySQL used idx_over_at and estimated 41,314,647 rows, leading to a full‑table‑scan‑like cost.

Forcing the primary key with an index hint reduced the execution time to 0.103 seconds.

2.3 Problem Extension

Odd Phenomenon 1

Changing LIMIT 1 to LIMIT 3 caused the optimizer to switch to the primary key.

Odd Phenomenon 2

Adding an ORDER BY on the primary key also forced the primary key usage.

Odd Phenomenon 3

Wrapping the query in a sub‑query resulted in the primary key being chosen.

2.4 Problem Analysis

The root cause was two‑fold: (1) a bug in recent code wrote the same value to over_at for many rows, making the index highly selective and causing the optimizer to favor it; (2) the cost‑based optimizer (CBO) in MySQL 5.7 evaluates execution cost based on estimated rows, temporary tables, sorting, and limit size, which explained the observed odd phenomena.

Using optimizer_trace the team compared LIMIT 1 and LIMIT 3 executions. For LIMIT 3 the trace showed both indexes were considered viable, while for LIMIT 1 the optimizer re‑checked index usage and chose idx_over_at because the low limit made the primary key appear more expensive.

3 Solution Ideas

Force the primary key index with an index hint for urgent production fixes, while being aware of the risk if the schema changes.

Run ANALYZE TABLE to refresh statistics, which can improve optimizer decisions; this usually requires privileged access.

Manipulate query shape (e.g., adding ORDER BY , temporary tables, or adjusting LIMIT ) to guide the optimizer.

Design appropriate indexes and write queries that align with the optimizer’s expectations.

4 Summary

The article summarizes a real incident, explains why MySQL’s optimizer chose a sub‑optimal index, and demonstrates how EXPLAIN and optimizer_trace can be used to diagnose and resolve such issues. It also notes that MySQL 8.0 improves optimizer cost estimation, encouraging further exploration.

References: MySQL 5.7 official documentation and the book “深入MySQL实战”.