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

This article mainly introduces the differences between count (field), count (primary key id), count (1) and count (*) in MySQL. It is very detailed and has a certain reference value. Interested friends must read it!

The first step is to figure out the semantics of count (). Count () is an aggregate function, and the returned result set is judged row by row. If the parameter of the count function is not NULL, the cumulative value is added by 1, otherwise it is not added. Finally, the cumulative value is returned.

Therefore, count (*), count (1), and count (primary key id) all represent the total number of rows that return the result set that meets the condition, while count (field) indicates that the parameter "field" is not the total number of NULL in the data row that meets the condition.

As for analyzing performance differences, keep these principles in mind:

Give whatever the server layer wants.

InnoDB only gives necessary values

The current optimizer only optimizes the semantics of count (*) to "fetch rows", while other "obvious" optimizations are not done.

Count (nullable field)

Scan the whole table, read the server layer, determine that the field can be empty, take out all the values of the field, and determine whether each value is empty or not.

Count (non-empty field) and count (primary key id)

Scan the whole table, read the server layer, determine that the fields are not empty, and accumulate by row.

Count (1)

Scan the whole table, but do not take a value, each row received by the server layer is 1, it is determined that it can not be null, accumulate by value.

Note: the reason why count (1) executes faster than count (primary key id): returning id from the engine involves parsing data rows and copying field values.

Count (*)

MySQL executes count (*) to make special optimizations in the optimizer. Because the row returned by count (*) must not be empty. Scan the whole table, but do not take a value, accumulate by row.

Seeing here, you would say that the optimizer can't judge for itself, the primary key id must be non-empty, why can't it be handled according to count (*), what a simple optimization. Of course, it is not impossible for MySQL to optimize specifically for this statement. But there are too many situations that require special optimization, and MySQL has already optimized count (*), so you can just use this statement.

Performance comparison conclusion

Count (nullable field) < count (non-null field) = count (primary key id) < count (1) ≈ count (*)

The above is all the content of this article entitled "what are the differences between count (field), count (id), count (1) and count (*) in MySQL? thank you for reading!" Hope to share the content to help you, more related knowledge, welcome to follow 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.