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

During a recent incident a Sentry alert reported a timeout for the query

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

. The order_info table has a composite index idx_uid_stat(uid, order_status), so the query should use that index.

Running EXPLAIN on the statement, however, showed type: ALL (full table scan) and key: NULL. The possible_keys list did contain idx_uid_stat, but the optimizer chose a full scan.

To investigate, the optimizer_trace feature (available from MySQL 5.6) was enabled:

SET optimizer_trace="enabled=on";
SELECT * FROM order_info WHERE uid = 5837661 ORDER BY id ASC LIMIT 1;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

The trace first calculated the cost of a full table scan (≈4.45e6) and then the cost of using idx_uid_stat (≈3.07e5). The index cost is far lower, and the chosen_range_access_summary section confirms that the optimizer initially selected the index.

Nevertheless, the final EXPLAIN still reported a primary‑key scan. The trace contains a reconsidering_access_paths_for_index_ordering entry that shows the optimizer re‑evaluated the plan because of the ORDER BY clause. It decided to use the clustered primary key (which is ordered by id) to avoid an explicit sort operation, assuming that scanning the whole table would quickly satisfy the small LIMIT.

The optimizer thinks that scanning the whole table, which is already sorted by id , will find the limited rows fast enough and will avoid a costly sort, even though the index would be much cheaper.

This behavior is a known optimizer bug that has existed since at least 2014 and remains unfixed in MySQL 5.7 and 8.0. It is triggered by queries that use ORDER BY id ASC LIMIT n with a small n. When n is larger, the optimizer correctly chooses the index.

Two practical work‑arounds are recommended:

Force the desired index explicitly:

SELECT * FROM order_info FORCE INDEX(idx_uid_stat) WHERE uid = 5837661 ORDER BY id ASC LIMIT 1;

Trick the optimizer by making the ORDER BY expression non‑trivial, e.g. adding zero:

SELECT * FROM order_info WHERE uid = 5837661 ORDER BY (id+0) ASC LIMIT 1;

This prevents the optimizer from treating the ordering as a simple primary‑key scan and forces it to evaluate the index cost, resulting in the much faster plan (≈28 ms).

Both solutions avoid the bug and ensure the query uses the efficient idx_uid_stat index.