Why ORDER BY with LIMIT Returns Unexpected Row Order in MySQL

Phenomenon and Problem

After sorting with ORDER BY and then using LIMIT to fetch the top rows, the returned order sometimes differs from the expected one.

The issue occurs because when the ORDER BY column has identical values, MySQL may return those rows in an arbitrary order.

To guarantee a stable order, add an extra sorting field (e.g., id) so that the combined order reduces the chance of duplicates.

After fixing the query to ORDER BY status, id, the problem is resolved, but it is still worthwhile to see what the official documentation says.

LIMIT Query Optimization

If you only need a specific number of rows, use the LIMIT clause instead of retrieving the whole result set and discarding the excess.

MySQL sometimes optimizes a query that contains LIMIT and no HAVING clause:

MySQL usually prefers a full table scan, but when LIMIT requests only a few rows, it may use an index.

When LIMIT is combined with ORDER BY, MySQL stops sorting after it has found the first row_count rows, which is fast if an index can provide the order.

If LIMIT is used with DISTINCT, MySQL stops once it has found row_count distinct rows. LIMIT 0 quickly returns an empty set, useful for testing query validity.

If the server uses a temporary table, LIMIT row_count determines the required space.

If ORDER BY does not use an index but is followed by LIMIT, the optimizer may avoid a merge file and perform an in‑memory filesort.

If the ORDER BY column has many rows with the same value, MySQL can return those rows in any order; the order is nondeterministic without a unique column.

The presence of LIMIT influences the execution plan, so an ORDER BY query with or without LIMIT may return rows in different orders.

Summary

If you only need a few rows from a result set, use LIMIT to avoid fetching unnecessary data.

For ORDER BY queries, the presence or absence of LIMIT can change the row order.

When LIMIT and ORDER BY are used together, sorting stops after the first row_count rows are found, and the result is returned immediately.

If the ORDER BY column contains duplicate values, MySQL may return those rows in any order; adding an additional column to the ORDER BY clause makes the order deterministic.

Reference documents:

https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

https://dev.mysql.com/doc/refman/5.7/en/

https://dev.mysql.com/doc/