mysql pagination - avoid missing or duplicate data to client

Question

I have one table in mySql like this

CREATE TABLE `usermst` (
  `userid` smallint(5) unsigned NOT NULL,
  `username` varchar(45) NOT NULL,
  `insdate` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

having data as follows

insert into usermst(userid, username)
values (1, "user 1"),
       (2, "user 2"),
       (3, "user 3"),
       (4, "user 4"),
       (5, "user 5"),
       (6, "user 6"),
       (7, "user 7"),
       (8, "user 8"),
       (9, "user 9"),
       (10, "user 10");

If I query for 1st page with 4 records (pagination), its work fine.

select * from usermst order by insdate desc, userid desc limit 0,4;

output:

userid  username insdate
10      user 10  2016-03-08 12:32:04.239335
9       user 9   2016-03-08 12:32:04.239335
8       user 8   2016-03-08 12:32:04.239335
7       user 7   2016-03-08 12:32:04.239335

request for 2nd page with following query

select * from usermst order by insdate desc, userid desc limit 4,4;

output:

userid  username insdate
6       user 6   2016-03-08 12:32:04.239335
5       user 5   2016-03-08 12:32:04.239335
4       user 4   2016-03-08 12:32:04.239335
3       user 3   2016-03-08 12:32:04.239335

But, If somehow records deleted for page1, data will be missed for client. or records inserted in page1 (ordering as per my query), duplicate data will be fetch on client. How to prevent this bug?

I don't really get the problem, why would data be missed and why there would be duplicates — xpy, Mar 08 '16 at 12:47
if data is not there anymore, it should not be available for the cllient. however if you want to avoid this "bug", you can fetch entire data into php array and paginate this data instead of making new queries. — mitkosoft, Mar 08 '16 at 12:49
hi @xpy and vigikaran, what happen if userid 9 delete before 2nd query fire. — Manish Sapkal, Mar 08 '16 at 13:17
About inserts, the question is, do you want to include these new records in a result set at all? Regarding deletes, the question is, is it critical to show less records on some pages? And last question, would it be fine if the result set will change for page #N if one clicks prev->next or next->prev? — Axalix, Mar 08 '16 at 13:17
@Axalix, Include new record on next page. and yes off course, result will be change for page. — Manish Sapkal, Mar 08 '16 at 13:27
It **should** behave that way, if something is added or removed, it should change the pagination results. — xpy, Mar 08 '16 at 13:33
Then this problem doesn't have a solution you're looking. Imagine you are on a page #1and you show 5 records per page. Imagine also you are staying on this page for an hour, but the DB keeps processing inserts what shifts these 5 records on a second page. So now after waiting for an hour you're clicking on page #2, of course you will see the same records again. — Axalix, Mar 08 '16 at 13:45
Btw this is one of the reasons why some sites don't have pages, but use prev-current-next logic based on a cursor. http://stackoverflow.com/questions/13872273/api-pagination-best-practices — Axalix, Mar 08 '16 at 13:54

Chris Peacock · Answer 1 · 2016-03-08T13:26:55.430

The approach I would use is to instead base your pagination on the sort column value - but only when traversing through pages (Next / Prev buttons). When wishing to navigate to a particular page number, your existing approach would still have to be used, but I think the problem you describe only really is a problem when using Next / Prev buttons to navigate.

For every 'next' page, fetch all rows where the 'insdate' is less than the last 'insdate' on the previous page fetch.

For the first page:

SELECT * FROM usermst ORDER BY insdate DESC, userid DESC LIMIT 0,4;
SELECT @prevInsDate := insdate, @prevUserId := userid FROM usermst 
    ORDER BY insdate DESC, userid DESC LIMIT 3,1;

For the next (second) page:

SELECT * FROM usermst 
WHERE insdate <= @prevInsDate AND userid < @prevUserId
ORDER BY insdate DESC, userid DESC LIMIT 0,4;

SELECT @prevInsDate := insdate, @prevUserId := userid FROM usermst 
    ORDER BY insdate DESC, userid DESC LIMIT 7,1;

Where @prevInsDate and @prevUserId have been declared as variables.

mysql pagination - avoid missing or duplicate data to client

1 Answers1