MySQL Optimization Summary - Total Number of Query Entries

1. COUNT(*) and COUNT(COL)

COUNT(*) usually performs an index scan on the primary key, but COUNT(COL) is not necessarily the case. In addition, the former counts the total number of all matching records in the statistical table, while the latter counts the number of all matching COL records in the calculation table. There are differences.
Optimization summary, for MyISAM tables:

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

2. Try to reduce queries like SELECT COUNT(*) FROMtablename WHERE COL = 'value';

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

2. COUNT(*) or COUNT(id)

According to my understanding, it should be faster to use COUNT(id) because if my id is an auto-incrementing primary key, then calculating its number obviously consumes fewer resources than calculating the number of all fields. But I have seen more than one similar article on how to speed up MySQL queries, which recommends that we use SELECT COUNT(*) instead of directly COUNT the primary key. Why is that?

It seems that this is because the table using the MyISAM engine stores the total number of entries. If there is no WHERE or the 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". Obviously, MySQL will interpret * as "a piece of data".

Test data, simple comparison, no further testing:

#0.817-Query time for one million records select count(*) from student ;
#0.789-Query time for one million records select count(id) from student;
#1.011-Query time for one million records select count(name) from student;
#1.162-Query time for one million recordsSELECT COUNT(*) FROM student WHERE namelike '%xiaoli%';#By default, the primary key index is used for query, but the index becomes invalid after adding the like condition

Summarize

In general, using COUNT(id) is faster. Here is a simple comparison for your reference.

The above is all the content of this article about MySQL optimization summary - total number of queries. I hope it will be helpful to everyone. Interested friends can refer to: MySQL optimization using joins instead of subqueries, MYSQL subquery and nested query optimization example analysis, MySQL in statement subquery efficiency optimization skills examples, etc. If there are deficiencies, please leave a message to point them out. Thank you friends for supporting this site!

You may also be interested in:

Detailed explanation of MYSQL configuration parameter optimization
Mysql query the most recent record of the sql statement (optimization)
Detailed explanation of 30 SQL query optimization techniques for MySQL tens of millions of large data
Mysql optimization techniques for querying dates based on time
10 SQL statement optimization techniques to improve MYSQL query efficiency
MySQL million-level data paging query optimization solution
Optimizing the performance of paging query for MySQL with tens of millions of data
MYSQL development performance research: optimization method for batch inserting data
Summary of ten principles for optimizing basic statements in MySQL
MySQL optimization strategy (recommended)

<<: js implements single click to modify the table

>>: How to build php+nginx+swoole+mysql+redis environment with docker