In MySQL, COUNT (1) is compared to count (*)

SQL tuning is primarily about reducing the number of consistent gets and physical reads.

COUNT (1) is compared with COUNT (*):

If your datasheet does not have a primary key, then count (1) is faster than COUNT (*)
If you have a primary key, the primary key (the Union primary key) is also faster than COUNT (*) as a condition for count.
If your watch has only one field, then count (*) is the fastest.
COUNT (*) count (1) is compared. The primary or the data field corresponding to count (1).
If Count (1) is a clustered index, ID, that must be count (1) fast. But the difference is very small.
Because Count (*), automatically optimizes the assigned field. So there is no need to go to count (?), with Count (*), SQL will help you to complete the optimization

Count Detailed:

COUNT (*) returns the total number of rows that exist in the table, including rows with a value of NULL, but the count (column name) returns the total number of all rows except null in the table (the columns with default values are also counted).
Distinct the column name, the result will be the result of dropping the value null and repeating data

Summary of three experiences

1. In any case select COUNT (*) from TableName is the optimal choice;
2. Minimize the query of select COUNT (*) from tablename WHERE COL = ' value ';
3. Eliminate the appearance of select COUNT (COL) from TableName.

The country is looking for an article does not understand English does not translate

The code is as follows

COUNT (*) vs count (Col) Looking at how people are using count (*) and Count (COL) it looks like most of them-I-i-they-are synonyms and just using What they happen to like, while there are substantial difference in performance and even query result. Lets look at the following series of examples: CREATE TABLE ' fact ' (   ' i ' int (a) unsigned not NULL,   ' val ' int (one) default NULL,   ' val2 ' int (a) unsigned not NUL L,   KEY ' i ' (' I ') Engine=myisam DEFAULT charset=latin1 mysql> Select COUNT (*) from fact; + ———-+ | count (*) | + ———-+ |  7340032 | + ———-+ 1 row in Set (0.00 sec) Mysql> Select count (val) from fact; +- ——— + | count (val) | + ———— + |    7216582 | + ———— + 1 row in Set (1.17 sec) Mysql> Select count (val2) from Fact + ————-+ | count (val2) | + ————-+ |     7340032 | + ————-+ 1 row in Set (0.00 SE c)

As is MYISAM table MySQL has cached number of rows in this table. This is why it are able to instantly answer COUNT (*) and
COUNT (val2) queries, but not count (Val). Why? Because Val column isn't defined as NOT null there can some NULL values in it and so MySQL have to perform table scan To find out. This is also why the different to the second query.

So COUNT (*) and COUNT (COL) queries not only could have substantial performance performance differences but also ask differ ent question.

MySQL Optimizer does good job in this case doing full table scan only if it's needed because column can be NULL.

Now lets try few more queries:

The code is as follows

Mysql> Select COUNT (*) from fact where i<10000; + ———-+ | COUNT (*) | + ———-+ | 733444 | + ———-+ 1 row in Set (0.40 sec) Mysql> Explain select COUNT (*) from fact where i<10000 G 1. Row *************************** Id:1 Select_type:simple Table:fact Type:range Possible_keys:i Key:i Key_len:4 Ref:null rows:691619 Extra:using where; Using Index 1 row in Set (0.00 sec) Mysql> Select COUNT (val) from fact where i<10000; + ———— + | Count (val) | + ———— + | 720934 | + ———— + 1 row in Set (1.29 sec) Mysql> Explain select count (val) from fact where i<10000 G 1. Row *************************** Id:1 Select_type:simple Table:fact Type:range Possible_keys:i Key:i Key_len:4 Ref:null rows:691619 Extra:using where 1 row in Set (0.00 sec) Mysql> Select COUNT (val2) from fact where i<10000; + ————-+ | Count (val2) | + ————-+ | 733444 | + ————-+ 1 row in Set (1.30 sec) Mysql> Explain select count (val2) from fact where i<10000 G 1. Row *************************** Id:1 Select_type:simple Table:fact Type:range Possible_keys:i Key:i Key_len:4 Ref:null rows:691619 Extra:using where 1 row in Set (0.00 sec)

As you can-even if you have WHERE clause performance for COUNT (*) and Count (Col) can is significantly different. In fact this example shows just 3 times performance difference because all data fits in memory, for IO bound workloads Frequently can and even times performance difference in this case.

The thing is count (*) query can be covering index even while count (col) can ' t. Of course can extend index to IS (i,val) and get query to is index covered again but I would use this workaround only If you can ' t change the query (ie it's third party application) or in case column name is in query for reason, and Yo U really need count of non-null values.

It is worth to Optimizer does don't do too good job optimizing the query. One could notice (val2) column is not NULL so count (VAL2) are same as COUNT (*) and so this query could be run as index cover Ed Query. It does not and both queries have to perform row reads in the.

code is as follows
mysql> ALTER TABLE fact drop key I, add key (I,val); Query OK, 7340032 rows Affected (37.15 sec) records:7340032  duplicates:0  warnings:0 mysql> Select COUNT (val) from fact where i<10000; + ———— + | count (val) | + ———— + |     720934 | + ———— + 1 row in Set (0.78 sec)