Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to optimize select count in Mysql, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. SELECT COUNT (*) FROM tablename is the best choice in any case.

two。 Minimize queries such as SELECT COUNT (*) FROM tablename WHERE COL = 'value'

3. Put an end to SELECT COUNT (COL) FROM tablename.

COUNT (*) and COUNT (COL)

After searching the Internet, I found that there are all kinds of theories:

For example, those who think that COUNT (COL) is faster than COUNT (*)

Those who think that COUNT (*) is faster than COUNT (COL)

There are friends who are very funny when it comes to this, it actually depends on character.

Without WHERE restriction, COUNT (*) and COUNT (COL) can be considered to be equivalent.

But with WHERE restrictions, COUNT (*) is much faster than COUNT (COL).

The specific data are as follows:

> SELECT COUNT (*) FROM cdb_posts where fid = 604

+-- +

| | COUNT (fid) |

+-- +

| | 79000 |

+-- +

1 row in set (0.03 sec)

Mysql > SELECT COUNT (tid) FROM cdb_posts where fid = 604

+-- +

| | COUNT (tid) |

+-- +

| | 79000 |

+-- +

1 row in set (0.33 sec)

Mysql > SELECT COUNT (pid) FROM cdb_posts where fid = 604

+-- +

| | COUNT (pid) |

+-- +

| | 79000 |

+-- +

1 row in set (0.33 sec)

COUNT (*) usually scans the index of the primary key, while COUNT (COL) is not necessarily. In addition, the former is the total number of records of all matches in the statistical table, while the latter is the number of records of all COL matches in the table. There's a difference.

WHERE in COUNT

To put it simply, in the case of COUNT, if there is no WHERE limit, MySQL directly returns the total number of rows saved

In the case of WHERE restrictions, it is always necessary to do a full table traversal of MySQL.

Optimization summary:

1. SELECT COUNT (*) FROM tablename is the best choice in any case.

two。 Minimize queries such as SELECT COUNT (*) FROM tablename WHERE COL = 'value'

3. Put an end to SELECT COUNT (COL) FROM tablename.

Thank you for reading this article carefully. I hope the article "how to optimize select count in Mysql" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.