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

This article introduces the knowledge of "is python count (1) more efficient than count (*)". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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 difference between the two is

(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. 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- > ('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) "is python count (1) more efficient than count (*)?" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.