Why MySQL Picks the Wrong Index for ORDER BY LIMIT Queries and How to Fix It

1. Introduction

Increasing data volume triggered frequent MySQL CPU‑100% alerts. Adding obvious indexes did not resolve the issue; the optimizer still chose an inefficient index when executing SELECT … ORDER BY id ASC LIMIT n even though the WHERE clause covered a composite index.

2. Environment

The table holds roughly 21 million rows. Monitoring graphs (Figures 1‑2) show CPU saturation from midnight onward.

3. Problem Discovery

A simple query:

SELECT * FROM test WHERE is_delete = 0 AND business_day = '2021-12-20' AND full_ps_code LIKE 'xxx%' AND id > 2100 ORDER BY id LIMIT 500;

averaged over 90 seconds per execution and ran many times (Figure 3). Table statistics revealed ~21 million rows.

4. Index Analysis

The table has a primary key on id and a composite index (business_day, full_ps_code). An EXPLAIN showed possible_keys includes the composite index, but key is the primary key, scanning ~17 million rows (Figure 5).

5. Experiments to Resolve the Issue

Add a new composite index ADD INDEX idx_test(business_day, full_ps_code, id). EXPLAIN still chose the primary key.

Force the new index with FORCE INDEX (idx_test). Execution time dropped to ~0.49 s (Figure 7).

Remove id from the WHERE clause . The query remained slow (≈96 s).

Remove ORDER BY . The composite index was used, execution time fell to 0.062 s, but results were unordered (Figures 8‑9).

Extend ORDER BY to include another indexed column ( ORDER BY id, full_ps_code). The composite index was used, query ran in 0.034 s, at the cost of an extra filesort (Figure 10).

Increase LIMIT to 1000 . The composite index was chosen automatically, execution time 0.316 s. Tests showed that a limit > 695 triggers the composite index for this data set.

All three successful methods (force index, extended ORDER BY, larger LIMIT) were applied in production, and CPU usage returned to normal.

6. Deep Dive into the Optimizer

The MySQL optimizer selects execution plans based on a cost model consisting of server_cost (CPU) and engine_cost (I/O). Important default parameters include:

row_evaluate_cost = 0.2

key_compare_cost = 0.1

io_block_read_cost = 1.0

Cost calculations for a full table scan ( table_scan_cost) and an index scan ( index_scan_cost) are shown in the extracted source snippets.

double scan_time = cost_model->row_evaluate_cost((double)records) + 1;

cost->add_cpu(cost_model->row_evaluate_cost((double)total_rows) + 0.01);

Using optimizer_trace reveals that the ORDER BY id clause forces the optimizer to reconsider access paths, marking the composite index as not_applicable and selecting the primary key instead (Figures 11‑13).

The root cause is the optimizer’s attempt to avoid a costly sort by preferring a full‑table scan when LIMIT is small. This behavior is documented as a bug (MySQL 5.7, bug 97001) and can be disabled with SET optimizer_switch='prefer_ordering_index=off' in MySQL 5.7.33+.

7. Recommendations

Use FORCE INDEX to explicitly choose the optimal index.

Add an extra column to ORDER BY so the composite index becomes usable.

Increase LIMIT when feasible (not recommended for large data sets).

Apply ORDER BY (id+0) as a trick to mislead the optimizer.

Upgrade to MySQL 5.7.33 or later and disable prefer_ordering_index.

For deeper analysis, run EXPLAIN FORMAT=JSON or enable optimizer_trace as shown.