Page numbers & total result count in a cursor based pagination

Question

Though it may sound like a stupid question, sometimes it is necessary to show page numbers (and also the last page). What is the best way to calculate total row counts and also calculate page numbers (by providing a page query to return specific page) in a cursor based pagination? Also are all these possible in a single query?

Popular example: Google shows page numbers in google search using a cursor based pagination and also the row count.

Fayfayal

Answer 1

Notice that Google does not show the last page number.

They show 1 - 10, and Next.

At the top, they do show an estimate of the total results, like:

About 1,730,000,000 results (0.94 seconds)

But this is not using SQL. This is using Google's proprietary distributed search technology.

To answer your question, MySQL had a feature called SQL_CALC_FOUND_ROWS, which you could use as a query modifier on a SELECT query with LIMIT. Then query the value FOUND_ROWS() to know how many rows would have returned if you had not used LIMIT.

SELECT SQL_CALC_FOUND_ROWS ... FROM Mytable WHERE ... LIMIT 10;

SELECT FOUND_ROWS();

However, https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows says this feature is deprecated. It turns out it's not efficient, and it's actually preferred to do two queries, one to count rows, then a second query to return results with LIMIT.

SELECT COUNT(*) FROM Mytable WHERE ...;

SELECT ... FROM Mytable WHERE ... LIMIT 10;

The performance measurements are shown in this blog: https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Hellespont

Can you guide me with some ideas about how to calculate page numbers and navigate to that page using cursor-based pagination? – Fayfayal

I don't know that it's possible to navigate to an arbitrary page when you use cursor-based pagination. You need to know the id of the start of the "next" page and that's only available if you have data for the preceding page. To skip to an arbitrary page you would still have to start at the first page and read all the data up to the page you wanted. This is equivalent to using LIMIT and OFFSET. So for the first page you want, you need to use LIMIT and OFFSET. Then once you have that page, you can use cursor-based pagination to view the subsequent pages. – Hellespont

Thanks. I thought so. – Fayfayal