MySQL Storage Engines and MyISAM vs InnoDB Explained

What are Storage Engines in MySQL?

In MySQL, Storage Engines are most important components that responsible for handling the various SQL operations for different table types. Since MySQL 5.5, InnoDB is the default storage engine for MySQL tables. MySQL follows a pllugable storage engine architecture through which it is able to do the following:

• It can load storage engines into a running MySQL server.
• It can unload storage engine from a running MySQL server.

There are many storage engines are available for MySQL. To check the availability of storage engines, you can run below MySQL statement:

SHOW ENGINES

Above statement will show you list of storage engines availability. You can check the value in the support column that will tell you if the engine can be used or not.

• YES = Engine is available for use.
• NO = Engine is not available for use.
• DEFAULT : Engine is available for use and currently set as the default storage engine.

What is MyISAM Storage Engine in MySQL?

MyISAM is a non-transactional storage engine type and well-known and well-optimized for handling heavy read operations. MyISAM follows table-level locking of tables that limits the performance in read/write operations. So, most of the time MyISAM is used in read-only operations.

What is InnoDB Storage Engine in MySQL?

InnoDB is a general-purpose, transaction-safe, and super reliable storage engine with high performance. InnoDB has the following features:

• InnoDB DML operations follow the ACID model that has the power of commit, rollback, and crash-recovery which helps us in protecting user data.
• Follows row-level locking and performs Oracle-style consistent read operations that yields multi-user concurrency and performance.
• InnoDB stores and arrange user data on disk and optimize your queries based on PRIMARY KEYs.
• InnoDB supoorts FOREIGN KEY constraints that maintain the data integrity.

What are the key differences between InnoDB and MyISAM storage engines?

There are following key differences between MyISAM and InnoDB:

• MyISAM is a non-transactional type engine but InnoDB is a transactional type engine and super reliable.
• InnoDB offers ACID (Atomicity, Consistency, Isolation, Durability) properties bu MyISAM does not support this.
• MyISAM does not support FOREIGN KEY constraints but InnoDB supoorts FOREIGN KEY constraints that manage the data integrity.
• InnoDB supports row-level locking but MyISAM supports table-level locking.
• MyISAM designed for need of speed and best suited for read-only operations while InnoDB designed for maximum performance when processing high volume of data.
• MyISAM does not offer data integrity but InnoDB provides data integrity.
• MyISAM stores its tables, indexes and data in diskspace using three different types of files ({tablename}.frm, {tablename}.myd, {tablename}.myi) while InnoDB stores its tables and indexes in a tablespace.
• MYISAM supports full-text search while InnoDB has no support for full-text search.
• MyISAM key buffer only caches indexes but InnoDB large pool of buffers caches both indexes and data.