Detailed explanation of the correct use of the count function in MySQL

1. Description

In MySQL, when we need to get the total number of rows in a table, we usually choose to use the following statement

select count(*) from table;

In fact, in addition to *, other parameters can be placed in the count function, such as constants, primary key IDs, and fields. So what is the difference between them? How efficient are they? Which method should we use to get the number of rows in the table?

Once you understand the operating principle of the count function, I believe the answers to the above questions will become clear.

2. Table structure

To solve the above problem, I created a user table with two fields: primary key id and name, the latter can be null. The table creation statement is as follows.

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
 `name` varchar(255) DEFAULT NULL COMMENT 'Name',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are 6,000,000 records in this table. The name field of the first 1,000,000 records is empty, and the remaining records have name=id. The code to create test data using a stored procedure is as follows

-- Use stored procedures to create test data delimiter;;
create procedure idata()
begin 
 declare i int; 
 set i=1; 
 while(i<=6000000)do 
  insert into user values(i, i);
  set i=i+1; 
 end while;
end;;
delimiter;
call idata();
-- Set the name field of the first 1,000,000 records to null
update user set name=null where id<1000000;

3. Execute SQL statements and results

In order to distinguish the differences between different parameters of the count function, the execution efficiency of SQL is mainly described from the two aspects of execution time and number of scanned rows. The characteristics of the count function are also described from the returned results.

• * symbol—— select count(*) from user ;
• Constant - select count(1) from user ;
• Non-empty fields—— select count(id) from user ;
• Nullable fields - select count(name) from user ;
  

3.1 * Symbol

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 6000000 |
+----------+
1 row in set (0.76 sec)

Traverse the entire table, do not take values ​​(after optimization, it must not be null, do not take values), accumulate counts, and finally return the results.

3.2 Constants

mysql> select count(1) from user;
+----------+
| count(1) |
+----------+
| 6000000 |
+----------+
1 row in set (0.76 sec)

Traverse the entire table, fetch data row by row, assign each row a value of 1, determine that the field cannot be empty, accumulate the count, and finally return the result.

3.3 Non-empty fields

mysql> select count(id) from user;
+-----------+
| count(id) |
+-----------+
| 6000000 |
+-----------+
1 row in set (0.85 sec)

Traverse the entire table, fetch data row by row (the smallest index tree will be selected for traversal, so it is more efficient than counting fields in the same situation), fetch the primary key id of each row, determine that the field cannot be empty, accumulate the count, and finally return the result.

3.4 Nullable fields

mysql> select count(name) from user;
+-------------+
| count(name) |
+-------------+
| 5900001 |
+-------------+
1 row in set (0.93 sec)

• If the field definition is not empty: traverse the entire table, fetch data row by row, fetch the field of each row, determine that the field cannot be empty, accumulate the count, and finally return the result.
• If the field definition is nullable: traverse the entire table, fetch data row by row, fetch the field in each row, determine if the field may be null, and then determine whether the value of the field is null. If it is not null, accumulate the count and finally return the result.
• If the field has no index, the primary key index tree will be traversed.
  

4. Execution result analysis

4.1 Result Set

First of all, from the perspective of the result set, the purpose of the first three SQL statements is the same - to return the number of all rows, and when the parameter of count function is a normal field and the field defaults to null , it returns the number of rows in which the field is not null .

4.2 Execution Time

From the execution time point of view, the efficiency is roughly count(可為空的字段) < count(非空字段) < count(常數) < count(*) .

5. Conclusion

Count is an aggregate function. For the returned result set, it is judged row by row. If the parameter of the count function is not NULL, the cumulative value is increased by 1, otherwise it is not increased. Finally, the accumulated value is returned.

• The reason why count(*) is the fastest is that it does not take the value of each row of data when counting.
• The reason count(1) is slightly slower than count(*) is that it takes each row and assigns a value of 1 to it.
• The reason why count(非空字段) is slightly slower than count(1) is that it extracts the primary key id from each data row.
• The reason why count(可為空的字段) is the slowest is that it may need to determine whether the field in each data row is null.
  

Therefore, it is better to use count(*).

The above is the detailed content of the correct use of the count function in MySQL. For more information about the MySQL count function, please pay attention to other related articles on 123WORDPRESS.COM!