0

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?

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.

| improve this answer | |
  • Thanks a lot! created_at is not unique indeed, which caused some trouble, but adding the id that way is a nice solution :) – Erik van de Ven May 24 '16 at 10:44
  • 1
    More discussion. – Rick James May 29 '16 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.