Declaration
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 may need to know how many rows the statement would have returned without the LIMIT clause without running it again. You can use SQL_CALC_FOUND_ROWS in the SELECT statement and then call the FOUND_ROWS() function to obtain the number of rows that would have been returned if no LIMIT clause was present.
As shown in the following 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 if no LIMIT clause were present. If the preceding SELECT statement does not use the SQL_CALC_FOUND_ROWS option, FOUND_ROWS() may return different results when LIMIT is used versus when it is not.
The number of valid 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.
As shown in the following example:
obclient> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;
obclient> SET @rows = FOUND_ROWS();
If you are using SQL_CALC_FOUND_ROWS, the system must determine how many rows are in the entire result set. Nevertheless, this is still faster than running the query again 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 still being able to determine the total number of rows in the result set without running the query again. For example, in web scripts that provide paginated displays, such as showing links to pages containing other parts of search results. Using FOUND_ROWS() allows you to determine how many additional pages are needed to display the remaining results.
The application of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNION queries than for simple SELECT statements. This is because in a UNION, the LIMIT clause may appear in multiple locations. It can apply to individual SELECT statements within the UNION or to the entire UNION result set.
The expected result of SQL_CALC_FOUND_ROWS for a UNION is to return the expected number of rows in the absence of a global LIMIT. The conditions for using SQL_CALC_FOUND_ROWS with UNION are:
The
SQL_CALC_FOUND_ROWSkeyword must appear in the firstSELECTstatement of theUNION.The value of
FOUND_ROWS()is accurate only whenUNION ALLis used. IfUNIONwithoutALLis used, two deletions occur, and the value ofFOUND_ROWS()is approximate.If
LIMITis not specified in theUNION,SQL_CALC_FOUND_ROWSis ignored and the number of rows created in the temporary table to handle theUNIONis returned.
Examples
obclient> SELECT SQL_CALC_FOUND_ROWS * FROM t2;
obclient> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 3 |
+--------------+
1 row in set