Why Does MySQL LIMIT with ORDER BY Return Duplicate Rows on Page 2?

Problem Description

In MySQL pagination using LIMIT, e.g., LIMIT 0,10 for the first page and LIMIT 10,10 for the second, combining with ORDER BY can cause duplicate rows on the second page.

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

Running this query may return rows that also appear on the first page. Using SELECT * with the same ORDER BY suffers the same issue.

When the table has many columns and only a few are needed, adding a second ordering field (e.g., ID ASC) seems to avoid duplicates, but MySQL can still reorder inconsistently.

Analysis of the Issue

In MySQL 5.6 the optimizer applies a priority‑queue optimization for ORDER BY … LIMIT statements. It keeps only the top N rows in a heap, which is an unstable sort; equal values may be output in arbitrary order.

Because heap sort is unstable, rows with the same view_count can appear in different orders between executions, leading to apparent duplication when paging.

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

The execution order is SELECT → ORDER BY → LIMIT. After SELECT, rows are arranged by heap sort; only the first N rows are kept, and when view_count is equal the order is nondeterministic.

Solutions

1. Add an index on the sorting columns

Creating an index on view_count (and optionally ID) lets MySQL read rows in index order, avoiding the priority‑queue path and providing stable ordering.

2. Understand pagination semantics

Pagination is built on top of sorting; the database does not guarantee stable ordering without an explicit, deterministic ORDER BY clause that includes a unique tie‑breaker.

3. Common sorting pitfalls

Without ORDER BY, result order is undefined and may differ between MySQL (clustered index) and Oracle (heap tables).

Duplicate pages can occur because the database does not enforce stable ordering for equal keys.

NULL and empty‑string handling differs between databases; MySQL treats an empty string as a zero‑length string, while Oracle treats it as NULL.