9 Differences between MySQL storage engine MyISAM and InnoDB _ MySQL

This article mainly introduces the nine differences between MySQL storage engine MyISAM and InnoDB, if you need it, you can refer to the following: Although the storage engine in MySQL is not only MyISAM and InnoDB, it is commonly used. Some webmasters may not pay attention to the MySQL Storage Engine. In fact, the storage engine is also an important point in database design. which storage engine should the blog system use? Next we will look at the differences between the two storage engines.

Differences between MySQL storage engine MyISAM and InnoDB

1. InnoDB supports transactions, but MyISAM does not. this is very important. A transaction is an advanced processing method. for example, you can roll back and restore any errors during column addition, deletion, and modification, but MyISAM cannot.

2. MyISAM is suitable for query and insert-based applications. InnoDB is suitable for applications with frequent modification and high security design.

III. InnoDB supports foreign keys, not MyISAM

4. MyISAM is the default engine and InnoDB needs to specify

5. InnoDB does not support FULLTEXT indexes.

6. InnoDB does not store the number of rows in the table. for example, when 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, MyISAM also needs to scan the entire table.

7. for a self-increasing field, InnoDB must contain only the index of this field. However, you can create a joint index with other fields in the MyISAM table.

8. when clearing the entire table, InnoDB deletes a row and a row, which is very slow. MyISAM will recreate the table

9. InnoDB supports row locks (in some cases, the whole table is locked, for example, update table set a = 1 where user like '% lee %'

We recommend that you use MyISAM in your blog system based on the above nine-point difference and the characteristics of your blog, because the main operation in the blog is reading and writing, and there are few chain operations. Therefore, selecting the MyISAM engine to enable your blog to open the page is more efficient than that of the InnoDB engine. of course, this is just my personal suggestion. most blogs are carefully selected based on actual conditions. My website currently uses the MyISAM engine!