How to calculate the number of pages for search results

Imagine we have some records in our database, and we want to show them separately, divided in pages. Under the list we want to show hyperlinks to the next and previous pages. In the previous artice Long list pagination in Java I explained how to make Pagination. But for the pagination to work we need to know total number of rows. There are several techniques that are suitable in different situations.




Classical approach

This technique requires 2 requests to the database. First request gets the number of rows, second gets the required amount of rows:

1. SELECT COUNT(*) FROM table WHERE some condition;
-- gets total count of rows

2. SELECT col1, col2 FROM table WHERE some condition LIMIT page_size OFFSET offset
-- gets data

This works perfectly with small amount of data. But when you have a table with thousands of rows, executing 2 requests can last too long.


Result Caching

For the first run we get all rows from the database and put them into cache.

SELECT col1, col2 FROM table WHERE some condition

Then we count total rows and get the required amount of rows from cache.

 
Estimating

We have too many rows so we do not want to/can call COUNT(*) or get all records from the database. All we know is CURRENT_PAGE and PAGE_SIZE parameters.

We make a request to get the required rows even if they are not in the database.

SELECT col1, col2 FROM table WHERE some condition LIMIT page_size OFFSET offset

Then we count the rows in the result. If we get exactly PAGE_SIZE number of rows, it means that there are possibly more rows in table, so we can have the next page. If we get less rows than PAGE_SIZE, it means that this page is the last.

So the total number of rows would be:
totalRows = PAGE_SIZE * (page-1) + ROWS_SIZE

If ROWS_SIZE equals PAGE_SIZE then 

totalRows = totalRows + 1; // to tell that we possibly have more rows