How do you retrieve the total number of rows even when using pagination with LIMIT in SQL?

Retrieve Total Rows Even When Using Pagination with LIMIT

When paginating results with the LIMIT clause in SQL, it can be cumbersome to have to run the query twice to obtain the total number of rows. This is especially true for complex queries that take a significant amount of time to execute. Fortunately, there's a solution that allows you to do this with a single query.

SQL_CALC_FOUND_ROWS and FOUND_ROWS()

Beginning with MySQL 4.0.0, you can utilize the SQL_CALC_FOUND_ROWS option in your main query to inform MySQL to count the total rows while disregarding the LIMIT clause. This requires you to execute a second query to retrieve the row count. However, this query is typically much simpler and faster than the main data retrieval query.

Here's how to use it:

<code class="sql">SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;
SELECT FOUND_ROWS();</code>

The second query immediately following the first one captures the total row count using the FOUND_ROWS() function.

Deprecated in MySQL 8.0

In MySQL versions 8.0.17 and later, the SQL_CALC_FOUND_ROWS modifier and FOUND_ROWS() function have been deprecated and will be removed in a future release. The MySQL documentation now recommends using COUNT instead to obtain the total row count.

For example:

<code class="sql">SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) WHERE id > 100;</code>

Conclusion

By leveraging these techniques, you can effectively retrieve the total number of rows even when using pagination with the LIMIT clause, improving your query efficiency and streamlining your code.

The above is the detailed content of How do you retrieve the total number of rows even when using pagination with LIMIT in SQL?. For more information, please follow other related articles on the PHP Chinese website!