MySQL database Optimization-COUNT (*) Efficiency

Source: Internet
Author: User
Tags mysql index

The performance of the MySQL database is always the focus of discussion. The following describes the efficiency of the COUNT (*) in MySQL database optimization. I hope it will help you.

In MySQL database optimization, COUNT (*) and COUNT (COL ):
I searched the internet and found that there are various statements:
For example, COUNT (COL) is faster than COUNT;
COUNT (*) is faster than COUNT (COL;
Another funny friend said that this is actually a matter of character.

Without the WHERE restriction, the values of COUNT (*) and COUNT (COL) are equivalent;
However, with the WHERE condition, COUNT (*) is much faster than COUNT (COL;

The specific data is as follows:

Mysql & gt; select count (*) FROM cdb_posts where fid = 604;
+ ---- +
| COUNT (fid) |
+ ---- +
| 1, 79000 |
+ ---- +
1 row in set (0.03 sec)

Mysql> select count (tid) FROM cdb_posts where fid = 604;
+ ---- +
| COUNT (tid) |
+ ---- +
| 1, 79000 |
+ ---- +
1 row in set (0.33 sec)

Mysql & gt; select count (pid) FROM cdb_posts where fid = 604;
+ ---- +
| COUNT (pid) |
+ ---- +
| 1, 79000 |
+ ---- +
1 row in set (0.33 sec)

COUNT (*) is usually used to scan the primary key index, but COUNT (COL) is not necessarily the same. In addition, the former is the total number of all records in the statistical table, the latter is the number of records that match all the COL in the calculation table. There are also differences.

WHERE in COUNT
This has been written before, please see the Mysql count (*), the use of DISTINCT and efficiency of research: http://www.ccvita.com/156.html

To put it simply, if there is no WHERE limit in COUNT, MySQL will directly return the total number of rows saved.
In the case of WHERE restrictions, you always need to traverse the full table of MySQL.

Optimization summary:
1. select count (*) FROM tablename is the optimal choice under any circumstances;
2. Minimize select count (*) FROM tablename where col = 'value' queries;
3. prevent the appearance of select count (COL) FROM tablename WHERE COL2 = 'value.

Add and delete fields in MySQL

Provides you with an in-depth understanding of MySQL index types

Mysql foreign Key Usage Analysis

How MySQL defines foreign keys

New Pricing strategies for MySQL database products

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.