OFFSET using a GUID kind of ID

Question

I would like use the OFFSET in a SELECT query so I can use pagination in my webapplication. The only problem is I only know the ID of the row from which I would like to start, not the actually OFFSET. An ID looks like this: asdfasfdf894t54yrqewrqew.

So what I do, I SELECT all results from a table and ORDER it by the created_at column and set the LIMIT to a certain value. Now I only know the last ID I get returned, so I would like to start the next page from that ID.

Anyone who knows an easy way to accomplish this using a MySQL query?

Answer 1

Don't use offset - it has to read all the rows to skip them.

If you order rows for pagination by created_at, then thats the column you need to use.

First page:

SELECT ... FROM table
ORDER BY created_at, id
LIMIT N + 1;

The +1 gives you a peek to the next page - you can quickly decide if next page exists and where does it start. If created_at is not guaranteed to be unique, adding the ID to the ordering helps keep it stable.

Next page:

SELECT ... FROM table
WHERE created_at >= <(x+1)th created_at from previous page>
    AND id >= <(x+1)th id from previous page>
ORDER BY created_at, id
LIMIT N + 1;

You use the extra item from fetching the previous page to quickly find the start of current page using the ordering index to get new page.