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

Today, the editor will share with you the relevant knowledge points about the differences of java's count (1), count (*) and count (listing). The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

First, let's take a look at the implementation effect:

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, the effect of count (1) and count (*) is the same. 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

Count (1) counts all records in the table, including records with a field of null.

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.

Differences between count (*) and count (1) and count (column names)

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.

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')

-> ('centering,' 17')

-> ('dumped, null)

-> ('estranged,'')

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 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) are all the contents of this article entitled "what are the differences between count (1), count (*) and count (listing) of java? thank you for reading!" I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to 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.