pagination with JOOQ

Question

I'm trying to use JOOQ for paginating results from Mysql DB, I want to request my MySQL DB for a specific offset.

My Clients are requesting different sizes of results with different offsets and my service is stateless.

I don't want to use limit and offset since I know they have a bad performance.

    public void fetch() {
    Cursor<Record> cursor = null;

    String query = "books WHERE type = 'KIDS'";


    try {
        cursor = create.selectFrom(query).fetchLazy();

        while (cursor.hasNext()) {
            Record book = cursor.fetchNext();
        }
    }
    finally {
        if (cursor != null) {
            cursor.close();
        }
    }
}

How can I start from page 5 or from books 1000 to 1100?

Best Regards, Matan

Answer 1

Performance wise, there's nothing wrong with LIMIT. It is true that OFFSET can lead to performance issues, because the skipped rows from OFFSET have to be processed by the database server just as if you actually fetched them, with the exception of not having to transfer them over the wire.

Having said so, your approach of trying to implement OFFSET in the Java client is much worse than implementing it in SQL. You do (at least) the same amount of work on the server side, while transferring much more data to the client just to discard it again. Just use OFFSET, in this case, if you must paginate to page 5.

A faster way to paginate is keyset pagination, which is supported by jOOQ via the SEEK clause. It is a different type of pagination, which doesn't allow to jump to page 5 very easily, only to the "next page" (such as e.g. the Twitter timeline).