The usage of sql_calc_found_rows in MySQL

1. Sql_calc_found_rows Brief Introduction

This is written in many paging programs:

#Detect the total number of records that meet the criteria
SELECT COUNT(*) from [table] WHERE ......;
#Query the data to be displayed on the page
SELECT * FROM [table] WHERE ...... limit M,N;

But starting with Mysql4.0.0, we can choose to use a different approach:

SELECT SQL_CALC_FOUND_ROWS * FROM [table] WHERE ...... limit M, N;
SELECT FOUND_ROWS();

#SQL_CALC_FOUND_ROWS tells MySQL to record the number of rows processed by sql
#FOUND_ROWS() took this record.

Although it is also two statements, but only once the main query, so the efficiency is much higher than the original.

2.sql_calc_found_rows found_rows () document in English

FOUND_ROWS()
A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns
To the client. In some cases, it is desirable to know how many rows the statement would have
Return without the LIMIT, but without running the statement again. To obtain this row count,
Include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

It is often possible to limit the number of rows returned by a LIMIT in a SELECT statement. Sometimes you may want to know how many rows will be returned if there are no LIMITs, but you don't want to execute the same again.
Statement. Then, include the SQL_CALC_FOUND_ROWS option in the SELECT query, then execute FOUND_ROWS():

Mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;

Mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned
Had it has written without the LIMIT clause.

The second SELECT will return the number of rows returned by the first SELECT if there is no LIMIT.


In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECT statement,
FOUND_ROWS() returns the number of rows in the result set returned by that statement.

If the SQL_CALC_FOUND_ROWS option is not used in the previous statement, FOUND_ROWS() will return the number of rows actually returned by the previous statement.

The row count available through FOUND_ROWS() is transient and not intended to be available
Past the statement following the SELECT SQL_CALC_FOUND_ROWS statement. If you need to
Refer to the value later, save it:

The number obtained by FOUND_ROWS() is temporary, and execution of the next statement will be invalid. If you want this number, save it:

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


If you are using SELECT SQL_CALC_FOUND_ROWS, mysql must calculate how many rows are in the full
Result set. However, this is faster than running the query again without LIMIT, because the
Result set need not be sent to the client.

If you use SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate the number of rows for all result sets. Despite this, it's better to do it again than not using LIMIT.
The query is much faster because the result set does not need to be returned to the client.


SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the
Number of rows that a query returns, but also determines the number of rows in the full result
Set without running the query again.

When you want to limit the number of rows returned by the query and you want to get the number of rows in the complete result set of the query, but you don't want to repeat the query, then
SQL_CALC_FOUND_ROWS and FOUND_ROWS() are very useful!

Usage under 3.UNION Statements

 3.1 sql_calc_found_rows can only appear in the first select of UNION, or compile an error!

mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  limit 1)  union    (select SQL_CALC_FOUND_ROWS  * from  actor limit 1);
ERROR 1234 (42000): Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS'

Case in select in 3.2 limit

#union all 
mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  limit 1)  union all   (select * from  actor limit 1);
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

#union
Mysql> (select Sql_calc_found_rows * from actor limit 1) union (SELECT * from actor limit 1);
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | Guiness | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in Set (0.00 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

You can see that when the limit is in Select, Found_rows () returns the number of rows that are displayed!

3.2 Limit in the case of an outside UNION

mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  )  union    (select   * from  actor ) order by actor_id limit 2  ;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.00 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|          200 |
+--------------+
1 row in set (0.00 sec)

mysql> (select  SQL_CALC_FOUND_ROWS  * from  actor  )  union all   (select   * from  actor ) order by actor_id limit 2  ;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.01 sec)

mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|          400 |
+--------------+
1 row in set (0.00 sec)

You can see that when the limit is outside of UNION, Found_rows () returns all the number of rows! UNION all returns twice times the number of rows

The usage of sql_calc_found_rows in MySQL