0

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     <-- 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

6
  • 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 '20 at 11:59
  • @nbk Performance! issue, LIMIT / OFFSET is slow
    – Aldee
    Aug 6 '20 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 '20 at 13:57
  • @nbk please consider reading this article use-the-index-luke.com/sql/partial-results/fetch-next-page
    – Aldee
    Aug 6 '20 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 '20 at 14:48
0

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?

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.

1
  • 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 '20 at 18:07

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.