Shulou(Shulou.com)06/01 Report--

This article mainly introduces the difference between count (1) and count (*) in mysql what the relevant knowledge is, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe that you will gain something after reading the difference between count (1) and count (*) in this mysql.

1. Count (1) and count (*)

When the table has a larger amount of data, it takes more time to use count (1) than to use count (*) after analyzing the table!

From the point of view of the execution plan, count (1) and count () have the same effect. But after the table has been analyzed, count (1) will take less time (less than 1 week of data) than count (), but not by much.

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

Because of count (), which field is automatically optimized for assignment. So there is no need to go to count (1), use count (), sql will help you complete the optimization, so: count (1) and count (*) basically no difference!

2. Count (1) and count (field)

The main differences between the two are:

(1) count (1) will count all the records in the table, including the records whose field is null.

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

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

In terms of execution effect:

Count (*) includes all columns, which are equivalent to the number of rows. When counting the results, the column value of NULL is not ignored.

Count (1) includes ignoring all columns, using 1 for lines of code, and not ignoring that the column value is NULL when counting the results.

Count (column name) only includes the column name. When counting the result, it ignores the count when the column value is empty (the empty value here is not just an empty string or 0, but represents null), that is, when a field value is NULL, it is not counted.

In terms of execution efficiency:

Column name is primary key, count (column name) will be faster than count (1)

Column name is not primary key, count (1) will be faster than count (column name)

If the table has multiple columns and no primary key, then count (1) is more efficient 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.

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- > ('averse,' 14'), ('averse,' 15'), ('averse,' 15'),-> ('baked,' NULL), ('baked,' 16'),-> ('cased,' 17'),-> ('dumped, null),-> (' eBay,'') Query OK, 8 rows affected (0.01sec) Records: 8 Duplicates: 0 Warnings: 0mysql > 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 | 1 | 1 | c | 1 | 1 | 1 | 1 | d | 1 | 1 | 0 | 0 | e | 1 | | 1 | 1 | 1 | 1 | +-+-+ 5 rows in set (0.00 sec) this is the end of the article on "what is the difference between count (1) and count (*) in mysql?" Thank you for reading! I believe you all have a certain understanding of the knowledge of "what is the difference between count (1) and count (*) in mysql". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.