The difference between MySQL storage engine MyISAM and InnoDB

First, INNODB support affairs, MyISAM not support, this is very important. Transactions are an advanced way of handling, such as in some column additions and deletions, as long as the error can be rolled back to restore, and MyISAM will not be.

Second, MyISAM suitable for query and insert-based applications, InnoDB suitable for frequent modification and design to high security applications

Third, INNODB support foreign key, MyISAM not support

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

V. INNODB does not support indexes of fulltext types

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

Vii. for self-growing fields, InnoDB must contain only the index of the field, but you can establish a federated index with other fields in the MyISAM table

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

Ix. InnoDB supports row locks (or, in some cases, lock an entire table, such as Update table set a=1 where user like '%lee% ')

MyISAM also uses a form-locking mechanism to optimize multiple concurrent read and write operations, at the cost of running the Optimize Table command frequently to restore space wasted by the updated mechanism

Generally speaking: MyISAM suitable for: (1) do a lot of count calculation, (2) Insert infrequently, query very frequently; (3) No transaction.

InnoDB Suitable for: (1) High reliability requirements, or request transactions, (2) Table updates and queries are fairly frequent, and table locking opportunities are relatively large cases.

By the above 10 points of difference, combined with the characteristics of their own projects, you can basically determine the required engine!

The difference between MySQL storage engine MyISAM and InnoDB