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

In a SELECT statement, it is often possible to return the number of rows with limit restrictions. Sometimes you might want to know how many rows are returned without limit, but you don't want to execute the same statement again. Then it's OK to include the sql_calc_found_rows option in the Select query, and then execute Found_rows ():

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

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

SELECT retrieves from one 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 a function that returns the exact number of records and is very easy to access. For a transaction-type storage engine (InnoDB, BDB), there are more problems storing an exact number of rows, because multiple things can happen, and each can have an impact on the number of rows.

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

Returns the number of different 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. ?>

However, using MySQL with its own function found_rows ();

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); ?>

The problem to be noticed when using this method
Reference:
1 must begin with select Sql_calc_found_rows
2 when Found_rows () is the number of rows without Limite
The following 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 (*) | +----------+ | 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>

How efficient is the method in two?