MySql Database Engines – InnoDB and MyISAM

A database storage engine is a component which determines the way data is structured in the database and this structure determines the way CRUD is done on the database. MySql has different types of engines of which InnoDB and MyISAM are the most heard of.
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB replaced MyISAM as MySql’s┬ádefault storage engine after 5.5 release.

MyISAM and InnoDB:

When we have a scenario with very less number of writes and heavy read and massive querying, MyISAM fits the best. It has a feature of table lock which on any write operation locks the whole table and then writes. So, in case we have an application which is write heavy will suffer a lag because of the sequential write with a table lock. When a table is table locked, only one query can run against it at one time. This is where a nice robust hardware support would be required.

MyISAM also does not support atomic transactions which is a key feature of (one of ACIDs) a database. You cannot commit and rollback with MyISAM. Due to this, our data can go into an inconsistence state and we might have to face serious issues. Let’s say you issue an update query which has to update a lot many rows. The table is locked and the data is being updated. In the mean time, some connection error occurs because of which the querying is interrupted and you know you are in a trouble. Imagine the situation when you have to update your table frequently. MyISAM does not guarantee any data integrity. InnoDB on the other hand supports ACID feature. It uses transactions and validations to ensure data doe not corrupt. In case of any crash, the database will recover itself to the last save point. For maintainance MyISAM has some commands which one should run on regular intervals – Optimize, Analyze, Check etc.

Comparing the sizes the tables take in both storage engines, MyISAM tables take less space because of the less overheads. The data can further be compressed if it is sure to be ready only. It also does not support/use foreign keys as a concept. InnoDB has to do a lot of house keeping because of its relational nature, so the data size is relatively higher.

There are very limited options available for instrumentation and tweaking MyISAM. There are some cool aspects of using MyISAM, ex: there is not deadlock. MyISAM has seen very limited development in past years. One of the crash-safe database extensions of MyISAM is MariaDB which uses storage engine ARIA.