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

This article will explain in detail how to use COUNT in MySQL, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

COUNT (expr) returns the number of rows in which expr is not NULL, resulting in a value of type BIGINT, or 0 if there is no match for the row. It should be noted here that the so-called NULL refers to the expr expression, not the value of the data row to be detected, and if the expr is the same as the data column name, then the number that the data column is not NULL is returned.

COUNT of MySQL

For the traditional storage engine InnoDb, it is very difficult to store the exact number of rows of a table, because multi-transaction operations may be concurrent and affect the number of rows of the table at the same time, so in order to avoid concurrent threads querying the same table and getting different rows, InnoDb does not store the number of rows of the table internally, so in InnoDb, SELECT (*) only returns the number of rows read by the current transaction. Before MySQL5.7.18, InnoDb processes the SELECTCOUNT (*) statement by scanning the clustering index, and after 5.7.18 InnoDb uses the smallest and available second index to process the SELECTCOUNT (*) statement (unless the query optimizer prompts for a different index). If there is no second index, then you need to scan the clustered index.

If the index records are not all in the buffer pool, it takes some time to process the SELECTCOUNT (*) statement. If you need to pursue a fast calculation, you can design a separate table in the application to record the number of entries in the target table when it is inserted or deleted. However, it can be difficult to expand when a large number of parallel processing comes to the counter table of the update target table. If you can get this information with a SHOWTABLESTATUS statement.

InnoDb handles SELECTCOUNT (*) and SELECTCOUNT (1) in the same way, with no performance gap.

For the MyIsam engine, COUNT (*) is very fast without the WHERE clause, because in the MyIsam engine, the exact number of rows of the table is saved. If the first column of the MyIsam table is set to non-empty, COUNT (1) has the same optimization effect (no significant performance difference was found in the comparison test of COUNT (*) and COUNT (1) for 100w simple data with non-empty data).

Specification of COUNT

Both COUNT (*) and COUNT (constant) are the number of rows recorded in the lookup table and can contain NULL data; COUNT (column name) is a function that looks up the column in the table that is not empty.

For COUNT (*) compared to COUNT (constant), COUNT (*) is the standard syntax for counting rows defined in SQL92, so many databases optimize it. For MySQL, COUNT (constant) is also converted to COUNT (*), and the standard syntax gives priority to COUNT (*), because the optimization strategy of each database query optimizer must start with COUNT (*). Ali's SQL development specification also clearly states that COUNT (*) should be used.

On how to use COUNT in MySQL to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.