Differences between MyISAM and InnoDB storage engines
In this table I tried to show the difference between MyISAM and InnoDB with simple examples:
| Description | MyISAM | InnoDB |
|---|---|---|
| Transactional engine | No | Yes |
| Foreign key support | No | Yes |
| Locking | Table-level locking | Row-level locking |
| Concurrent queries to different parts of a table | Slower | Faster |
| Mixed load (SELECT/UPDATE/DELETE/INSERT) | Slower | Faster |
| INSERT operations | Faster | Slower due to transaction overhead — the cost of reliability |
| Mostly read operations (SELECT) | Faster | Slower |
| Deadlocks | Do not occur | Possible |
| Full-text search support | Yes | No (available since MySQL 5.6.4) |
COUNT(*) query |
Faster | Slower |
mysqlhotcopy support |
Yes | No |
| Table file storage | Separate file per table | By default, data is stored in large shared files |
| Binary copy of tables? | Yes | No |
| Table size in the database | Smaller | Larger |
| Behavior on failure | The whole table can crash | Can be recovered from logs |
| Storing “logs” and similar data | Better | Worse |
Conclusions:
- Prefer MyISAM for tables dominated by one access pattern: reads (news site) or writes (for example, logging).
- Prefer InnoDB in all other cases, and whenever data durability matters more.