Shulou(Shulou.com)05/31 Report--

This article mainly introduces the count(1) than count(*) efficiency related knowledge, detailed and easy to understand, simple and fast operation, has a certain reference value, I believe that everyone will have a harvest after reading this count(1) than count(*) efficiency article, let's take a look at it.

Is MySQL count(1) really faster than count(*)? Anyway, my colleagues all said so, and I thought it was right for the time being, so didn't I study it myself? If I told you they were the same, would you believe me?

Count with Where condition will count all the rows according to the scanning result, and its performance depends more on your Where condition, so we only explain the case without Where.

MyISAM engine will record the total number of a table, so it will directly return the number when executing count(*), which is very efficient. After MySQL 5.5, the default engine is switched to InnoDB. InnoDB has increased version control (MVCC). When there are multiple transactions accessing data and there are update operations at the same time, each transaction needs to maintain its own visibility, so the number of rows queried by each transaction is also different, so it cannot cache the specific number of rows. It needs to count all the rows each time. What is the difference between count(1) and count(*)?

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference. This is the explanation of the official website, click directly to read the original text to view the official text, so the two implementations are actually the same, so why is it the same?

To explore this problem, we first need to understand the meaning of count, as follows:

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

The rough explanation is to return a count of the non-NULL values of expr in the row retrieved by the SELECT statement, so here we see that first it is an aggregate function, and then counts the result set of SELECT, but requires that the parameter is not NULL. So let's continue reading the official website:

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

The rough idea is that count(*) is different, he doesn't care if the return value is empty or not, he will calculate his count, because the 1 in count(1) is a constant truth expression, then count(*) or count(1) counts all the result sets, so they are essentially the same.

Of course, InnoDB itself has made some optimizations in this place. It will use the smallest secondary index to optimize the query of count. Cluster indexes are chosen only if there is no secondary index, which saves a lot of overhead from an IO perspective alone.

Here we understand that count(*) and count(1) are essentially the same, so what about count(column)?

count(column) will also traverse the entire table, but the difference is that it will get the value of column after determining whether it is empty, and then accumulate, so if you need to parse the content for the primary key, if it is a secondary so you need to get the content according to the primary key again, and it is an IO operation, so the performance of count(column) is definitely not as good as the first two, if compared according to efficiency:

count(*)=count(1)>count(primary key)>count(column)

Since count(*) depends on all data sets in the query, do we also need to avoid the full count as much as possible in our design? Usually we will do appropriate caching for predictable count queries, either Redis or a separate MySQL count table, of course, we need to consider consistency in either way.

About "count(1) is more efficient than count(*)" The content of this article is introduced here, thank you for reading! I believe everyone has a certain understanding of the knowledge of "count(1) is more efficient than count(*)." If you still want to learn more knowledge, please pay attention to 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.