MySQL Cursor Query: Say goodbye to slow pagination with `WHERE id> last_id`

When developing applications that handle large volumes of data, we often need to iterate over the entire table to perform data processing tasks—such as generating sitemaps, exporting data, or performing batch updates. The most common approach is to use  LIMIT offset, size Paginated query. However, when... offset When the query scope grows significantly, it will become increasingly slow, potentially leading to timeouts or a surge in server load. This article introduces a simple yet highly efficient approach-Primary key-based cursor query（WHERE id > last_idIt ensures a constant data traversal speed, making it effortlessly capable of handling millions of data records.

The pain points of traditional pagination

Suppose we have a song list; music_songsThe primary key is ; song_idWe need to export the URLs of all songs into a sitemap. Traditional pagination approach:

SELECT song_id FROM music_songs ORDER BY song_id LIMIT 1000000, 1000;

When this SQL statement is executed, the database must first scan and skip the first 1 million rows, then return the next 1,000 rows. Even if  song_id When a primary key index is used, MySQL first reads 1 million index entries before fetching the corresponding data from the underlying table. When the offset is very large, this portion of the cost can be substantial; furthermore, as the page number increases, performance decreases linearly.

Worse still, using ; OFFSET Can lead torescanEach query starts counting from the first row; the process becomes slower as the query progresses. When generating a sitemap containing 6 million data entries, the initial few hundred thousand entries can be returned relatively quickly; however, for subsequent batches, each batch may take dozens of seconds or even several minutes to process, and the entire export process can take several hours.

The core concept of the CUBIC query

The CROSOLAR query leverages the auto-incrementing primary key feature, querying only a single record each time; id > Previous maximum; id This record prevents the "skipping" of already read data. Its basic form is:

SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT batch_size;

After each query, record the maximum value for this batch. id As the next phase; last_idRepeat this process until no data is returned.

The query performance for this approach isconstantThis is because each time the primary key index is used to locate the record directly; last_id Next position; then scan; batch_size  One record. Regardless of the total data volume, the time taken for each batch of queries is nearly identical.

Why is the CQL query so fast?

1. Direct Indexing：id > last_id It allows for rapid定位 to the starting position in the index, eliminating the need to scan previous records.
2. Sequential readingSince the data is stored in sorted order within the index, subsequent operations... ORDER BY id It performs sequential reading, which is highly efficient.
3. Fixed scan amountScan only one batch at a time; batch_size A fixed number of data entries does not increase as the total data volume grows.

Crosstab query use cases

• Export all table data: Such as generating sitemap, exporting CSV, backing up data, etc.
• Batch processing tasks: Perform certain operations on each piece of data (such as AI generating articles, updating fields) without skipping access.
• Data migration:Copy data from one table to another.
• Real-time data stream processing: Continuously read new data from the database (similar to message queue).

Crosstab query restrictions

• Must be based on a monotonically increasing primary key(or other sorting fields) to ensure order. If the primary key is not monotonically increasing, but there are timestamp fields in the business, you can also use a similar method, but you need to create a corresponding index.
• No random page jumps: If you need to implement a user interface that "jumps to page N", cursor query is not suitable because  last_id  Must be determined by the previous page. You should still use  LIMIT offset, size, but you can consider optimizing it in your business (such as reducing the number of pages, using cache).
• Pay attention to the stability of the sort field: If there are duplicate values ​​in the sorting field, you must ensure that the sorting is unique, otherwise data may be lost. Usually the primary key is unique and increasing, which is the safest.

Performance Comparison Test

When I was processing sitemap generation for 6 million song data, using traditional  OFFSET  Paging, it takes several hours to generate more than 100 XML files, and it becomes slower as time goes by. After switching to cursor query, the entire process was completed within a few seconds, and each batch of queries was stable at the millisecond level. The performance difference is amazing!

sum up

cursor query (WHERE id > last_id) is a simple but powerful optimization technique, especially suitable for data processing scenarios that require traversing the entire table. It ditches the bulky  OFFSET, utilizing the orderliness of the primary key index to achieve constant-time paging. In actual development, when you need to "process all data one by one", you might as well try this method, which may bring you unexpected performance improvements.

Tip: If other filter conditions are required in the query (such as  status = 1), must be in  (status, id) Create a composite index to ensure that queries can still be efficiently located. For example:

ALTER TABLE music_songs ADD INDEX idx_status_id (status, id);

Then, the query is modified to:

SELECT * FROM music_songs WHERE status = 1 AND id > last_id ORDER BY id LIMIT batch_size;

This approach not only enables filtering but also leverages index ordering, delivering outstanding performance.