Sample Header Ad - 728x90

MySQL - UUID/created_at cursor based pagination?

12 votes
2 answers
15139 views
For a large dataset, paginating with an OFFSET is known to be slow and not the best way to paginate. A much better way to paginate is with a cursor, which is just a unique identifier on the row so we know where to continue paginating from where we last left off from the last cursor position. When it comes to a cursor where it is an auto incrementing id value, it's fairly easily to implement: SELECT * FROM users WHERE id <= %cursor // cursor is the auto incrementing id, ex. 100000 ORDER BY id DESC LIMIT %limit What we're not certain about, is if instead of an auto incrementing id cursor, the only unique sequential identifiers for the cursor are uuid and created_at on the table rows. We can certainly query based on the uuid to get the created_at, and then select all users that are <= created_at but the issue is what if there are multiple instances of the same created_at timestamp in the users table? Any idea how to query the userstable based on uuid/created_at cursor combination to ensure we get the correct datasets (just as if we were using auto incrementing id)? Again, the only unique field is uuid since created_at may be duplicate, but their combination would be unique per row.
Asked by Wonka (145 rep)
Apr 30, 2018, 06:27 PM
Last activity: Feb 14, 2024, 10:00 PM