Count (*), COUNT (1) and count (field) differ

In the impression, Count (key) is more efficient than count (*), so the number of rows is counted in the project in Count (field). In the code Reivew was pointed out that the application of count (*), and then checked, and did a simple test, it is true that I remember wrong, it is evident that the code review how useful ah. Count (*)

COUNT (*) is a column that is not NULL, so a row is counted as long as not all columns are null (that is, records that exist).

MySQL uses explain to view its execution plan, and COUNT (*) tries to improve performance with indexes with the following characteristics: NOT NULL column fields are narrower

As shown in the figure, the table primary key ID is the bigint type, and the gst_id index of type int is automatically selected when count (*). count (field)

Count (field) is a row that is not null for a field column, so it is not counted if it is null for a row.

Also using explain to view its execution plan, count (field) also tries to use indexes to improve performance, and temporarily discovers the following two scenarios: If the index containing the field is changed to field as the primary key, a narrower index is selected, like count (*). This is the same as using count (*)

This shows that counting with count (field) has the following problem: If the field has a null record and is intended to be used to count all records, the result is incorrect if the field has no index or is not an optimal index, the efficiency is lower count (1)

Also see count (1) usage. The validation in MySQL does not find a significant difference from count (*) and is considered to be the same. Summary

COUNT (*) and COUNT (1) are not significantly different, and the use of Count (field) incorrectly can cause errors or performance problems and is not recommended.

In addition, if a where statement is included, the conditional index in the where will be preferred, and the execution plan discussed above should not make much sense.