3 ms·

I try to avoid using the key based paging because you're tying functionality to an implementation detail. If you change your key generation method (start using

I try to avoid using the key based paging because you're tying functionality to an implementation detail. If you change your key generation method (start using guids), the order may not be predictable. Or if you distribute your database with peer to peer replication, the order of your keys may only be consistent relative to other keys generated by the same instance. Or maybe you add some historical records, and they've got a time stamp that's accurate, but this sort of paging will show them in the order they were inserted.

lukaseder 13y ago
It's true that it is non-trivial to get this right with SQL directly. If SQL (or SQL tooling) had some abstraction for such a clause it would be easier to understand that the paging predicates can be added dynamically, based on the `ORDER BY` clause. In other words, even if the linked Stack Overflow question makes a very simple example, all you need is a unique sort order (i.e. containing at least one key, not necessarily the primary key) and then filter by that. This is described here: http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/ http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-... E.g. SELECT player_id, first_name, last_name, score FROM players WHERE game_id = 42 -- assuming 15 is Jack Harris's player_id AND (score, player_id) < (949, 15) ORDER BY score DESC, player_id DESC LIMIT 10;
tbarbugli 13y ago
And whats the alternative for you ?
- schrodinger 13y ago
  Order by the thing you actually care about, like a time stamp. And add an index on that column to make it efficient. You still need to analyze the query to determine if it's worth adding a covering index, or letting the DBMS perform a key lookup, but that's probably beyond the scope of a comment here. I guess it's not keyset based paging that is to be avoided, but paging based on a surrogate key which is a meaningless value. If your key had a meaningful value, it's a valid way of writing the query.

lukaseder 13y ago

It's true that it is non-trivial to get this right with SQL directly. If SQL (or SQL tooling) had some abstraction for such a clause it would be easier to understand that the paging predicates can be added dynamically, based on the `ORDER BY` clause. In other words, even if the linked Stack Overflow question makes a very simple example, all you need is a unique sort order (i.e. containing at least one key, not necessarily the primary key) and then filter by that. This is described here: http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/ http://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-... E.g. SELECT player_id, first_name, last_name, score FROM players WHERE game_id = 42 -- assuming 15 is Jack Harris's player_id AND (score, player_id) < (949, 15) ORDER BY score DESC, player_id DESC LIMIT 10;

tbarbugli 13y ago

And whats the alternative for you ?

schrodinger 13y ago
Order by the thing you actually care about, like a time stamp. And add an index on that column to make it efficient. You still need to analyze the query to determine if it's worth adding a covering index, or letting the DBMS perform a key lookup, but that's probably beyond the scope of a comment here. I guess it's not keyset based paging that is to be avoided, but paging based on a surrogate key which is a meaningless value. If your key had a meaningful value, it's a valid way of writing the query.