I am trying to change the pagination from OFFSET, LIMIT to keyset pagination. My problem is, the resultset consists of several groups (plz) ordered by uid. I added a fiddle, see http://sqlfiddle.com/#!9/62a380/10.

Query to get the results

  uid, nick, u.plz, geodb_locations.name 
  user as u 
  geodb_locations ON u.plz = geodb_locations.id 
  u.plz IN(29386,30013,29384,29385,29232,29481,29667,29355,29977,30370,30319,30160,29970,29932,29679,29268,30128,29169,29546,29235,29444,29148,29313,30259) 
  uid DESC


uid     nick    plz     name
46208   user21  29386   Graz
46050   user22  29386   Graz
44995   user23  29386   Graz
12883   user46  29384   Gratkorn     <-- Pagination with only uid stops here
32667   user47  29385   Gratwein
33950   user50  29481   Hitzendorf
33926   user51  29481   Hitzendorf
16027   user52  29667   Lannach
496     user53  29667   Lannach
19971   user55  29977   Pöls
37080   user58  30370   Weiz
33844   user59  30370   Weiz

To paginate through the results I use

uid < 46050

Problem: Pagination is not possible for the whole resultset because uid can be higher than the last uid because of several groups within the resultset. So pagination has to be done by more than 1 column!

Next problem: There is no second column for pagination. WHERE .. IN and ORDER BY FIELD sorting is fixed. These are zip-codes and they are mixed for each different query, so they can't be used for keyset pagination like (plz, uid) < (123, 987654)

So how is it possible to paginate through the resultset? Any ideas? Thanks in advance!

Fiddle http://sqlfiddle.com/#!9/62a380/10

  • your pagination looks more like split by postleitzahl(postcode) or name of the city, pagination in sql you ake with LIMIT OFFSET. Why do you not sort the data as wanted and paginate in your programm
    – nbk
    Aug 6, 2020 at 11:59
  • @nbk Performance! issue, LIMIT / OFFSET is slow
    – Aldee
    Aug 6, 2020 at 13:02
  • pagination for the enduser, should be made in the app webseite, mysql gies with rows, you can lmit the rows, and no limit works just fine with indexes. Still idon_'z gt what you expect. Most computer and handy have lots of power and can paginate without problems .
    – nbk
    Aug 6, 2020 at 13:57
  • @nbk please consider reading this article use-the-index-luke.com/sql/partial-results/fetch-next-page
    – Aldee
    Aug 6, 2020 at 14:04
  • I understood your problem even before you posted your link, but you don't have anything so that a subselect would increase the speed, you are stuck with the INn clause, and that is as slow as LIMIT,
    – nbk
    Aug 6, 2020 at 14:48

1 Answer 1


You effectively have a 2-column ordering. So, when "remembering where you left off", you must include both columns. Then, the > becomes more complex. I discuss that in a couple of places:

Should I store data pre-ordered rather than ordering on the fly?


General discussion of avoiding OFFSET for pagination: http://mysql.rjweb.org/doc.php/pagination

Granted, the use of FIELD() adds a wrinkle. But not a big wrinkle. Simply use the function call instead of a column name in a few places.

  • Thanks but your answer is not very helpful to me. I know that I have to order by 2 columns - but how? And I don't understand what you mean with the use of the FIELD function? What would be a better approach? Code example highly appreciated! I have added a fiddle for better understanding.
    – Aldee
    Aug 6, 2020 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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