Why MySQL Optimizer Chooses a Full Table Scan for ORDER BY id ASC LIMIT 1 and How to Force the Correct Index

I received a Sentry alert indicating that a simple SELECT query timed out, so I investigated the statement:

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

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

CREATE TABLE `order_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned,
  `order_status` tinyint(3) DEFAULT NULL,
  ...
  PRIMARY KEY (`id`),
  KEY `idx_uid_stat` (`uid`,`order_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Although EXPLAIN shows possible_keys: idx_uid_stat , the chosen key is NULL , meaning MySQL performed a full table scan:

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

MySQL selects the execution plan based on estimated costs. The full‑table‑scan cost was about 4.45e6 rows, while using idx_uid_stat would cost roughly 3.07e5 rows, clearly cheaper. Yet the final plan still used the primary key because of a second optimization step triggered by the ORDER BY id ASC clause.

Enabling optimizer_trace shows the detailed decision process:

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 reports the cost comparison and then a reconsidering_access_paths_for_index_ordering block, where the optimizer prefers the primary key index to avoid an explicit sort, assuming that scanning the clustered primary key (already ordered by id ) will satisfy the LIMIT quickly.

{
  "reconsidering_access_paths_for_index_ordering": {
    "clause": "ORDER BY",
    "index_order_summary": {
      "table": "`rebate_order_info`",
      "index_provides_order": true,
      "order_direction": "asc",
      "index": "PRIMARY",
      "plan_changed": true,
      "access_type": "index_scan"
    }
  }
}

This behavior is considered a bug because the index idx_uid_stat would return the row in about 28 ms, while the full scan takes much longer. The issue has been reported since 2014 and persists in MySQL 5.7 and 8.0.

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 adding a no‑op arithmetic expression to the ORDER BY clause, which prevents the optimizer from treating the scan as already ordered: select * from order_info where uid = 5837661 order by (id+0) asc limit 1

The second method is preferred because it keeps the query portable without relying on index hints.