Why MySQL Picks Full Table Scan Over Index with ORDER BY … LIMIT 1 (and How to Fix It)

Recently a Sentry alert reported a timeout for the SQL 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) 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;

Running EXPLAIN shows possible_keys includes idx_uid_stat but the chosen key is NULL, meaning a full table scan is used. The screenshot below illustrates this result:

MySQL selects the execution plan based on cost. Using optimizer_trace we can see the cost comparison:

{
  "rows_estimation": [
    {
      "table": "`rebate_order_info`",
      "range_analysis": {
        "table_scan": {"rows": 21155996, "cost": 4.45e6}
      },
      "analyzing_range_alternatives": {
        "range_scan_alternatives": [
          {
            "index": "idx_uid_stat",
            "rows": 255918,
            "cost": 307103,
            "chosen": true
          }
        ]
      }
    }
  ]
}

The trace shows the index idx_uid_stat has a much lower cost (307 103) than a full scan (4.45 million). However, because the query orders by id ASC, the optimizer re‑considers access paths for ordering and finally chooses the primary key index, which results in a full table scan.

This behavior is a known optimizer bug: when ORDER BY id ASC LIMIT n with a small n, MySQL prefers scanning the clustered primary key to avoid a separate sort operation, even though using the secondary index would be faster (e.g., 28 ms).

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 harmless expression to the ORDER BY clause:

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

The second method is generally preferred because it avoids hard‑coding the index name and still leads the optimizer to choose the efficient index.