mysqlsqlpaginationsql-optimizationdatabase-optimization

Page numbers & total result count in a cursor based pagination

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.

Solution

  • Notice that Google does not show the last page number.

    They show 1 - 10, and Next.

    enter image description here

    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/