Syntax
FOUND_ROWS()
Purpose
When you execute a SELECT statement, you can specify a LIMIT clause to restrict the number of rows returned from the database server to the client. In some cases, you do not need to execute the statement again to determine the number of rows returned by this statement with the absence of LIMIT. You can use SQL_CALC_FOUND_ROWS in the SELECT statement, and then call the FOUND_ROWS() function to determine the number of rows returned by this statement with the absence of LIMIT.
Here is 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, which indicates the number of rows returned by the first SELECT statement with the absence of the LIMIT clause. If you do not use SQL_CALC_FOUND_ROWS in the preceding SELECT statement, FOUND_ROWS() may return different results when the LIMIT clause is or is not used.
The number of valid rows returned by FOUND_ROWS() is instantaneous and cannot skip the statement after SELECT SQL_CALC_FOUND_ROWS. Save the value if you need to use it later.
Here is 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 all result sets. However, this is still faster than running the query again without using the LIMIT clause, because the result sets do not need to be sent to the client.
SQL_CALC_FOUND_ROWS and FOUND_ROWS() are very useful when you want to limit the number of rows returned by a query. You can also determine the number of rows in all the result sets without running the query again. For example, if a Web script presents a paged display containing links to the pages that show other sections of a search result You can use FOUND_ROWS() to determine the number of other pages that are needed to display the remaining results.
The application of SQL_CALC_FOUND_ROWS and FOUND_ROWS() for simple SELECT statements is more complex than the application for the UNION query. This is because the LIMIT clause may appear in multiple positions in UNION. It may apply to independent SELECT statements in UNION or the entire UNION result.
SQL_CALC_FOUND_ROWS expects UNION to return the number of rows that must be returned when no global LIMIT clause exists. Conditions for using SQL_CALC_FOUND_ROWS and UNION in conjunction are as follows:
The
SQL_CALC_FOUND_ROWSkeyword must appear in the firstSELECTstatement ofUNION.The value of
FOUND_ROWS()is accurate only whenUNION ALLis used. If you use aUNIONstatement without specifying the ALL option, the delete operation occurs twice, and the value ofFOUND_ROWS()only needs to be approximate.If the
UNIONstatement does not contain aLIMITclause,SQL_CALC_FOUND_ROWSis ignored, and the number of rows created in the temporary table forUNIONis returned.
Examples
obclient> SELECT SQL_CALC_FOUND_ROWS * FROM t2;
obclient> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 3 |
+--------------+
1 row in set