Why Moving MySQL LIMIT Outside a Subquery Breaks ORDER BY and How to Fix It

When writing a paginated query that orders records by creation time in descending order, the original SQL looked like this:

select
    record.a,
    y.c
from
    (
        select
            a, b
        from
            x
        order by timestamp desc
        limit 0,10
    ) record
left join y
on record.b = y.d;

New requirements forced additional WHERE conditions, so the LIMIT 0,10 was moved outside the subquery while the ORDER BY remained inside. The expected result—still descending by timestamp after the join—was actually ascending.

Rolling back other changes and only moving the LIMIT to the outer query revealed that the ordering issue persisted, confirming the problem was not caused by the added logic.

Removing the LIMIT altogether produced an unordered timestamp column, suggesting that the presence of LIMIT influences the execution order.

Further investigation showed that the final result seemed to be sorted by the joined table y 's auto‑increment ID or created_at column, which are indexed fields. This indicates that MySQL does not guarantee the outer result follows the inner subquery’s ORDER BY when the subquery is hidden behind a join.

Another observation: without LIMIT, the output order does not follow the left‑joined main table’s sequence; instead, it appears to follow the order of the joined table’s indexed column, hinting at optimizer re‑ordering similar to Java’s instruction reordering.

According to the MySQL manual, the ORDER BY clause can be optimized away under certain conditions, and the LIMIT clause can stop the query early once the required number of rows is retrieved.

The screenshots above illustrate that LIMIT enables early termination of the query, which can affect the order in which rows are produced.

If LIMIT is omitted, MySQL may reorder the join processing for optimization, which can change the output order.

The simplest and most reliable solution is to move both ORDER BY and LIMIT to the outermost query; this does not degrade performance as long as appropriate indexes exist.