Recently, the number function has been used a lot, and when the number to be counted is relatively large, it is found that count(*) takes more time, and count(1) takes less time.
Some documents have the following statements:
If your data table does not have a primary key, then count(1) is faster than count(*).
If there is a primary key, then the primary key (joint primary key) is also faster than count(*).
If your table has only one field, then count(*) is the fastest
count(*) count(1) Compare the two. The main thing is to count the data fields corresponding to (1).
If count(1) is a polyindex, id, then it must be count(1) faster. But the difference is very small.
Because count(*), the field is automatically optimized to specify to. So there is no need to count(?), use count(*), SQL will help you complete the optimization
Count Details:
count(*) will return the total number of all rows present in the table, including rows with a value of null,However, count (column name) will return the total number of all rows in the table except null(Columns with default values are also counted).
distinct column name, the result will be the result after removing the null value and duplicate data
In the past, I didn't know that the blog said that everyone was recommended to use count(0) and that the query efficiency was relatively high, etc
Today, it is found that using count(0) is a misconception!!!
count(0) The first column of the statistics is not empty
Do not use number(column name) or count(constant) instead of count(*),
count(*) is the syntax for the standard number of rows defined by SQL92, which has nothing to do with the database, NULL and non-NULL.
Note: count(*) counts rows with a NULL value, while count(column name) does not count rows with a NULL value.
Therefore, in the future, when you do all the statistics and do not rule out empty data, it is better to honestly use count (*)!!!
|
Posted on 8/10/2017 1:29:00 PM
|
|
|
Posted on 8/10/2017 1:54:00 PM