Why MySQL LIMIT Can Return Duplicate Rows and How to Fix It

Problem Scenario

A new transaction‑record export feature paginates results with LIMIT 1000. In development and test environments the export works, but in production users see duplicate rows and missing data because the ordering is nondeterministic when many rows share the same create_time.

Root Cause Analysis

The query orders by create_time descending and then applies LIMIT. When multiple rows have identical create_time values, MySQL may return those rows in any order because the ORDER BY column is not unique. The execution plan can therefore produce different row orders on successive pages, leading to duplicates or gaps.

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan.

Solution

Include a column that is guaranteed to be unique (e.g., the primary key id) in the ORDER BY clause. This makes the ordering deterministic and eliminates duplicate‑row pagination issues.

SELECT * FROM tb_order ORDER BY create_time, id DESC;

Extended Knowledge – LIMIT Optimization

MySQL’s optimizer treats LIMIT specially. When LIMIT is combined with ORDER BY and an index can satisfy the ordering, the engine stops scanning as soon as the required number of rows is found, avoiding a full sort.

If only a few rows are needed, using LIMIT alone can allow the optimizer to use an index instead of a full table scan.

Combining LIMIT row_count with ORDER BY lets MySQL stop sorting after the first row_count rows are retrieved.

Pairing LIMIT with DISTINCT stops once the distinct row count is satisfied.

When ORDER BY cannot use an index and LIMIT is present, the optimizer may avoid a filesort by using an in‑memory sort on the limited rows.

Limit and Order By Interaction

Because rows with identical sort keys are ordered nondeterministically, adding LIMIT can change which rows appear on each page. The following examples illustrate the effect.

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

Rows with the same category appear in a different order when LIMIT is applied.

Adding a unique column to the ordering resolves the issue:

SELECT * FROM ratings ORDER BY category, id;

+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

Including id makes the result order deterministic even when category values repeat.

Conclusion

The incident demonstrates how MySQL’s handling of ORDER BY with duplicate values can cause pagination anomalies. By appending a unique column to the ORDER BY clause, developers can guarantee stable result ordering, avoid duplicate exports, and benefit from MySQL’s LIMIT optimizations for better performance.

Reference: MySQL 8.0 Documentation – Limit Optimization (https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html)