Usage differences for COUNT (ID) count (1) count (*) in MySQL

The table structure is as follows:

The code is as follows

Copy Code

Mysql> Show CREATE TABLE Userg; 1. Row *************************** Table:user Create table:create Table ' user ' ( ' ID ' int (a) unsigned not NULL auto_increment, ' Name ' varchar not NULL, ' pwd ' varchar not NULL, ' Email ' varchar not NULL, ' Phone ' varchar not NULL, ' Sex ' enum (' F ', ' M ', ' n ') not NULL DEFAULT ' n ', ' addres ' varchar not NULL, ' tag ' 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 the Explain:1, COUNT (ID)

The code is as follows

Copy Code

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 | 152 | 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 | 152 | 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 | 152 | NULL | 4998401 | Using Index | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ 1 row in Set (0.00 sec)

Comparing three queries, the results of the 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 following three operations will restart the MySQL service.

The code is as follows

Copy Code

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 the 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)

The above three queries have some differences, where count (ID) is the longest, and count (*) is slightly faster than count (1).
The two groups of queries, with none of the criteria used to the index, scanned the entire table, and the index name was used without conditions.

So do not use COUNT (*) and COUNT (1) as much as possible in your application, so that count (Primary_key) is eliminated.

There's a lot of information on the web.

COUNT (1) is faster than count (*) without a primary key;

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

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

Description

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

COUNT (*) is the same as Count (1), including statistics that have null values;

Count (column) does not include statistics that are null for the value, and the column here does not refer to Primary_key;