Detailed explanation of the execution differences between count(1), count(*) and count(column name)

Implementation effect:

1. count(1) and count(*)

When the amount of data in the table is large, after analyzing the table, using count(1) takes longer than using count(*)!

From the execution plan, count(1) and count(*) have the same effect. However, after analyzing the table, count(1) will take less time than count(*) (for data within 10,000 items), but the difference is not much.

If count(1) is a clustered index, id, then count(1) is definitely faster. But the difference is very small.

Because count(*) will automatically optimize to that field. So there is no need to count(1), use count(*), sql will help you optimize. Therefore: there is basically no difference between count(1) and count(*)!

2. count(1) and count(field)

The main difference between the two is

(1) count(1) will count the number of all records in the table, including records where the fields are null.

(2) count(field) will count the number of times the field appears in the table, ignoring the case where the field is null. That is, records whose fields are null are not counted.

The difference between count(*) and count(1) and count(column name)

Execution effect:

count(*) includes all columns, which is equivalent to the number of rows. When counting the results, column values ​​with NULL values ​​will not be ignored.
count(1) includes ignoring all columns, using 1 to represent the code line, and does not ignore column values ​​with NULL values ​​when counting the results.
count(column name) only includes the column name. When counting the results, the count of empty column values ​​(empty here does not mean an empty string or 0, but means null) will be ignored. That is, when a field value is NULL, it will not be counted.

Execution efficiency:
If the column name is the primary key, count(column name) will be faster than count(1)
If the column name is not the primary key, count(1) will be faster than count(column name)
If the table has multiple columns and no primary key, count(1) is more efficient than count(*)
If there is a primary key, the execution efficiency of select count (primary key) is the best
If the table has only one field, select count(*) is optimal.

Example Analysis

mysql> create table counttest(name char(1), age char(2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into counttest values
  -> ('a', '14'),('a', '15'), ('a', '15'), 
  -> ('b', NULL), ('b', '16'), 
  -> ('c', '17'),
  -> ('d', null), 
  ->('e', '');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql> select * from counttest;
+------+------+
| name | age |
+------+------+
| a | 14 |
| a | 15 |
| a | 15 |
| b | NULL |
| b | 16 |
| c | 17 |
| d | NULL |
| e | |
+------+------+
8 rows in set (0.00 sec)

mysql> select name, count(name), count(1), count(*), count(age), count(distinct(age))
  -> from counttest
  -> group by name;
+------+-------------+----------+----------+------------+----------------------+
| name | count(name) | count(1) | count(*) | count(age) | count(distinct(age)) |
+------+-------------+----------+----------+------------+----------------------+
| a | 3 | 3 | 3 | 3 | 2 |
| b | 2 | 2 | 2 | 1 | 1 |
| c | 1 | 1 | 1 | 1 | 1 |
| d | 1 | 1 | 1 | 0 | 0 |
| e | 1 | 1 | 1 | 1 | 1 |
+------+-------------+----------+----------+------------+----------------------+
5 rows in set (0.00 sec)

Additional reference: http://blog.csdn.net/lihuarongaini/article/details/68485838

This concludes this article on the differences between count(1), count(*) and count(column name). For more information about count(1), count(*) and count(column name), please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!