FOUND_ROWS

Syntax

FOUND_ROWS()

Description

A SELECT statement may include a LIMIT clause to restrict the number of rows returned by the database server to the client. In some cases, you might want to know how many rows the statement would have returned without the LIMIT clause without re-executing the statement. You can use the SQL_CALC_FOUND_ROWS option in the SELECT statement and then call the FOUND_ROWS() function to get the number of rows that the statement would have returned without the LIMIT clause.

Here is an example:

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

The second SELECT statement returns a number indicating how many rows the first SELECT statement would have returned without the LIMIT clause. If the SELECT statement does not use the SQL_CALC_FOUND_ROWS option, the FOUND_ROWS() function may return different results when the statement is executed with and without the LIMIT clause.

The number of rows returned by the FOUND_ROWS() function is instantaneous and cannot be used by statements that follow the SELECT SQL_CALC_FOUND_ROWS statement. If you need to use this value later, you must save it.

Here is an example:

SELECT SQL_CALC_FOUND_ROWS * FROM ... ;
SET @rows = FOUND_ROWS();

If you are using SQL_CALC_FOUND_ROWS, the system must calculate how many rows are in the entire result set. However, this is still faster than re-executing the query without the LIMIT clause because the result set does not need to be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_ROWS() are useful when you want to limit the number of rows returned by a query but also want to know how many rows are in the entire result set without re-executing the query. For example, in a web script that provides paginated display, the display includes links to other pages of search results. Using FOUND_ROWS() allows you to determine how many additional pages are needed for the remaining results.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION queries than for simple SELECT statements because the LIMIT clause can appear in multiple places within a UNION. It may apply to individual SELECT statements within the UNION or to the entire UNION result.

The expected result of SQL_CALC_FOUND_ROWS for a UNION is to return the appropriate number of rows without a global LIMIT. The conditions for using SQL_CALC_FOUND_ROWS with a UNION are:

• The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.

• The value of FOUND_ROWS() is accurate only when using UNION ALL. If you use a UNION without the ALL keyword, there will be two deletions, and the value of FOUND_ROWS() will be approximate.

• If there is no LIMIT clause in the UNION, the SQL_CALC_FOUND_ROWS option is ignored, and the number of rows created in the temporary table to handle the UNION is returned.

Examples

SELECT SQL_CALC_FOUND_ROWS * FROM t2;
SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            3 |
+--------------+
1 row in set (0.001 sec)