We’ve blogged about this before. OFFSET pagination is terribly slow, once you reach higher page numbers. Besides, chances are, that your database doesn’t even implement it correctly, yet (and your emulation is probably wrong, too).
Join Markus’s movement for KEYSET pagination, which isn’t only much faster, but also more intuitive. Popular websites like Reddit, Twitter, Facebook and many more already implement keyset pagination. Why don’t you?
jOOQ is the only Java SQL API that already implements KEYSET pagination natively using the synthetic SEEK clause. Here’s how to do it:
DSL.using(configuration) .select(PLAYERS.PLAYER_ID, PLAYERS.FIRST_NAME, PLAYERS.LAST_NAME, PLAYERS.SCORE) .from(PLAYERS) .where(PLAYERS.GAME_ID.eq(42)) .orderBy(PLAYERS.SCORE.desc(), PLAYERS.PLAYER_ID.asc()) .seek(949, 15) // This jumps to the tuple (949, 15) .limit(10) .fetch();
Read more about this new movement here: http://use-the-index-luke.com/no-offset