Understanding MySQL ORDER BY with LIMIT Pagination Issues and Their Fixes

Problem Description

When querying a user table with ORDER BY create_time LIMIT pageNo,2 , the first and fourth pages returned identical rows because MySQL's limit optimization stops sorting after it finds the required number of rows, and duplicate create_time values lead to nondeterministic ordering.

Observed Behavior

Running the pagination query on an 8‑row table (4 pages) showed that page 1 and page 4 returned the same data, contradicting the expectation that pagination should be stable.

MySQL Limit Optimization

MySQL may apply an optimization where, if LIMIT is used together with ORDER BY , it finds the required number of rows for the limit and returns them immediately without sorting the entire result set. If the ordering column has many duplicate values, the relative order of those rows is undefined, causing pagination inconsistencies.

Root Cause

The create_time column contains duplicate timestamps; MySQL therefore returns rows with the same timestamp in an arbitrary order, which changes between pages.

Solution

To guarantee a stable order, add a secondary unique column (e.g., id ) to the ORDER BY clause. The corrected query is:

SELECT * FROM user ORDER BY create_time, id LIMIT 6,2;

This ensures that even when create_time values are identical, the id provides a deterministic tie‑breaker, making pagination accurate.

Conclusion

Always include a unique secondary sort key when using ORDER BY with LIMIT for pagination to avoid nondeterministic results caused by duplicate ordering values.