Understanding Pagination Issues with ORDER BY and LIMIT in MySQL

When writing a pagination query that mixes ORDER BY and LIMIT , the result set for page N can contain the same records as previous pages, leading to sorting chaos.

In MySQL, pagination often uses LIMIT (e.g., LIMIT 0,20 for the first page, LIMIT 20,20 for the second). Adding ORDER BY is common, but when both are used the N‑th page may repeat data from earlier pages.

SELECT a,b FROM table WHERE c=1 ORDER BY d desc LIMIT 0,20

Such a query can return the same row as LIMIT 20,20 . To avoid this, developers often append a unique identifier (e.g., the primary‑key ID) to the ORDER BY clause:

SELECT a,b FROM table WHERE c=1 ORDER BY d desc, id desc LIMIT 0,20

In theory MySQL already uses the primary‑key as a tie‑breaker when the ordered column values are equal, so adding ID should be unnecessary. In practice, however, MySQL 5.6 introduced an optimizer optimization for ORDER BY … LIMIT that employs a priority queue.

The priority queue keeps only the top n rows during sorting, using a heap sort algorithm, which is unstable: rows with equal sort keys may appear in different orders on successive executions.

MySQL 5.5 lacks this optimization, so the described duplication problem does not occur in that version.

The execution order is roughly: FROM → WHERE → SELECT → ORDER BY → LIMIT . Because the priority queue retains only a limited number of rows, when the second page is fetched MySQL may pick any of the rows with the same d value, causing nondeterministic ordering and possible duplication.

(1) SELECT 
(2) DISTINCT <select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5) ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

To mitigate the issue, consider the following solutions:

Use a column with unique values for sorting; if the column is indexed, the database can read rows in order directly. Adding an ID sort at the end is also safe.

Understand that pagination is built on sorting; the database provides sorting, while pagination is an application‑level requirement. Official docs describe LIMIT (MySQL) and ROWNUM<n (Oracle) but do not define pagination semantics.

Be aware of common database sorting pitfalls: without ORDER BY , result order is undefined and may differ between MySQL (clustered index) and Oracle (heap tables). Duplicate rows can appear when sorting on non‑unique columns.

Handle deep pagination carefully: large offsets cause the engine to read and discard many rows, consuming buffer pool memory and slowing queries. Prefer filtering by a monotonic column (e.g., auto‑increment ID) combined with LIMIT to avoid costly offsets.

Additional notes cover differences in NULL handling between databases, the impact of deep pagination on performance, and the fact that pagination accuracy is not guaranteed by the DBMS itself.