Mysql – what is most efficient way to retrieve next and previous items based on ordering and current item only

mongodbMySQL

So I have this problem in many of my apps and I don't even know how to describe it correctly.

I'm using MongoDB (3.6) so my IDs are hashes.

I have a table/grid view, which displays items mapped to database records, then when someone clicks on one you get the single-item view page. But then from there, I want to allow next and previous "pagination".

I quote "pagination" because the URL never contains a page number, and can't. The url only contains a direct link to an item.

For example: mydomain.com/posts/59118d075e04da6104421bb4

Let's say to keep it simple, items have a created_at timestamp and the ordering is always by created_at ascending.

You have 100 items, you land on item 54 out of 100. But you don't know that because the id is a hash. Maybe it was 54th in the ordering when you bookmarked it and now since more items have been added it's now 58th out of 104.

The API returns the data for that item, but then what happens when the user wants to navigate to the previous or next item. What's the most efficient way to handle that?

A very slow way would be to query ALL items ordered by created_at, then loop through the list until you find the one that matches the current ID. And then -1 +1 that would be your previous/next item.

And every time a user clicks the prev/next arrow you repeat this slow process.

I have the same problem in MySQL (5.7). I don't know how to say "given this ID, what is the prev/next records based on this ordering"

Another way I thought of doing this is storing a persistent array of all the IDs, and then getting the position for the item that matches a given ID, and then you could more easily get the IDs of items +1 -1 that position. That wouldn't work too well if you had a huge number of records though.

Is there a more efficient way? Hope someone can enlighten me.. thank you!

Best Answer

In MySQL it would be simple. Given an @id value, you can easily find the next or previous rows - and efficiently assuming there is an index on (id) and another index on (created_at):

-- find next item
select t.*
from tablename as t
     join
     ( select created_at
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
order by t.created_at asc
limit 1 ;   -- N next items

To find the previous items, you only have to change > to < and asc to desc.

If there can be rows with same created_at, then you need to modify:

-- find next item
select t.*
from tablename as t
     join
     ( select created_at, id
       from tablename
       where id = @id
     ) as current
     on t.created_at > current.created_at
     or t.created_at = current.created_at and t.id > current.id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items

The whole current subquery is not truly needed as the values @id and @created_at will likely be available from the previous run so the query can be simplified:

-- find next item
select t.*
from tablename as t
where   t.created_at > @created_at
     or t.created_at = @created_at and t.id > @id
order by t.created_at asc, t.id asc
limit 1 ;   -- N next items
Related Question