The difference between count (Primary_key), COUNT (1), COUNT (*) in MySQL

The table structure is as follows:

Mysql> Show CREATE TABLE user\g;*************************** 1. Row ***************************       table:usercreate table:create Table ' user ' (  ' id ' int (ten) unsigned not NULL AUTO _increment,  ' name ' varchar (NOT null),  ' pwd ' varchar (') ' is not null,  ' email ' varchar (+) NOT NULL,  ' Phone ' varchar ' NOT null,  ' sex ' enum (' F ', ' M ', ' n ') is not null DEFAULT ' N ',  ' addres ' varchar (+) NOT null,  ' t AG ' varchar ' not NULL,  PRIMARY key (' id '),  key ' name ' (' name ')) Engine=innodb auto_increment=5000003 DEFAULT charset=utf8 comment= ' user table ' 1 row in Set (0.00 sec)

Let's do Explain:1, COUNT (ID)

Mysql> select COUNT (id) from user;+-----------+| Count (ID) |+-----------+|   5000002 |+-----------+1 row in Set (1.93 sec)

Mysql> Explain select count (ID) from user;+----+-------------+-------+-------+---------------+------+---------+- -----+---------+-------------+

| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows    | Extra       |

+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+|  1 | Simple      | user  | index | NULL          | name |     NULL | 4998401 | Using Index |

+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+

1 row in Set (0.05 sec)

2, COUNT (1)

Mysql> Select COUNT (1) from user;+----------+| Count (1) |+----------+|  5000002 |+----------+1 row in Set (0.90 sec)

Mysql> Explain select COUNT (1) from user;+----+-------------+-------+-------+---------------+------+---------+-- ----+---------+-------------+| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ |  1 | Simple      | user  | index | NULL          | name |     NULL | 4998401 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+---------+------------- +1 row in Set (0.00 sec)

3. Count (*)

Mysql> Select COUNT (*) from user;+----------+| COUNT (*) |+----------+|  5000002 |+----------+1 row in Set (0.87 sec)

Mysql> Explain select COUNT (*) from user;+----+-------------+-------+-------+---------------+------+---------+-- ----+---------+-------------+| ID | Select_type | Table | Type  | possible_keys | key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ |  1 | Simple      | user  | index | NULL          | name |     NULL | 4998401 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+---------+------------- +1 row in Set (0.00 sec)

Comparing three queries, the results of explain are identical, which shows that the three efficiency is the same?

Take a look at the following three operations, with the Where Condition sex= ' F ', the MySQL service will be restarted in the middle of the following three operations.

1. Count (ID)

Mysql> select COUNT (id) from user where sex= ' F '; +-----------+| Count (ID) |+-----------+|   1681259 |+-----------+1 row in Set (18.87 sec) mysql> Explain select count (ID) from user where sex= ' F '; +----+----------- --+-------+------+---------------+------+---------+------+---------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | Simple      | user  | All  | NULL          | NULL | NULL    | NULL | 4998401 | Using where |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in Set (0.00 sec)

2, COUNT (1)

Mysql> Select COUNT (1) from user where sex= ' F '; +----------+| Count (1) |+----------+|  1681259 |+----------+1 row in Set (4.81 sec) mysql> Explain select COUNT (1) from user where sex= ' F '; +----+-------------+ -------+------+---------------+------+---------+------+---------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | Simple      | user  | All  | NULL          | NULL | NULL    | NULL | 4998401 | Using where |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in Set (0.00 sec)

3. Count (*)

Mysql> Select COUNT (*) from user where sex= ' F '; +----------+| COUNT (*) |+----------+|  1681259 |+----------+1 row in Set (4.69 sec) mysql> Explain select count (*) from user where sex= ' F '; +----+-------------+ -------+------+---------------+------+---------+------+---------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | Simple      | user  | All  | NULL          | NULL | NULL    | NULL | 4998401 | Using where |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ 1 row in Set (0.00 sec)

There are some differences between the three queries above, where count (ID) takes the longest and count (*) is slightly faster than count (1).

Two sets of queries, none of which were used to index, scanned the whole table, and the index name was used without conditions.

So try not to use COUNT (*) and COUNT (1) in your application to eliminate the use of Count (Primary_key).

There's a lot of information online.

No primary key, COUNT (1) is faster than count (*);

With the primary key, COUNT (Primary_key) is the fastest, but in the above test it is found that count (Primary_key) is the slowest, is the test inaccurate? This remains to be verified.

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

Description

The 1 in count (1) does not refer to the first column;

COUNT (*) is the same as Count (1), which includes statistics for null values;

Count (column) does not include a statistic that has a value of NULL, where column refers to not primary_key;

This article is from the Mail, http://www.hblpf.com/?post=55