MySQL SELECT found_rows () differs from COUNT (*) usage

In MySQL found_rows () and COUNT (*) can be recorded, if all the same why there are two such functions, I would like to introduce the Select Found_rows () and the Count (*) Usage difference

It is often possible to return rows with limit restrictions in SELECT statements. Sometimes you may want to know how many rows will be returned without a limit, but you do not want to execute the same statement again. Then, include the Sql_calc_found_rows option in the Select query and then execute Found_rows () to do so:

The code is as follows	Copy Code
Mysql> SELECT sql_calc_found_rows * from Tbl_name WHERE ID > LIMIT 10; Mysql> SELECT found_rows ();

The slightly different of COUNT (*) is that it returns the number of rows retrieved, regardless of whether they contain null values.

SELECT retrieves from a table without retrieving other columns, and when 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 applies only to MyISAM tables, because these table types store the exact number of records returned by a function and are very easy to access. For transactional storage engines (InnoDB, BDB), the problem of storing an exact number of rows is more, because multiple-thing processing can occur, each of which can have an impact on the number of rows.

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

Returns the number of distinct non-null values.

If no matching items are 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'm not going to write this down. ?>

However, with MySQL self-found_rows function ();

You can also quickly find the total
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); ?>

Problems to be aware of when using this method
Reference:
1 must start with select Sql_calc_found_rows
2 at this point found_rows () is the number of rows without Limite
Here is a demo I hope you see more clearly
PHP Code:

The code is as follows

Copy Code

mysql> Select COUNT (*) from Zd_sort2; +----------+ | count (*) | +----------+ |       | +----------+ 1 row in Set (0.14 sec)   Mysql> Select Sql_calc_found_rows st2_id  from Zd_sort2 limit 3; +--------+ | st2_id | +--------+ |      1 |    |      6 |       |     |         +--------+ 3 Rows in Set (0.00 sec)   Mysql> Select Found_rows (); +--------------+ | found_rows () | +--------------+ |           | +--------------+ 1 row in Set (0.00 sec)   Mysql>

How efficient is the two method?

MySQL SELECT found_rows () differs from COUNT (*) usage