MySQL optimized count (*) efficiency

MySQL optimized count (*) efficiency
just to a friend to solve the problem he wrote discuz! plug-in, talking about the efficiency of MySQL count (*), found that the more said more unclear, simply write down and share to everyone.

COUNT (*) with COUNT (COL)
Search the Internet, found that all kinds of statements are:
For example, think that count (COL) is faster than count (*);
Think that count (*) is faster than count (COL);
And friends are funny to say that this is actually to see the character.

The Count (*) and COUNT (COL) are basically considered equivalent without the where restriction ;
However, if there is a WHERE constraint , COUNT (*) is much faster than count (COL);

The specific data are as follows:

Mysql> 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 (*) is typically an index scan of the primary key, and COUNT (COL) is not necessarily the same, and the former is the total number of records in the table that are all in compliance, and the latter is the number of records that calculate all the compliant COL in the tables. And there's a difference.

Where when Count is
This has been written before, detailed see "MySQL count (*), distinct use and efficiency research": http://www.ccvita.com/156.html

Simply put, count, if there is no where limit, MySQL directly return to save the total number of rows
In the case where there is a where limitation, it is always necessary to perform a full table traversal of MySQL.

optimization Summary, for MyISAM tables :
1. Select COUNT (*) from TableName is the best choice in any case;
2. Minimize Select COUNT (*) from tablename WHERE COL = ' value ' such queries;
3. Eliminate select COUNT (COL) from tablename WHERE COL2 = ' value ' appears.

 

From for notes (Wiz)

MySQL optimized count (*) efficiency