You may use a LIMIT clause in a SELECT statement to limit the number of rows that are returned from the database server to the client. In some cases, you need to retrieve the actual number of rows that the statement returns if the statement does not have the LIMIT clause. If you do not want to execute the statement again, you can use SQL_CALC_FOUND_ROWS in the SELECT statement. In this case, you can invoke the FOUND_ROW() function to retrieve the actual number of rows that are returned by the SELECT statement that does not have the LIMIT clause.
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS();
The second SELECT statement returns a number that indicates how many rows are returned by the first SELECT statement if the first statement does not have the LIMIT clause. If the preceding SELECT statements do not use the SQL_CALC_FOUND_ROWS option, the results of FOUND_ROWS() may differ based on whether the LIMIT clause is used.
For the SELECT SQL_CALC_FOUND_ROWS statement in the preceding example, the returned value of the FOUND_ROWS() function is valid only for a short period. The returned value becomes invalid when the statement that follows the SELECT SQL_CALC_FOUND_ROWS statement is executed. If you need to use the returned number of rows later, you must save the number.
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ; mysql> SET @rows = FOUND_ROWS();
If you use SQL_CALC_FOUND_ROWS in a query, the system calculates the number of rows in the full result set. This process requires less time than the process of running another query that does not use the LIMIT clause. This is because the result set in the former process does not need to be sent to the client.
Assume that you want to limit the number of rows that a query returns and do not want to run another query to retrieve the number of rows in the full result set. In this case, you can use SQL_CALC_FOUND_ROWS and FOUND_ROWS(). For example, you can use a web script for a paged display. The displayed information contains the links to pages for the other parts of the query results. You can use the FOUND_ROWS() function to determine the number of additional pages that are required to show the remaining results.
The implementation of SQL_CALC_FOUND_ROWS and FOUND_ROWS() in UNION queries is more complex than that in simple SELECT statements. This is because a UNION query may contain one or more LIMIT clauses. For example, you may use the LIMIT clauses in the SELECT statements of a UNION query, or use the clauses to limit the UNION results.
If SQL_CALC_FOUND_ROWS is used in a UNION query, the expected return result is the row count that is not limited by the global LIMIT clause. If you need to use SQL_CALC_FOUND_ROWS in a UNION query, ensure that the following requirements are met:
- The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT statement of the UNION query.
- The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.
- If the UNION query does not contain the LIMIT clauses, SQL_CALC_FOUND_ROWS is ignored. In this case, the query returns the number of rows in the temporary table that is created to process the UNION query.
This function returns the auto-increment field value that is latest inserted in the current session. If you insert multiple rows into the table in the latest operation, the LAST_INSERT_ID() function returns the auto-increment field value of the first row.
mysql>select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec)