FOUND_ROWS

Syntax

FOUND_ROWS()

Purpose

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 may 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 a SELECT statement and then call the FOUND_ROWS() function to obtain the number of rows that the statement would have returned without the LIMIT clause.

Here's an example:

obclient> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
     WHERE id > 100 LIMIT 10;
obclient> 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 used with LIMIT and without LIMIT.

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

Here's an example:

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

If you are using SQL_CALC_FOUND_ROWS, the system must calculate the total number of rows in the result set. However, this is faster than re-running the query without LIMIT 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 while also being able to determine the total number of rows in the result set without re-executing the query. For example, in a web script that provides paginated search results, you can use FOUND_ROWS() to determine how many additional pages are needed to display the remaining results.

Using SQL_CALC_FOUND_ROWS and FOUND_ROWS() with UNION queries is more complex than with simple SELECT statements because LIMIT can appear in multiple locations 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 with 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 ALL, there will be two deletions, and the value of FOUND_ROWS() will be approximate.

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

Examples

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