Mysql pagination without offset

Question

Let's assume I have got a table with these data:

Table data

I want to order it by createdAt column DESC, and id column DESC

select id, createdAt from post order by createdAt desc, id desc

Now it looks like this:

ORDER BY createdAt DESC, id DESC

I want to paginate it with 2 items per page and, for performance reasons, I don't want to use offset, just limit:

select id, createdAt from post order by createdAt desc, id desc limit 2

enter image description here

To get the next 2 items i use this query:

SELECT id, createdAt FROM post  WHERE createdAt <= '2014-11-16 09:11:03' AND (id < '15' OR createdAt < '2014-11-16 09:11:03') ORDER BY createdAt DESC, id DESC LIMIT 2

enter image description here

I can go on like this. Get the last item's createdAt and id then use it for next page query.

But i'm trying to formulate the previous page query for almost two days and can't figure out a way yet.

Here is what i tried already:

Get the first item from current result (instead of last) use it's id and createdAt field and reverse the conditions in query (createdAt >=, id >, created at >). But this query always gives me the first two results (which is normal because the rows providing this conditions are the first two).

I'm already out of ideas. I need help. Thanks.

Yes, it's full of log - analytics data with a couple million rows. — cnkt
– cnkt, Commented Nov 16, 2014 at 13:33
offset slows it down after a couple of pages. More info: i.imgur.com/Q8ZRKPs.jpg and slideshare.net/Eweaver/efficient-pagination-using-mysql — cnkt
– cnkt, Commented Nov 16, 2014 at 13:52

FrankPl · Accepted Answer · 2014-11-16 15:12:31Z

4

You could order ascending to get the correct records:

SELECT id, createdAt 
  FROM post 
 WHERE createdAt >= '2014-11-16 09:11:03' 
   AND (id > '15' OR createdAt > '2014-11-16 09:11:03') ORDER BY createdAt ASC, id ASC
LIMIT 2

and the reverse the sorting when displaying the result set.

answered Nov 16, 2014 at 15:12

FrankPl

13.3k2 gold badges17 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql pagination without offset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related