What should I do if mysql count query is very slow? mysql query speed optimization solution

Mysql query speed is too slow, which is a very troublesome thing. Therefore, the author spent some time to sort out the optimization plan for mysql query speed. This article is all author's If you have any questions or mistakes, you are welcome to communicate and correct me. We can learn and make progress together.

Count() optimization of MySQL large tables

Recommended related mysql video tutorials: "mysql tutorial"

This article is also written for It can help everyone clear up their doubts and get back to the topic. The following is based on my judgment based on the data structure of the B-tree and speculation on the experimental results.

Today I experimented with MySQL’s count() operation optimization, as follows The discussion is based on the mysql5.7 InnoDB storage engine. x86 windows operating system.

The structure of the created table is as follows (the amount of data is 1 million):

First of all, about mysql’s count(*), count(PK), count(1 ) Which one is faster?
The implementation results are as follows:



There is no difference! After adding the WHERE clause, the three queries take the same time, so I won’t post pictures.

I wrote a SQL statement select count(*) from table before when I was in the company, which was very slow when there was a lot of data. So how to optimize?

This starts with the index of InnoDB. The index of InnoDB is B Tree.

For the primary key index: it only stores data on leaf nodes, its key is the primary key , and the value is the entire data .
For auxiliary indexes: key is the column to be indexed, and value is the primary key.

This gives us two pieces of information:
1. The entire data will be found based on the primary key
2. Only the primary key can be found based on the auxiliary index, and then the remaining information must be found through the primary key.

So if we want to optimize the count(*) operation, we need to find a short column and create an auxiliary index for it.
In my example it is status, although its "severelity" is almost 0.

Create the index first: ALTER TABLE test1 ADD INDEX (status );
Then query, as shown below:

As you can see, the query time dropped from 3.35s to 0.26s, and the query speed increased by nearly 13 times.

If the index is the column str, what will the result be?
Create an index first: alter table test1 add index (str)
The results are as follows:

As you can see, the time is 0.422s, which is also very long Fast, but compared to the status column, there is still a gap of about 1.5 times.

To be more bold and do an experiment, I deleted the index of the status column and created a status and left(omdb,200)(This column has an average of 1000 characters) joint index, and then look at the query time.
Create index: alter table test1 add index (status,omdb(200))
The results are as follows:

The time is 1.172s
alter table test1 add index (status,imdbid);

Supplement! !
Pay attention to index failure!
The normal appearance after establishing the index:
You can see that key_len is 6, and the Extra description is using index.

And if the index fails:

There are many situations in which indexes fail, such as using functions, != operations, etc. Please refer to the official documentation for details.

I have no in-depth research on MySQL. The above is based on my judgment based on the data structure of the B-tree and speculation on the experimental results. If there are any shortcomings, please correct me.

Related articles:

Sql server2005 Summary of 50 ways to optimize query speed

Improve query speed: SQL Server database optimization plan

The above is the detailed content of What should I do if mysql count query is very slow? mysql query speed optimization solution. For more information, please follow other related articles on the PHP Chinese website!