MySQL engines comparison

MySQL engines comparisonMySQL operations are generated by storage engines, also known as table types. One database can include several engines that can be of a different type or identical. The most common used table type is Innodb, which is the default program used by the latest version of MySQL 5.7 mainly because of its abilities to recover at a fast rate and protect the user data following a crash. One of the most preferred engines by developers to go with Innodb is MyISAM, which has been the original default table type for prior MySQL versions. Here is a short comparison of the two engines:

ACID reliability

The main characteristics of a MySQL engine are Atomicity, Consistency, Isolation and Durability (ACID). Together, they form a referential point for the creation of any database. Innodb is ACID-compliant and also fully transactional with ROLLBACK and COMMIT, as well as a reliable tool for Foreign Keys. On the other hand, MySQL lacks these abilities and it is not manageable with the ACID features.

Recovery options

Both engines offer compression. However, they have different ways of recovering after a crash. Innodb replays logs in order to recover automatically, while MyISAM requires a complete repair and a full rebuild of all its tables and indexes.

Data ordering

Developers have different preferences when it comes to the method of data ordering. Some do not consider this as an important aspect, so they choose MyISAM as a table type because it does not follow an order in storage of data. Innodb, on the other hand, stores data in rows on pages in PK order. This engine also allows row-level locking, as opposed to MyISAM which only provides table-level locking.

Both engines offer a variety of tools to work with. Innodb can convert changed pages from random to sequential before flushing to disk, which increases the security of data. However, MyISAM is a faster option for storage engines due to its fixed row size and small footprint.