1

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.

1
  • should we for offset based Pagination. Actions are added dynamically. or add seconds/milliseconds to the deadline column --> at this moment User only sets hours and minutes for the deadline field
    – j10
    Nov 22, 2017 at 6:15

1 Answer 1

2

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.

4
  • Sorry for not getting back on this one. But you suggested solution based on Offset Pagination right ? can we work out some solution for Cursor or Value based Pagination when deadline field has repeated values (and there is no relation between deadline and id columns)
    – j10
    Mar 9, 2018 at 9:30
  • 1
    @j10 - Compound Leftoff
    – Rick James
    Mar 13, 2018 at 2:31
  • Thank you Rick. Your Solution was helpful. We also filter results on 'completed_At' in DESC fashion and 'deleted_At' in DESC fashion. So we should define 2 more indexes : ('deleted_At', 'id') and ('completed_At', 'id') ?
    – j10
    Mar 13, 2018 at 10:58
  • 1
    @j10 - that sounds correct.
    – Rick James
    Mar 13, 2018 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.