How slow is MySQL count query? MySQL Query speed optimization scheme

MySQL query too slow is a very annoying thing, so, the author has spent some time to organize the MySQL Query speed optimization program, this article is all the author's personal views, such as questions or wrong welcome to communicate and correct, we learn together progress.

Count () Optimization for MySQL large table

Writing an article is also to help you to dismiss the question, return to the point, the following is based on my combination of B + Tree data structure and the experimental results of the speculation to make the judgment

Today we experimented with MySQL's count () operation optimizations, the following discussion is based on the mysql5.7 InnoDB storage engine. x86 Windows operating system.

The table created is structured as follows (data volume is 1 million):

The first is about MySQL's count (*), COUNT (PK), COUNT (1) which fast question.
The implementation results are as follows:



And there's no difference! Add the WHERE clause after the 3 query time is the same, I will not paste the picture.

Before the company wrote a select count(*) from table SQL statement, when the data is very slow. So how to optimize it?

This is to say from the index of InnoDB, the index of InnoDB is B+tree.

For a primary key index: It stores data only on leaf nodes, its key is the primary key , and value is the entire data .
For secondary indexes: key is an indexed column and value is the primary key.

This gives us two information:
1. According to the primary key will find the entire data
2. Depending on the secondary index, only the primary key can be found, and then the remaining information must be checked through the primary key.

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

Index first: ALTER TABLE test1 ADD INDEX ( status );
Then query, such as:

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

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

It can be seen that the time is 0.422s, but also very quickly, but status it is still 1.5 times times the gap compared to this column.

A little more daring to do an experiment, I status delete the index of this column, create a status and left(omdb,200) (this column average 1000 characters) of the joint index, and then look at the query time.
Build index: alter table test1 add index ( status ,omdb(200))
The results are as follows:

Time is 1.172s
ALTER TABLE TEST1 Add index ( status , imdbid);

Add!!
Be aware of the failure of the index!
A normal appearance after indexing is established:
You can see that Key_len is 6, and extra's description is using index.

And if the index fails:

There are many conditions for indexing failure, such as using functions,! = operations, etc., please refer to the official documentation.

There is no deep research on MySQL, the above is based on my combination of B + Tree data structure and the experimental results of the estimation of the judgment, if there are shortcomings, welcome to correct.

Related articles:

SQL server2005 optimization Query Speed 50 Summary of methods

Improve query speed: SQL Server database optimization scenario