The difference between count, count(*) and count (column name) in sql

1. count(1) and count(*)

When the amount of data in the table is larger, after analyzing the table, it takes more time to use count(1) than to use count(*)! From the execution plan, count(1) and count(*) have the same effect. However, after the table is analyzed, count(1) will take less time than count(*) (the amount of data within 1w), but it is not much worse.

If count(1) is a clustered index, id, then count(1) is definitely faster. But the difference is small. Because of count(), it will automatically optimize to that field. So there is no need to go to count(1), use count(), sql will do the optimization for you. So:

count(1) and count(*) are basically the same!

2. count(1) and count( field)

The main difference between the two is

(1) count(1) will count all the records in the table, including records whose 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.

3. 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, the column value of NULL will not be ignored.

count(1) includes ignoring all columns, using 1 to represent the code line, when counting the results, the column value of NULL will not be ignored

count (column name) only includes the column of the column name. When counting the results, it will ignore the count of the empty column value (the empty string here is not just an empty string or 0, but means null), that is, the value of a field is null. When NULL, no statistics are counted.

In terms of execution efficiency:

The column name is the primary key, and count(column name) will be faster than count(1)

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) performs better than count(*)

If there is a primary key, the execution efficiency of select count (primary key) is optimal

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

  1. Case Analysis

Take a look at an example to see at a glance:

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)

 

Tags: SQL

Posted by newbreed65 on Sat, 21 May 2022 19:49:21 +0300