PHP Learning Path: 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:

code is as follows	copy code
mysql> Select Sql_calc_found_rows * from Tbl_name      , WHERE ID > LIMIT; 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:

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 &NBSP;      $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 (*) | +----------+ | 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 two method?

found_rows ()

A SELECT statement may include a limit clause that restricts the number of rows that the server returns to the client. In some cases, it is not necessary to run the statement again to know how many rows the statement returned without a limit. To know the number of rows, include selecting Sql_calc_found_rows in the SELECT statement, and then calling Found_rows ():

mysql> SELECT sql_calc_found_rows * from tbl_name

WHERE ID > LIMIT ten;

Mysql> SELECT found_rows ();

The second select Returns a number indicating the number of rows returned by the first select without the limit clause (if the SELECT statement above does not include the sql_calc_found_rows option, limit is used and not used, found_ ROWS () may return different results).

The valid number of rows through Found_rows () is instantaneous and is not used to cross the statement following the Select Sql_calc_found_rows statement. If you need to see this value later, 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 a small number of rows in all result sets. However, this is faster than running the query again without limit, because the result set does not need to be sent to the client.

Sql_calc_found_rows and Found_rows () are useful when you want to limit the number of rows returned by an inquiry, and you can determine the number of rows in all result sets without having to run the query again. An example is a Web script that provides a page display that contains a connection to a page that displays other parts of the search results. Using Found_rows () lets you determine how many other pages are needed for the rest of the results.

The application of Sql_calc_found_rows and Found_rows () is more complex for union queries than for simple SELECT statements, because in union, LIMIT may appear in multiple locations. It may apply to a personal SELECT statement in the Union, or to the whole of the Union result.

Sql_calc_found_rows's intent on union is that it should not need to return the number of rows returned by the full limit. The conditions used together with the Sql_calc_found_rows and Union are:

• sql_calc_found_rows keyword must appear in the first select of Union.
• The value of found_rows () is accurate only when the UNION all is used. If you use the union without all, two deletions occur, and the found_rows () refers to only approximate.
• If no LIMIT is present in the Union, Sql_calc_found_rows is ignored, returning the number of rows created in the staging table to handle the union.