1

I'm planning to write a query that sorts the result by a value s and then paginate the result. Let's say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?

1
  • Read up on keyset pagination – Charlieface Jan 19 at 2:11
1

When the field being sorted on is not unique, then the order of the results are non-deterministic, meaning there is no guarantee on their exact ordering. Specifically in your example case where the first 10 results all share the same exact value for the s field could yield indeterminate results for the first time you run the query vs the second time. It is possible you see the same result in both cases or not at all as you guessed. You would need to add another field to the ORDER BY clause that uniqifies the sort in order to always guarantee deterministic results.

For a little proof of case and further reading, here is a DBA.StackExchange question and answer where the user was experiencing unpredictable ordering of results because the field they were sorting on wasn't unique. The comments on that answer briefly mention that being the cause.

0

Not guaranteed. And that's not the only problem with that algorithm. A DELETE or INSERT can cause a duplicated or skipped row.

Read the following for a solution to all of the above problems, plus a performance boost: http://mysql.rjweb.org/doc.php/pagination

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.