MySQL – Cursor Based Pagination for Timestamp Field with Non-Unique Values

MySQLpagingtimestamp

We are trying to create a query for the following table : 

 CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(60) NOT NULL,
  `assigned_To` varchar(40) DEFAULT NULL,
  `updated_At` datetime(3) NOT NULL,
  `completed_At` datetime(3) DEFAULT NULL,
  `deleted_At` datetime(3) DEFAULT NULL,
  `created_At` datetime(3) NOT NULL,
  `deadline` datetime(3) NOT NULL,
  `notes` varchar(400) DEFAULT NULL,
  `completed` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `action_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `action_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=latin1

We want to show the Actions using pagination based on deadline field in Ascending order. But deadline can have same values and is creating problem for us to implement cursor based pagination on deadline field.

Action can be created for any future date. We thought of using created_At and deadline but it fails as we can create Action on 22 Nov with deadline 25 Nov and another action on 23 Nov with deadline 24 Nov. I mean we there is no relationship between two of them. and exactly similar thing happens with the id and deadine column.

How can I tackle this problem of showing Actions using pagination with deadline in ASC order.

Best Answer

ORDER BY deadline ASC, id ASC will be "deterministic" (assuming id is the PRIMARY KEY for the table). And include INDEX(deadline, id).

More discussion of pagination and the evils of OFFSET: http://mysql.rjweb.org/doc.php/pagination

If there is any chance of adding or deleting rows while the user is paging through the list, items can be skipped or duplicated in the paginated list. "Remembering where you left off" (see link) solves the problem. However, it is a little tricky to say > when two columns are involved.

Related Question