MySQL - UUID/created_at cursor based pagination?
WHERE created_at <= x
AND ( created_at < x OR uuid < y )
ORDER BY created_at DESC,
uuid DESC
or this equivalent:
WHERE ( created_at < x
OR ( created_at = x AND uuid < y )
)
ORDER BY created_at DESC,
uuid DESC
This technique works for any pair of columns where the first one (created_at
) could have duplicates and the second is unique (uuid
or id
).
And this is required:
INDEX(created_at, uuid)
Note that both parts of the WHERE
are DESC
. Mixing ASC
and DESC
will defeat the usability of the INDEX
. (MySQL 8.0 can work around that.)
Note also, that this assumes you don't care what order the rows are in when created_at
is duplicated, but you do want a consistent ordering. Note that uuid
will be seemingly random, but still consistent. With that said, id
(with or without Galera) and uuid
work equally well.
(UUIDs suck, but that is a different discussion.)
More on pagination without using OFFSET
.
I will answer what you asked, but first let me tell you that I don't understand why you want to do that. An autoincremental id is very good for this task. But it is correct to also use a timestamp column, because it is a bad practice to rely on an id for sorting. Why? Because there are cases when its order might not be chronological - for example, if you use Galera cluster and you have failovers.
To do what you asked, first create this index:
ALTER TABLE users
ADD INDEX idx_created_at_uuid (created_at, uuid);
The order columns is important. If you reverse it, the index will not be useful.
Now you just need to run a query like this:
SELECT some_columns
FROM users
WHERE created_at <= x AND uuid = y
ORDER BY created_at DESC;
uuid
is only needed because created_at is not unique. If created_at
is not the first column, MySQL will have to read all rows and copy them to a temporary table (which could be in-memory or on-disk) to sort them.
If you decide to use the id, just keep the above snippets, but replace uuid
with id
.