Mysql Storage Engine MyISAM and InnoDB 9-point difference _mysql

Although the storage engine in MySQL is not just the two MyISAM and InnoDB, it is common for both of them. There may be a webmaster did not pay attention to the MySQL storage engine, in fact, the storage engine is the database design of a major point, then the blog system should use what kind of storage engine? Let's look at the difference between the two storage engines, respectively.

The difference between MySQL storage engine MyISAM and InnoDB

First, INNODB support services, MyISAM not supported, this is very important. Transaction is a kind of advanced processing, such as in some column additions and deletions, as long as the error can be rolled back to restore, and MyISAM can not be.

Second, MyISAM suitable for query and insert-oriented applications, INNODB suitable for frequent modification and design to security on the high application

Third, INNODB support foreign key, MyISAM does not support

Four, MyISAM is the default engine, InnoDB need to specify

V. INNODB does not support fulltext types of indexes

The number of rows in the InnoDB that do not save the table, such as SELECT COUNT (*) from table, InnoDB needs to scan the entire table to calculate how many rows, but MyISAM simply read out the saved rows. Note that MyISAM also needs to scan the entire table when the COUNT (*) statement contains a WHERE condition

For a INNODB field, you must include only the index for that field, but you can establish a federated index with the other fields in the MyISAM table

Eight, empty the entire table, InnoDB is a row of deletes, the efficiency is very slow. MyISAM will rebuild the table.

Nine, InnoDB support row locks (in some cases, the whole table, such as Update table set a=1 where user like '%lee% '

Through the above nine points, combined with the characteristics of personal blog, recommended personal blog system using MyISAM, because the main operation in the blog is read and write, there is little chain operation. So choosing the MyISAM engine makes your blog more efficient than the InnoDB engine blog, and of course it's just personal advice, most blogs are carefully chosen according to the actual situation. My station is now using the MyISAM engine!