The count (*) trap of the MySQL innodb Engine

The following articles mainly describe the count (*) trap in the MySQL innodb engine (*) if you are interested in the trap, you can click the following article to view it. I hope it will help you in this regard.

Why does word cause MySQL database to crash?

Why does InnoDB delete from xxx slow speed?

MySQL Storage Engine note

Recommendation circle: JBPM @ net

More related recommendations today, the students discuss the issue of oracle's count (*) and count (1) in the group, just referring to the MySQL situation. I suddenly thought of my own problem: the execution speed of count (*) on myisam engine is very fast, and the execution speed has nothing to do with the number of records, but this is not the case with MySQL innodb. The more records, the slower the speed.

So I did an experiment and executed select count (*) from table in an innodb table with million records. I have been waiting for the results after about 8 minutes. Execute the same statement immediately, which takes about 22 seconds. Execute the task for the third time, or about 22 seconds.

So I guess innodb does not save the number of records, but does real-time statistics, resulting in a slow speed. The second and third times are relatively fast because of high-speed cache. So I called DBA. After professional answers from DBA, I understand: I guess it's right, huh, huh. Therefore, pay attention to the count (*) Problem in MySQL innodb in the future. Avoid it as much as possible ...... Unless it is ensured that the table always has a small number of records.

In addition, I checked some information on the Internet:

Reference

InnoDB Pitfalls

However, all is not rosy with InnoDB. because of its transactional nature, it has bottlenecks of its own. on MyISAM, doing a query that does select count (*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index.

On InnoDB, this info is not stored in an index, and even the index and the data are kept in the same file. so, doing the same query on a table can incur a significant performance penalty.

To check what overhead this has, I wrote a simple test benchmark code. I duplicated a client node table that has 20,243 rows from MyISAM to InnoDB.

On a quiescent AMD 64 machine with MySQL server 5.0.24, doing a select count (*) FROM node takes 0.835 milliseconds on MyISAM, while on InnoDB it takes 12.292 milliseconds!

The above is the description of the count (*) Problem of the MySQL innodb engine.