Why MySQL ORDER BY + LIMIT Returns Unexpected Order and How to Ensure Consistency

Problem Description

When applying ORDER BY together with LIMIT to fetch the top rows, the returned order may differ from expectations. MySQL does not guarantee a deterministic order when the ordered column contains duplicate values.

Investigation

Below are screenshots showing the discrepancy between queries with and without LIMIT:

Solution

Adding a secondary unique column (e.g., id) to the ORDER BY clause makes the ordering deterministic: ORDER BY status, id The revised query produces a stable order, as illustrated below:

Official MySQL Documentation on LIMIT Optimization

Key points extracted from the MySQL manual:

MySQL usually prefers a full‑table scan, but when LIMIT restricts the result to a few rows it may choose an index.

If LIMIT row_count is combined with ORDER BY, MySQL stops sorting after it has found the first row_count rows, which can lead to a different order than a query without LIMIT.

When LIMIT row_count is used with DISTINCT, MySQL stops after finding the required number of unique rows. LIMIT 0 returns an empty set instantly, useful for validating a query.

If a temporary table is created, LIMIT determines the amount of space needed.

When ORDER BY cannot use an index and LIMIT is present, the optimizer may avoid a merge file and perform an in‑memory filesort.

Why Order Can Vary

If the ORDER BY column contains duplicate values, MySQL is free to return those rows in any order, and the exact ordering may change between executions depending on the chosen execution plan.

Examples of LIMIT Impact

These screenshots demonstrate how adding LIMIT can affect the order of rows in different categories:

Best Practices

Use LIMIT when you only need a specific number of rows to avoid fetching the entire result set.

Be aware that ORDER BY with or without LIMIT may produce different row orders.

When LIMIT row_count is used with ORDER BY, MySQL stops sorting after the first row_count rows are found and returns them immediately.

If the ordered column has duplicate values, the order of those rows is nondeterministic; ensure uniqueness or add extra columns.

Include additional columns in the ORDER BY clause (e.g., a primary key) to guarantee a deterministic order.