TDSQL-C for MySQL
- Release Notes and Announcements
- Announcements
- Product Introduction
- Suggestions on Usage Specifications
- Kernel Features
- Kernel Version Release Notes
- Optimized Kernel Version
- Functionality Features
- Performance Features
- Security Features
- Stability Feature
- Analysis Engine Features
- Purchase Guide
- Value-Added Services Billing Overview
- Database Audit
- Audit Rule Template
- Serverless Service
- Serverless Introduction
- Serverless Resource Pack
- Configuration Change
- Operation Guide
- Database Connection
- Configuring Network Address
- Instance Management
- Configuration Adjustment
- Instance Mode Management
- Cluster Management
- Scaling Instance
- Database Proxy
- Database Proxy Kernel Features
- Managing Database Proxy
- Automatic Read/Write Separation
- Connection Pool Feature
- Other Features
- Account Management
- Console Account
- Database Account
- Configuring Custom Password Strength
- Database Management
- Database Management Tool
- Columnar Storage Index (CSI)
- Utilization of Columnar Storage Indexes
- Read-Only Analysis Engine
- Feature Limits and Compatibility
- Description of Functions and Operators
- Read-Only Analysis Engine Management
- Data Loading
- Executing Queries
- Exclusive Features
- Performance Optimization
- Performance White Paper
- Parameter Configuration
- Multi-AZ Deployment
- Backup and Restoration
- Performing Backup
- Managing Backup
- Rollback
- Restoring Data from Logical Backups
- Restoring Data Through the Console
- Restoring Data from Snapshot Backup
- Data Migration
- Parallel Query
- Setting Parallel Query
- Database Security and Encryption
- Access and Authorization
- Security Group Management
- TencentDB Security Group Management
- Monitoring and Alarms
- Monitoring
- Alarms
- Monitoring Metric Alarm
- Practical Tutorial
- Data Rollback
- Database/Table-Level Rollback
- Rolling back Entire Cluster
- White Paper
- Security White Paper
- Performance White Paper
- Troubleshooting
- Connection Issues
- API Documentation
- Making API Requests
- Instance APIs
- Account APIs
- Audit APIs
- Database Proxy APIs
- Backup and Restoration APIs
- Parameter Management APIs
- Performance Analysis APIs
- Serverless APIs
- ResourcePackage APIs
- Other APIs
- FAQs
- Service Agreement
- TDSQL-C Policy
- General References
Ordered Pagination Feature
Last updated: 2025-03-20 16:06:07
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
For details on enabling and using the ordered pagination feature, see Paging Order Preservation Feature.
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No