Understanding and Solving Duplicate Records When Using ORDER BY with LIMIT in MySQL

When using LIMIT for pagination in MySQL, e.g., LIMIT 0,10 for the first page and LIMIT 10,10 for the second, combining it with ORDER BY can cause the second page to contain rows that also appear on the first page.

Example query that may produce duplicates:

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

A workaround that avoids duplication is to select all columns:

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

However, developers often need only specific columns and do not want to retrieve the entire post_content . To address this, an additional sorting key can be 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 behavior is to use the primary key ID as a tie‑breaker when view_count values are equal, but due to the optimizer’s handling of ORDER BY … LIMIT in MySQL 5.6, a priority‑queue (heap sort) is used.

The priority queue keeps only the top n rows in memory, which reduces sorting overhead but introduces instability because heap sort is not a stable sorting algorithm; rows with equal sort keys may appear in different orders across executions.

MySQL 5.5 does not apply this optimization, so the duplicate‑page issue does not occur in that version.

The logical execution order of a SELECT statement is:

(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 after the SELECT phase, the result set may be arbitrarily ordered when view_count values are identical, leading to duplicate rows on subsequent pages.

Solutions

1. Index the sorting columns – Adding an index on view_count (or a composite index with ID ) allows MySQL to read rows in the required order directly, eliminating the need for the unstable priority‑queue.

2. Understand proper pagination – Pagination relies on a deterministic order; without a stable sort, duplicate pages are inevitable. Use explicit ordering keys and consider keyset pagination (e.g., “WHERE view_count < last_value”) for large datasets.

3. Common sorting pitfalls – Without ORDER BY , MySQL does not guarantee row order. Different databases handle NULLs and empty strings differently, and heap‑sorted results are not stable.

In summary, the duplicate‑page problem arises from MySQL 5.6’s priority‑queue optimization for ORDER BY … LIMIT . Adding appropriate indexes, using stable ordering, and applying correct pagination techniques resolve the issue.