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

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.