Difference between count (*) and count (id) in mysql

Mysql queries the number of records by page or under a certain condition, usually count (), so you know count (*), count (id), or count (name) are there any differences?

 

Take a look at the figure below and you will understand it:

 

 

Count (*) indicates the number of all records, including NULL data. While count (a field) only calculates the number of valid data records. This is the main difference between them.

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 point has been written before. For details, see count (*) and DISTINCT usage and efficiency in Mysql.
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.
 

In MySQL5, select count (*) and select count (id) are identical.

The table contains more than 3 million records.

It took 0.8 seconds, but when I used explain to view the query plan, I found that it did not use the primiary index, so it was forced to use the primary key index, and the result took 7 seconds!
I carefully studied the index used by default. It actually uses an index on bit (1). This field is a flag.

After thinking about it, it is estimated that this index is the smallest, based on bit (1), and the primary key is based on int (4), and all indexes can be used to calculate count (*), because a record always corresponds to an index element.

Therefore, there is a small tip for the Author: If your table has a field similar to a flag (such as whether to delete it logically), an index will be created on it and count (*) the speed is increased several times. Of course, it is better to use the bit (1) type instead of int or char (1) to save the flag bit, which will be slower.

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.