Why MySQL Picks Full Table Scan Over Index with ORDER BY id LIMIT 1

Problem description

This week a Sentry alert reported a timeout for the following SQL query:

select * from order_info where uid = 5837661 order by id asc limit 1

Running show create table order_info reveals that the table has a composite index idx_uid_stat(uid, order_status) and a primary key on id.

Although the optimizer should use idx_uid_stat, EXPLAIN shows a full table scan (key = PRIMARY).

The optimizer calculates costs for a full table scan and for each possible index. The trace shows a cost of 4.45e6 for the scan and 307103 for the index, yet the final plan still reports a primary‑key scan because of the ORDER BY clause.

The optimizer reconsiders access paths for ordering and prefers scanning the clustered primary key to avoid an explicit sort, assuming that with a small LIMIT the scan will be fast enough.

The short explanation is that the optimizer thinks scanning the whole table (already sorted by id ) will find the limited rows quickly and avoid a sort, so it loses time scanning the table.

This behavior is a known bug in MySQL 5.6‑8.0 when the query contains ORDER BY id ASC LIMIT n. The index idx_uid_stat would execute in about 28 ms, but the optimizer chooses the slower full scan.

Workarounds

Force the desired index explicitly:

select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1

Apply a harmless arithmetic expression to the ORDER BY column to trick the optimizer:

select * from order_info where uid = 5837661 order by (id+0) asc limit 1

The second method is preferred because it does not depend on the index name.

Reference

MySQL optimizer bug discussion: http://4zsw5.cn/L1zEi