How to Efficiently Determine Total Row Count in Paginated SQL Queries?

Determining Total Row Count with Pagination

When implementing pagination in an SQL query, accurately determining the total number of rows can be crucial. Running the query twice—once without the LIMIT clause to count the rows and once with the LIMIT clause to retrieve the paginated data—may not be optimal, especially for complex queries.

Fortunately, MySQL provides an elegant solution with the SQL_CALC_FOUND_ROWS and FOUND_ROWS() constructs. By adding SQL_CALC_FOUND_ROWS to the main query, MySQL counts the total number of rows, even in the presence of the LIMIT clause. A subsequent query using FOUND_ROWS() retrieves this count.

Here's an example:

SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();

This approach is faster than running the query twice and provides an accurate total count.

Deprecated Functions in MySQL 8

In MySQL 8.0.17, SQL_CALC_FOUND_ROWS and FOUND_ROWS() were deprecated. For newer MySQL versions, it's recommended to use COUNT instead.

SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;

By leveraging these techniques, developers can accurately count rows in paginated SQL queries without the overhead of multiple queries.

The above is the detailed content of How to Efficiently Determine Total Row Count in Paginated SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!