MySQL Optimization Summary Total Queries

1. COUNT (*) and COUNT (COL)

COUNT (*) is usually an index scan of the primary key, while COUNT (COL) is not fixed. In addition, the former is the total number of consistent records in the statistical table, while the latter is the number of consistent COL records in the calculation table. There are differences.
Optimization summary, for MyISAM table:

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

2. Minimize the query of SELECT COUNT (*) FROMtablename WHERE COL = 'value';

3. Eliminate SELECT COUNT (COL) FROM tablename WHERE COL 2 = '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 consumes 1 less resources than counting all the fields. But I've seen more than one similar article on mysql query acceleration, suggesting that we use SELECT COUNT (*) instead of the direct COUNT primary key. Why?

It seems that the table with MyISAM engine stores the total number of entries. If WHERE or WHERE is always true (such as WHERE 1), COUNT (*) can directly return the total number of entries.

In addition, it is obvious that COUNT (*) does not mean "calculate all fields", but MySQL will parse * into "1 piece of data".

Test data, simple comparison 1, no more in-depth test:

#0.817-1 Millions of query time 
select count(*) from student ;
#0.789-1 Millions of query time 
select count(id) from student;
#1.011-1 Millions of query time 
select count(name) from student;
#1.162-1 Millions of query time 
SELECT COUNT(*) FROM student WHERE namelike '%xiaoli%';# By default, the query is indexed with the primary key, but the like Index invalidation after condition 

Summarize

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

The above is the article on MySQL optimization summary-the total number of queries, I hope to help you. Interested friends can refer to: MySQL optimization using join (join) instead of subquery, MYSQL subquery and nested query optimization instance analysis, mysql in statement subquery efficiency slow optimization skills examples, etc. If there are shortcomings, welcome to leave a message. Thank you friends for your support to this site!