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 users
table 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
Last activity: Feb 14, 2024, 10:00 PM