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

This article mainly introduces the total number of MySQL records to achieve query optimization, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the total number of MySQL records to achieve query optimization.

1. COUNT (*) and COUNT (COL)

COUNT (*) usually scans the index of the primary key, while COUNT (COL) is not necessarily. In addition, the former is the total number of records of all matches in the statistical table, while the latter is the number of records of all COL matches in the table. There's a difference.

Optimize the summary for the MyISAM table:

1. SELECT COUNT (*) FROM tablename is the best choice in any case.

two。 Minimize queries such as SELECT COUNT (*) FROMtablename WHERE COL = 'value'

3. Stop the appearance of SELECT COUNT (COL) FROM tablename WHERE COL2 = 'value'.

2. COUNT (*) or COUNT (id)

As far as I understand it, it should be faster to use COUNT (id), because if my id is a self-increasing primary key, it obviously takes less resources to calculate its number than to calculate the number of all fields. But I've read more than one similar article that guides mysql query acceleration, suggesting that we use SELECT COUNT (*) instead of a direct COUNT primary key. Why?

It seems that because the total number of entries is stored in the table of the MyISAM engine, if no WHERE or WHERE is always true (such as WHERE 1), then COUNT (*) can directly return the total number of entries.

In addition, it is obvious that COUNT (*) does not mean "calculate all fields", and it is clear that MySQL will parse * into the meaning of "one piece of data".

The test data, simply compared, did not do more in-depth testing:

# 0.817-1 million query time select count (*) from student;# 0.789-1 million query time select count (id) from student;#1.011- 1 million query time select count (name) from student;#1.162- 1 million query time SELECT COUNT (*) FROM student WHERE namelike'% xiaoli%';# is queried by primary key index by default, but the index is invalid after adding like condition

After reading the above about the total number of MySQL records to achieve query optimization, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

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.