MySQL storage engine MyISAM and InnoDB differences

The two most commonly used engines in the Mysql database are Innordb and MyISAM. InnoDB is the default storage engine for MySQL.

The difference between the two:

1. Aspects of transaction processing
MyISAM: The emphasis is on performance, which is faster than the InnoDB type, but does not provide transactional support.
InnoDB: provides transactional support.

2. Foreign key
MyISAM: Foreign keys are not supported,

InnoDB: Supports foreign keys.

3. Lock
MyISAM: Only table-level locks are supported,

InnoDB: Row-level and table-level locks are supported, row-level locks are default, row locks are significantly improves the performance of multiple user concurrency operations. InnoDB is more suitable for more insert and update operations,
MyISAM, however, is suitable for frequently queried situations. In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table .
For example: Update table set num=1 where name like "%aaa%".

4. Full-Text Indexing
MyISAM: Supports full-text indexing,

InnoDB: Full-text indexing is not supported. InnoDB Support for full-text indexing starts with the mysql5.6 version.

5. Table PRIMARY Key
MyISAM: Allows a table with no primary key to exist.
InnoDB: If no primary key is set, a 6-byte primary key is automatically generated (the user is not visible).

6. The exact number of rows in the table
myisam:select Count (*) from Table,myisam simply read out the number of rows saved. Because MyISAM has a built-in counter, COUNT (*) reads it directly from the counter.
InnoDB: The exact number of rows of the table is not saved, that is, when the select count (*) from table is executed, InnoDB to scan through the entire table to calculate how many rows.

MySQL storage engine MyISAM and InnoDB differences