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

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.