The difference between MySQL count(1), count(*), and count(field)

Regarding the number of rows in the database, both MySQL and Oracle have a function that can be used, that is COUNT.

However, this commonly used COUNT function hides many mysteries, especially during job interviews, and you can easily be abused if you are not careful. If you don’t believe me, please try to answer the following questions:

> 1. How many uses does COUNT have?
> 2. What is the difference between the query results of COUNT(field name) and COUNT(*)?
> 3. What is the difference between COUNT(1) and COUNT(*)?
> 4. Which one is more efficient, COUNT(1) or COUNT(*)?
> 5. Why does Alibaba Java Development Manual recommend using COUNT(*)?
> 6. What optimizations does MySQL's MyISAM engine make to COUNT(*)?
> 7. What optimizations does MySQL's InnoDB engine make to COUNT(*)?
> 8. What is the key premise for the above-mentioned MySQL optimization of COUNT(*)?
> 9. When performing SELECT COUNT(*), does it make a difference whether or not to add a where condition?
> 10. What is the execution process of COUNT(*), COUNT(1) and COUNT(field name)?
If you can answer all the above 10 questions correctly, it means that you really understand the COUNT function.

1. First look at COUNT

1. COUNT(expr) returns the number of rows retrieved by the SELECT statement where the value of expr is not NULL. The result is a BIGINT value.

2. If the query result does not hit any record, it returns 0

3. However, it is worth noting that the statistical results of COUNT(*) will include the number of rows with NULL values.

In addition to COUNT(id) and COUNT(*), you can also use COUNT(constant) (such as COUNT(1)) to count the number of rows. So what are the differences between these three SQL statements? Which one is more efficient? Why does the Alibaba Java Development Manual forbid the use of COUNT(column name) or COUNT(constant) instead of COUNT(*)?

2. The difference between COUNT(field), COUNT(constant) and COUNT(*)

COUNT(constant) and COUNT(*) indicate directly querying the number of rows in the database table that meet the conditions.

COUNT(column name) indicates the number of rows whose values ​​of the columns that meet the conditions are not NULL.

COUNT(*) is the standard syntax for counting rows defined in SQL92. Because it is a standard syntax, the MySQL database has been optimized a lot.

SQL92 is an ANSI/ISO standard for databases. It defines a language (SQL) and the behavior of the database (transactions, isolation levels, etc.).

3. Optimization of COUNT(*)

MySQL mainly uses two execution engines:

• InnoDB Engine
• MyISAM Engine

MyISAM does not support transactions, and the locks in MyISAM are table-level locks; while InnoDB supports transactions and row-level locks.

MyISAM

MyISAM has made a simple optimization, recording the total number of rows in the table separately. If count(*) is executed, it can be returned directly, provided that there is no where condition. MyISAM uses table-level locks and there will be no concurrent row operations, so the results are accurate.

InnoDB

InnoDB cannot use this kind of cache operation because it supports transactions and most operations are row-level locks. Rows may be modified in parallel, so the cache records are inaccurate.

However, InnoDB has made some optimizations for the COUNT(*) statement.

Scan the table through low-cost indexes without paying attention to the specific content of the table.

Indexes in InnoDB are divided into clustered indexes (primary key indexes) and non-clustered indexes (non-primary key indexes). The leaf nodes of clustered indexes store the entire row of records, while the leaf nodes of non-clustered indexes store the primary key values ​​of the row of records.

MySQL will give priority to selecting the smallest non-clustered index to scan the table.

The premise of optimization is that the query statement does not contain where conditions and group by conditions.

4. COUNT(*) and COUNT(1)

The official MySQL documentation says this:

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

Therefore, for count(1) and count(*), MySQL's optimization is exactly the same, and there is no question of which is faster!

However, it is still recommended to use count(*) because it is the standard syntax for counting rows defined by SQL92.

5.COUNT(field)

Perform a full table scan to determine whether the value of the specified field is NULL. If not, add it.

The performance is slower than count(1) and count(*).

6. Summary

The COUNT function is mainly used to count the number of rows in a table. The main usages are COUNT(*), COUNT(field) and COUNT(1).

Because COUNT(*) is the standard syntax for counting rows defined in SQL92, MySQL has made many optimizations for it. MyISAM will directly record the total number of rows in the table for COUNT(*) query, while InnoDB will select the smallest index when scanning the table to reduce costs. Of course, the premise of these optimizations is that there is no where and group condition query.

In InnoDB, there is no difference in the implementation of COUNT(*) and COUNT(1), and the efficiency is the same, but COUNT(field) requires a non-NULL check on the field, so the efficiency is lower.

Because COUNT(*) is the standard syntax for counting rows defined by SQL92 and is highly efficient, please use COUNT(*) directly to query the number of rows in the table!

Reference link: MySQL's COUNT statement can be tortured so badly by the interviewer! ?

This is the end of this article about the differences between MySQL count(1), count(*), and count(field). For more information about MySQL count(1), count(*), and count(field), please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!