Usage differences between mysql SELECT FOUND_ROWS () and COUNT (*)

In mysql, both FOUND_ROWS () and COUNT (*) can collect statistical records. If both are the same, why are there two such functions? Next I will introduce SELECT FOUND_ROWS () and COUNT (*) usage differences

In SELECT statements, LIMIT is often used to LIMIT the number of returned rows. Sometimes you may want to know how many rows will be returned without LIMIT, but you do not want to execute the same statement again. Then, the SELECT query contains the SQL _CALC_FOUND_ROWS option, and then execute FOUND_ROWS:

The Code is as follows:	Copy code
Mysql> SELECT SQL _CALC_FOUND_ROWS * FROM tbl_name -> WHERE id> 100 LIMIT 10; Mysql> SELECT FOUND_ROWS ();

The slight difference between COUNT (*) is that it returns the number of retrieved rows, whether or not it contains NULL values.

When SELECT is retrieved from a table, but no other columns are retrieved, and there is no WHERE clause, COUNT (*) is optimized to the fastest return speed. For example:

The Code is as follows:	Copy code
Mysql> select count (*) FROM tablename;

This optimization only applies to MyISAM tables because these table types store the exact number of records returned by a function and are very easy to access. For transaction-type storage engines (InnoDB and BDB), there are many problems with storing a precise number of rows, because multiple transactions may be processed, and each row may have an impact on the number of rows.

COUNT (DISTINCT expr, [expr...])

Returns the number of non-NULL values.

If no matching item is found, COUNT (DISTINCT) returns 0.

PHP code:

The Code is as follows:	Copy code
<? Php $ SQL = "select count (*) from t "; $ Res = mysql_query ($ SQL ); $ Num = mysql_result ($ res, 0 ); $ SQL = "select topic, detail from t limit 5 "; // *** I will not write the following: ?>

However, use the built-in mysql function found_rows ();

You can also quickly find the total number.
PHP code:

The Code is as follows:	Copy code
<? Php   $ SQL = "select SQL _calc_found_rows topic, detail from t limit 5 "; $ SQL = "select found_rows ()"; $ Num = mysql_result ($ res, 0 ); ?>

Notes for using this method
Reference:
1 must start with select SQL _calc_found_rows
2. found_rows () indicates the number of rows without limite.
The following is a demonstration. I hope you can better understand it.
PHP code:

The Code is as follows:

Copy code

Mysql> select count (*) from zd_sort2; + ---------- + | Count (*) | + ---------- + | 20 | + ---------- + 1 row in set (0.14 sec)   Mysql> select SQL _calc_found_rows st2_id from zd_sort2 limit 3; + -------- + | St2_id | + -------- + | 1 | | 6 | | 12 | + -------- + 3 rows in set (0.00 sec)   Mysql> select found_rows (); + -------------- + | Found_rows () | + -------------- + | 20 | + -------------- + 1 row in set (0.00 sec)   Mysql>

What is the efficiency of the two methods?