If you’ve decided to use MySQL database for your enterprise application, you should wisely choose the MySQL storage engine type that will be appropriate for your situation.
Depending on the situation, a particular storage engine can be a best fit or worst fit.
Once you understand the differences between all MySQL storage engines, it will help you to take advantage of the flexibility of using different storage engines for different tables.
With the right selection of the MySQL engine that suites your requirements, you can get excellent performance, and can optimize your MySQL database effectively.
- MyISAM is best suited for high “select” query rate, and non transactional operations.
- InnoDB is best suited for parallel insert/update/delete operations (because of row level locking), and transactional operations (because of roll back feature).
- Memory engine (HEAP) is best suited for on the fly fast data access, as everything is stored in the RAM.
- MyISAM is worst for high “insert/update” query rate. (because of table level locking).
- InnoDB is worst when there is combination of non-transactional and read only operations.
- Memory engine is worst for long term usage (because Of data integrity issues) and transactional operations.
The following table summarizes the key differences between these three types of engines. Only those features that differs between these engines are listed, excluding those features that are present in all three engines.
|Configurable ACID Properties||No||Yes||No|
|Crash Safe||No||Yes||No (RAM)|
|Foreign Key Support||No||Yes||No|
|Multi Version Concurrency Control (MVCC)||No||Yes||No|
|Full-text Search Index||Yes||No||No|