Mysql's count query efficiency from innodb to MyIsam is greatly improved _ MySQL

The count query efficiency of mysql from innodb to MyIsam greatly improves bitsCN. the count query efficiency of commysql from innodb to MyIsam has been greatly improved recently, after the company has a business log table that exceeds 5 million, count (*) needs to be 4 minutes 55 seconds, after the storage engine of the table is converted from innodb to MyIsam, the query efficiency is greatly improved, from 4 minutes 55 seconds to 0.01 seconds. The procedure is as follows: mysql> select count (*) from tb_option_log; 4min55s mysql> show table status from database name where name = 'TB _ option_log '; Engine: innoDB mysql> alter table tb_option_log type = 'myisam'; mysql> show table status from database name where name = 'TB _ option_log '; Engine: myisam mysql> select count (*) from tb_option_log; 0.01 s; Supplement: MyISAM is the default storage engine of MySQL. each MyISAM table is stored in three files. Frm file storage table definition. The data file is MYD (MYData ). The index file is an extension of MYI (MYIndex. Because MyISAM is relatively simple, it is more efficient than InnoDB .. using MyISAM for small applications is a good choice. myISAM tables are saved as files. using MyISAM storage in cross-platform data transfer saves a lot of trouble. The following are some details and specific implementation differences: 1. innoDB does not support FULLTEXT indexes. 2. innoDB does not store the specific number of rows in the table. that is to say, when you execute select count (*) from table, InnoDB needs to scan the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are somewhat different. InnoDB tables use count (*) or count (primary key ), add the where col condition. The col column is a column other than the primary key of the table that has unique constraint indexes. In this way, the query speed will be fast. To avoid full table scanning. 3. for fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields. 4. when deleting FROM table, InnoDB does not create a new table, but deletes a row. 5. the load table from master operation does not work for InnoDB. the solution is to first change the InnoDB TABLE to the MyISAM TABLE, and then change the imported data to the InnoDB TABLE, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys. Author: bitsCN.com