Why Does MySQL LIMIT with ORDER BY Return Duplicate Rows? Explained & Fixes

Problem Description

In MySQL pagination we usually use LIMIT, e.g., LIMIT 0,10 for the first page and LIMIT 10,10 for the second page. When LIMIT is combined with ORDER BY, the second page may contain rows that also appear on the first page.

Example query:

SELECT `post_title`,`post_date` FROM post WHERE `post_status`='publish' ORDER BY view_count desc LIMIT 5,5

Using the above query can return rows that also appear in LIMIT 0,5. Selecting all columns avoids the issue but pulls unnecessary data.

To retrieve only specific columns without duplication, an additional sorting key is added:

SELECT `post_title`,`post_date` FROM post WHERE `post_status`='publish' ORDER BY view_count desc, ID asc LIMIT 5,5

MySQL’s default sorting does not guarantee stable order when view_count values are equal, so adding ID asc is necessary.

Analysis

In MySQL 5.6 the optimizer uses a priority queue for ORDER BY … LIMIT queries. The priority queue keeps only the top N rows during sorting, reducing memory usage but relying on heap sort, an unstable sorting algorithm. Consequently, rows with identical view_count may be ordered differently between executions.

MySQL 5.5 lacks this optimization, so the problem does not appear there.

The execution order of a SELECT statement is: FROM → WHERE → SELECT → ORDER BY → LIMIT.

Because the priority queue sorts using heap sort, after SELECT the rows are in an arbitrary order for equal view_count values. When LIMIT is applied, only the first N rows are kept, leading to possible duplication on subsequent pages.

Solutions

Index the sorting columns : Adding an index on view_count (and optionally ID) allows MySQL to read rows in index order, avoiding the priority‑queue path.

Use a stable ORDER BY clause : Pagination is built on top of sorting; without a deterministic order the pages can overlap. Use ORDER BY view_count DESC, ID ASC to ensure stable results.

General database sorting notes : Without ORDER BY, result order is undefined. Different databases handle NULL and empty strings differently; MySQL treats empty strings as length‑0 strings, while Oracle treats them as NULL.

Additional Notes

Even with the above fixes, high‑frequency inserts under READ‑COMMITTED isolation can still cause overlapping pages. Pagination therefore never guarantees perfect accuracy in all scenarios.