MySQL seek-method / keyset pagination? 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
SELECT  
  uid, nick, u.plz, geodb_locations.name 
FROM
  user as u 
LEFT JOIN 
  geodb_locations ON u.plz = geodb_locations.id 
WHERE 
  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) 
           
ORDER BY 
  FIELD(u.plz,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

Resultset
uid     nick    plz     name
46208   user21  29386   Graz
46050   user22  29386   Graz
44995   user23  29386   Graz
-----------------------------------
12883   user46  29384   Gratkorn     &lt;-- 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 &lt; 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) &lt; (123, 987654)
So how is it possible to paginate through the resultset? Any ideas? Thanks in advance!
Fiddle
http://sqlfiddle.com/#!9/62a380/10
You effectively have a 2-column ordering.  So, when &quot;remembering where you left off&quot;, you must include both columns.  Then, the &gt; becomes more complex.  I discuss that in a couple of places:
Should I store data pre-ordered rather than ordering on the fly?
http://mysql.rjweb.org/doc.php/deletebig#iterating_through_a_compound_key
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.