In MySQL, the data on the second page may be duplicated with the data on the first page if an order by clause with duplicate field values is used together with a limit clause for pagination queries. This is because MySQL uses heapsort, which is an unstable sorting method. When duplicate values exist, the output result changes each time. Therefore, when an order by...limit... statement is used, it is suggested that indexes are set for fields in the order by clause or other fields are added to the order by clause to maintain data uniqueness.
To solve this problem, the read-only analysis engine provides the ordered pagination feature. By using this feature, you do not need to worry about the database logic, and no duplicate data exists in the result output by order by...limit..., ensuring the sequence consistency.
Implementation Principles
In SQL execution scenarios involving sorting and pagination, the read-only analysis engine performs implicit sorting on the full data by default after it determines the business sorting logic. This avoids the problem of data duplication caused by limit operations.
Ordered Pagination Scenarios
Only the limit operator is used, and no order by operator is used.
Duplicate order by keys exist, and only some fields in the output result are sorted.
The subquery involves sorting, but sorting is not performed in the outer query.
Impact on Performance
If the ordered pagination feature is enabled, additional sorting operations will be performed. Therefore, some queries may experience performance degradation. Please choose whether to use the ordered pagination feature according to the actual business situation.
Usage Instructions