Why LIMIT with ORDER BY Can Produce Duplicate Pages in MySQL and How to Resolve It

When paging queries in MySQL using LIMIT , e.g., LIMIT 0,10 for the first page and LIMIT 10,10 for the second, the result set may contain records from the first page again if ORDER BY is also used.

Example that can cause duplication:

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

Using SELECT * with the same ORDER BY and LIMIT also exhibits the issue, but developers often want only a few columns without pulling the large post_content field.

SELECT
  *
FROM
  post
WHERE
  post_status = 'publish'
ORDER BY
  view_count desc
LIMIT
  5, 5

One workaround is to add a secondary deterministic sort key, such as the primary key ID :

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

In MySQL 5.6 the optimizer applies a priority queue optimization for ORDER BY … LIMIT queries. The priority queue keeps only the top n rows in a heap, which reduces memory usage but uses a heap sort , an unstable sorting algorithm. When the sort key values are equal (e.g., identical view_count ), the relative order of rows is not preserved, leading to different ordering on each execution and duplicate rows across pages.

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

The execution order of a SELECT statement is roughly:

(1) SELECT
(2) DISTINCT
(3) FROM
(4)
JOIN
(5) ON
(6) WHERE
(7) GROUP BY
(8) HAVING
(9) ORDER BY
(10) LIMIT

Because the priority queue sorts only the top n rows, after the SELECT phase the rows are in a heap order; during ORDER BY only the rows with the highest view_count are moved forward. The LIMIT then stops after the required number of rows, so when the second page is requested MySQL may pick any of the rows with equal view_count , causing repeats.

Solutions

1. Index the sorting column

Creating an index on the column(s) used in ORDER BY (e.g., view_count ) lets MySQL read rows in the required order directly, avoiding the priority‑queue heap sort and eliminating duplicates.

2. Understand pagination semantics

Pagination is built on top of sorting; the database provides sorting, while pagination is an application‑level requirement. Official MySQL and Oracle documentation describe LIMIT and ROWNUM techniques but do not define pagination itself. Even with correct sorting, high‑frequency inserts can still cause overlap between pages under the READ COMMITTED isolation level.

3. Common database sorting pitfalls

Without an ORDER BY , MySQL (using clustered indexes) and Oracle (using heap tables) return rows in nondeterministic order, which can vary between executions. Duplicate page results are a known limitation of pagination based on unstable sorts.

Other issues include differing handling of NULL values and empty strings across databases (e.g., Oracle treats NULL as unknown and cannot compare it, while MySQL stores an empty string as a zero‑length string).

In summary, the duplicate‑page problem stems from MySQL 5.6’s priority‑queue optimization that performs an unstable heap sort when ORDER BY and LIMIT are combined. Adding appropriate indexes, using deterministic secondary sort keys, or upgrading to a version where the optimizer behaves differently can mitigate the issue.