Pagination issue while sorting based on last modified property

Question

I need to show some records sorted based on modified column (latest activity on top) (Post with new edit or comments at the top)

App UI has twitter like 'more' post button for infinite scroll. each 'more' will add next 10 records to UI.

Issue is that pagination index breaks when any of the to be shown record is modified

for example Suppose i have records A,B,C,..Z in jobs table.

first time I'm' showing the records A-J to the user using

SELECT * FROM Jobs WHERE 1 ORDER BY last_modified DESC LIMIT 0, 10

second time if none of the records are modified

SELECT * FROM Jobs WHERE 1 ORDER BY last_modified DESC LIMIT 10, 10

will return K-T

But if some body modifies any records after J before the user clicks 'more button',

SELECT * FROM Jobs WHERE 1 ORDER BY last_modified DESC LIMIT 10, 10

will return J-S

Here record J is duplicated. I can hide it by not inserting J to the UI, but the more button will show only 9 records. But this mechanism fails when large number of records are updated, If 10 records are modified, the query will return A-J again.

What is the best way to handle this pagination issue?

Keeping a second time stamp fails if a record has multiple updates.

Server cache of queries?

Answer 1

I would do a NOT IN() and a LIMIT instead of just a straight LIMIT with a pre-set offset.

SELECT * FROM Jobs WHERE name NOT IN('A','B','C','D','E','F','G','H','I','J') 
ORDER BY last_modified DESC LIMIT 10

This way you still get the most recent 10 every time but you would need to be tracking what IDs have already been shown and constantly negative match on those in your sql query.