Mysql Optimization-COUNT (*) Efficiency Analysis

I just solved the Discuz he wrote for a friend! Plug-in problems, when talking about the efficiency of MySQL COUNT (*), the more I find it unclear, simply write it down and share it with you.

I just solved the Discuz he wrote for a friend! Plug-in problems, when talking about the efficiency of MySQL COUNT (*), the more I find it unclear, simply write it down and share it with you.

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:

The Code is as follows:

> 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 solution for COUNT

By the way, mysql's DISTINCT keywords are of a lot of unexpected use.
1. It can be used when count does not repeat records
For example

The Code is as follows:
Select count (DISTINCT id) FROM tablename;

Calculate the number of records with different IDs in the talbebname table.

2. You can use
For example

The Code is as follows:
Select distinct id FROM tablename;

Returns the specific values of different IDs in the talbebname table.

3. In case 2 above, there will be ambiguity when you need to return results with more than two columns in the mysql table
For example

The Code is as follows:
Select distinct id, type FROM tablename;

In fact, the returned result is a result of different IDs and types, that is, DISTINCT serves two fields at the same time. It must be the same id and tyoe to be excluded. It is different from the expected result.
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 for the MyISAM table:

The Code is as follows:
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.