The difference between MySQL count(*), count(1) and count(field name)
In MySQLCOUNT(*)、COUNT(1)andCOUNT (field name)Differences:
| Count type | describe | Whether to calculate NULL value | Performance considerations |
|---|---|---|---|
| COUNT(*) | Calculate the number of rows including all columns | Yes, does not distinguish whether the column value is NULL | Usually optimal because there is no need to check the value of a specific column |
| COUNT(1) | Functionally equivalent to COUNT(*), calculate the number of rows | Yes, 1 is a constant, never NULL | Same performance as COUNT(*) because execution plans are usually the same |
| COUNT (field name) | Calculate the number of rows with non-NULL values in the specified column | No, only count the number of rows with non-NULL values | May be slower than COUNT(*) or COUNT(1), especially when the checked field contains many NULL values |
In most cases,COUNT(*)is preferred because it complies with SQL standards and performs well in performance.
In relational database management systems such as MySQL,COUNT(*)andCOUNT(1)Functionally equivalent, the difference between them is not obvious, and is mainly reflected in the following aspects:
Is there any obvious difference between COUNT(*) and COUNT(1)
1. Functional equivalence
-
COUNT(*): Calculate the number of all rows in the query result set, including all columns, and no rows are ignored, even if some columns containNULLvalue. -
COUNT(1):Will1As a non-empty constant value, count each row. because1Never doNULL, so it actually has to do withCOUNT(*)All rows are calculated the same.
In modern database systems,COUNT(*)andCOUNT(1)The performance in the execution plan is usually the same, and the query efficiency is basically no different.
2. Semantic clarity
-
COUNT(*): Semantics are clear, clearly indicating the intention to count all rows, so it is more popular. It's more intuitive for those who read the code. -
COUNT(1): Although functionally equivalent toCOUNT(*), but some developers may prefer its appearance for personal or historical reasons. However, from the perspective of semantic clarity,COUNT(*)Better.
3. Performance differences (comparison between historical and modern database systems)
- Historically, some old database systems were processing
COUNT(*)When the data is resolved, the entire line of data may be parsed, andCOUNT(1)You can directly count the number of rows, so there will be some slight performance differences. - However, in modern database systems (such as MySQL, PostgreSQL, SQL Server, etc.),
COUNT(*)andCOUNT(1)Similar handling in terms of performance. The optimizers of these systems understand that these two queries are essentially asking for the same content: row count. Therefore, the performance differences can be ignored.
4. Use suggestions
- When writing SQL queries, the readability and maintainability of the code should be given priority.
COUNT(*)It is usually more recommended for its clarity and readability. - Unless it is based on a deep understanding of the database optimizer or a personal preference (and this preference does not usually bring significant performance advantages in modern database systems), it should be used
COUNT(*)To count the rows.
To sum up,COUNT(*)andCOUNT(1)Functionally equivalent, and performance differences are negligible in modern database systems.
Summarize
The above is personal experience. I hope you can give you a reference and I hope you can support me more.