Comparison between count (1) and count (*) in mysql

Count details:

Count (*) will return the total number of all existing rows in the table, including those with a value of null, while count (column name) will return the total number of all rows in the table except null (columns with default values will also be counted).

Distinct column name, the result will be the result after removing the value of null and duplicate data

Sql tuning is mainly about reducing the number of consistent gets and physical reads.

Count (1) compares with count (*):

If your datasheet does not have a primary key, then count (1) is faster than count (*)

If there is a primary key, the condition of the primary key (joint primary key) as count is also faster than that of count (*).

If your table has only one field, then count (*) is the fastest.

Count (*) count (1) compare the two. The main thing is to count (1) the corresponding data field.

If count (1) is a clustered index, id, that must be count (1) fast. But the difference is very small.

Because of count (*), which field is automatically optimized for assignment. So there is no need to go to count (?), use count (*), sql will help you complete the optimization

Sum up three experiences

1. SELECT COUNT (*) FROM tablename is the best choice in any case.

two。 Minimize queries such as SELECT COUNT (*) FROM tablename WHERE COL = 'value'

3. Put an end to SELECT COUNT (COL) FROM tablename.