Why a Simple ORDER BY Caused a 20‑Second MySQL Slow Query and How to Fix It

Recently the order‑fulfillment system suffered a failure that caused order back‑log due to a slow MySQL query. The root cause was a slow query caused by an inefficient index choice.

<code>SELECT id, order_id, flag, order_sn, old_order_sn, wms_flag, warehouse, ...
FROM xxx_order_xxx
WHERE wms_flag = 1 AND warehouse IN ('ABC','XYZ') AND flag IN (90,3)
ORDER BY id ASC
LIMIT 350</code>

The table has several indexes:

<code>PRIMARY KEY (id),
KEY idx_flag (wms_flag, flag),
KEY warehouse (warehouse),
KEY warehouse_2 (warehouse, extra2, flag)
...</code>

To decide which index the query should use, the data distribution was examined.

Count of rows where wms_flag = 1 : 127,132

Count of rows for small warehouses: 105,180

Count of rows for large warehouses:

<code>warehouse | count
-------------------
大仓1      | 105
大仓2      | 4,943,267
</code>

Two scenarios arise:

Scenario 1: count1 > count2 – use the warehouse or warehouse_2 index (e.g., small‑warehouse case).

Scenario 2: count1 < count2 – use idx_flag or the primary key (e.g., large‑warehouse case).

In production the situation matched Scenario 2, and the slow query appeared.

The execution plan in the test environment for Scenario 2 showed that the ORDER BY id ASC LIMIT 350 caused MySQL to abandon the idx_flag index and use the primary‑key index because the selectivity of idx_flag was low. MySQL scanned the primary‑key index from the beginning, encountering many rows with wms_flag = 0 (about 29,091,485 rows) before finding the 350 matching rows, resulting in a 20‑second execution.

Removing the ORDER BY or the LIMIT changed the plan: without ORDER BY , MySQL used idx_flag and scanned only 13,903 rows; without LIMIT , it scanned 149,152 rows to find all matches.

To avoid the primary‑key scan, a small trick was applied: adding +0 to the ORDER BY id expression forces MySQL to use the idx_flag index. The revised query:

<code>SELECT id, order_id, flag, order_sn, old_order_sn, wms_flag, warehouse, ...
FROM xxx_order_xxx
WHERE wms_flag = 1 AND warehouse IN ('ABC','XYZ') AND flag IN (90,3)
ORDER BY id + 0 ASC
LIMIT 350</code>

This change reduced execution time from 20 seconds to 0.6 seconds. Ultimately, because the business does not require sorting, the final fix removed the ORDER BY id ASC clause entirely.

Images illustrating the execution plans: