Reverse Sorting Optimization

Concept

As page numbers increase, pagination query efficiency drops. To solve this, Jimmer supports reverse sorting optimization.

Reverse sorting optimization takes effect only when the following prerequisites are met together:

1. This feature does not apply to queries that only care about in-page data regardless of total row count before pagination, i.e. limit(limit, offset). It must be queries that care about both in-page data and total count.

2. The template query must have explicit orderBy clause.

3. The queried page data is late relative to all data before pagination. I.e.

   offset + pageSize / 2 > totalCount / 2

When all above conditions are met, Jimmer flips the sorting of the template query. Because a forward sorting query with relatively large page number and a reverse sorting query with relatively small page number are equivalent.

Example

Let's look at an example.

Java

public Page<Book> findBooks(
    int pageIndex,
    int pageSize    
) {
    BookTable table = Tables.BOOK_TABLE;
    
    return sqlClient
        .createQuery(table)
        .orderBy(table.name().asc(), table.edition().desc())
        .select(table)
        .fetchPage(pageIndex, pageSize);
}

Kotlin

fun findBooks(
    pageIndex: Int,
    pageSize: Int
): Page<Book> = 
    sqlClient
        .createQuery(Book::class) {
            orderBy(table.name.asc(), table.edition.desc())
            select(table)
        }
        .fetchPage(pageIndex, pageSize)

Assume there are 12 Book records total. pageSize is 2, so there are 6 pages. pageIndex has 5 possible values: 0, 1, 2, 3, 4, 5.

• 0, 1, 2: Data to query is early, use forward sorting pagination.

• 3, 4, 5: Data to query is late, use reverse sorting pagination.

Next we demonstrate the difference between reverse and forward sorting pagination with pageIndex=2 and pageIndex=3 as examples.

Forward Sorting

Executing findBooks(2, 2) generates the forward sorting SQL:

select 
    tb_1_.ID,
    tb_1_.NAME,
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_  
order by
    /* highlight-next-line */  
    tb_1_.NAME asc,
    /* highlight-next-line */
    tb_1_.EDITION desc
limit ? /* 2 */
offset ? /* 4 */ 

The result is:

{
    "rows":[
        {
            "id":11,
            "name":"GraphQL in Action",
            "edition":2,
            "price":81,
            "store":{
                "id":2
            }
        },
        {
            "id":10, 
            "name":"GraphQL in Action",
            "edition":1,
            "price":80,
            "store":{
                "id":2
            }
        }
    ],
    "totalCount":12,
    "totalPage":6
}

Reverse Sorting

Executing findBooks(3, 2) generates the reverse sorting SQL:

select
    tb_1_.ID,
    tb_1_.NAME, 
    tb_1_.EDITION,
    tb_1_.PRICE,
    tb_1_.STORE_ID
from BOOK tb_1_   
order by
    /* highlight-next-line */
    tb_1_.NAME desc, // Reversed: asc to desc
    /* highlight-next-line */ 
    tb_1_.EDITION asc // Reversed: desc to asc
limit ? /* 2 */
offset ? /* 4 */

The result is:

{
    "rows":[
        {
            "id":3,
            "name":"Learning GraphQL",
            "edition":3,
            "price":51, 
            "store":{
                "id":1
            }
        },
        {
            "id":2,
            "name":"Learning GraphQL",
            "edition":2,
            "price":55,
            "store":{
                "id":1
            } 
        }
    ],
    "totalCount":12,
    "totalPage":6
}