1、 count(1) and count(*)

When a table is large, it takes more time to use count(1) than count(*) after parsing the table!

From the execution plan, count(1) and count() have the same effect. But after the table is analyzed, count(1) takes less time (less than 1W) than count(), but not by much.

If count(1) is a clustered index,id, it must be count(1) fast. But the difference is very small.

Because count() is automatically optimized to specify that field. So there is no need to use 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(1)

The main differences between the two are:

(1) count(1) counts all records in the table, including those whose fields are null.

(2) Count (field) Counts the number of times that the field appears in the table, ignoring the null field. That is, records whose field is null are not counted.

Count (*) differs from count(1) and count(column name)

Execution effect:

Count (*) includes all columns and is equivalent to the number of rows. NULL columns are not ignored when the result is counted

Count (1) involves ignoring all columns, using 1 to represent the line of code, and not ignoring NULL columns when counting results

Count (column name) includes only the column name. When the result is counted, the count of the column value that is null is ignored. That is, if the value of a column is null, the count is not counted.

Execution efficiency:

Column name primary key, 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 (*)

Select count (primary key) is optimal if there is a primary key

Select count (*) is optimal if the table has only one field.

4. Case 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.01sec) 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 the 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 the set (0.00 SEC)Copy the code

Source | blog.csdn.net/iFuMI/article/details/77920767

Welcome to follow my wechat public account “Code farming breakthrough”, share Python, Java, big data, machine learning, artificial intelligence and other technologies, pay attention to code farming technology improvement, career breakthrough, thinking transition, 200,000 + code farming growth charge first stop, accompany you have a dream to grow together