MySQL optimization summary-total number of queries

This article mainly introduces the MySQL optimization summary-the total number of queries. The article conducts a simple test and comparison, which has certain reference value. Friends who need it can learn about it. I hope it can help everyone.

1, COUNT(*) and COUNT(COL)

COUNT(*) usually performs an index scan on the primary key, while COUNT(COL) Not necessarily. In addition, the former is the total number of all matching records in the statistical table, while the latter is 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 under any circumstances;

2. Try your best Reduce SELECT COUNT(*) FROM tablename WHERE COL = 'value' queries;

3. Eliminate 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 will obviously consume less resources than calculating the number of all fields. However, I have seen in more than one article similar to guiding mysql query acceleration that it is recommended that we use SELECT COUNT(*) instead of direct COUNT primary key. Why is this?

It seems that the table using the MyISAM engine stores the total number of entries. If there is 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 "calculate all fields". Obviously MySQL will parse * into "a piece of data".

Test data, simple comparison, no more in-depth testing:

#0.817-一百万条的查询时间
select count(*) from student ;
#0.789-一百万条的查询时间
select count(id) from student;
#1.011-一百万条的查询时间
select count(name) from student;
#1.162-一百万条的查询时间
SELECT COUNT(*) FROM student WHERE namelike '%xiaoli%';#默认用主键索引查询，但是加上like条件后索引失效

Generally speaking, using COUNT(id) is still better It's faster. Here is a simple comparison for your reference.

Related recommendations:

Example sharing of IN to INNER JOIN for mysql optimization

Summary of some commonly used mysql optimization methods

Summary of commonly used mysql optimization sql statement query methods

The above is the detailed content of MySQL optimization summary-total number of queries. For more information, please follow other related articles on the PHP Chinese website!