Be a positive person

Code, fix bugs, improve yourself

I have a paradise, facing programming, spring flowers!

X can represent: primary key ID, field, 1, *

0

For count(primary key ID)

The InnoDB engine iterates through the table, fetching the ID value of each row and returning it to the server layer, which determines that the ID value is not empty and adds it up by row

For the count(field)

If the field is defined as not NULL, the column is read from the record line by line, and the value is accumulated

If the field definition is allowed to be NULL, then the value must be extracted to determine if it is not null

For count of 1

Innodb engine traverses the entire table, but does not value it, and returns it to the server layer. For each row returned by InnoDB, the server puts a number 1 in it, and decides that it cannot be empty, so it adds up each row

For the count (*)

I’m not going to pull all the fields out, I’m going to optimize it, I’m not going to take the value, count(*) is definitely not null, I’m going to add it up

1 summarizes

If you want to count rows, use either count(*) or count(1), the former is recommended

If you want to count the number of fields that are not NULL, you use count.

MySQL > update MySQL > update MySQL > update MySQL > update MySQL

  1. When mysql confirms that the expression value in parentheses cannot be null, it is actually counting the number of rows

  2. If mysql knows that col cannot be NULL, mysql will optimize the count(col) expression to count(*).

That is, count(primary key field) and count(1) are optimized to count(*).

In MySQL 5.7 Reference Manual in the official Manual: dev.mysql.com/doc/refman/…

There’s this passage:

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

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

So in order of efficiency, count(field)<count(primary key ID)<count(1)≈count(*)

So, try to use count(*)

2 develop

Mysql >> (3)) SQL

Thank you for reading, if you think this blog is helpful to you, please like or like, let more people see! I wish you happy every day!


No matter what you do, as long as you stick to it, you will see the difference! On the road, neither humble nor pushy!

I wish you and I can become the best of themselves on the way of life, to become an independent person

© Alfayun who is getting better every day