Difference between mysqlSELECTFOUND_ROWS () and COUNT (*) _ MySQL

The difference between mysqlSELECTFOUND_ROWS () and COUNT (*) is that both FOUND_ROWS () and COUNT (*) in mysql can collect statistics records. if both are the same, why are there two such functions, the following describes the usage differences between SELECT FOUND_ROWS () and COUNT (*).

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:
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:
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.

The code is as follows:
$ 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 PHP code:

The code is as follows:
$ 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:

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>

FOUND_ROWS ()

The a select statement may include a limit clause to LIMIT the number of rows that the server returns to the client. In some cases, you do not need to run the statement again to know how many lines are returned when there is no LIMIT. To know the number of rows, SELECT SQL _CALC_FOUND_ROWS in the SELECT statement, and then call FOUND_ROWS ():

Mysql>SELECT SQL _CALC_FOUND_ROWS * FROMTbl_name

->WHERE id & gt; 100 LIMIT 10;

Mysql>SELECT FOUND_ROWS ();

The second SELECT statement returns a number indicating the number of rows returned by the first SELECT statement without the LIMIT clause (if the preceding SELECT statement does not include the SQL _CALC_FOUND_ROWS option, when LIMIT is used or not used, FOUND_ROWS () may return different results ).

The valid number of rows through FOUND_ROWS () is instantaneous and is not used for statements beyond 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 re-running the query 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 the result sets without running the query again. An example is a webpage-based Web script that contains links to pages displaying other parts of the search results. Using FOUND_ROWS () enables you to determine the number of other pages required for the remaining results.

The application of SQL _CALC_FOUND_ROWS and FOUND_ROWS () is more complex for UNION queries than for simple SELECT statements, because LIMIT may appear in multiple locations in UNION. It may be applicable to the individual SELECT statement in the UNION statement or the entire process from the overall to the UNION result.

SQL _CALC_FOUND_ROWS is intended for UNION because it should return the number of rows to be returned without full LIMIT. The conditions used by SQL _CALC_FOUND_ROWS and UNION are:

• The SQL _CALC_FOUND_ROWS keyword must appear in the first SELECT of UNION.
• The value of FOUND_ROWS () is accurate only when union all is used. If UNION without ALL is used, two deletions will occur, while FOUND_ROWS () only requires an approximation.
• If LIMIT does not appear in UNION, SQL _CALC_FOUND_ROWS is ignored and the number of rows created in the temporary table to process UNION is returned.