Storage Engines in MySQL

MySQL is an open-source RDBMS (Relational Database Management System) which is widely used RDBMS after Oracle. It supports different storage engines and these storage engines have their own properties. In this article, we will discuss few storage engines of MySQL.

InnoDB Storage Engine: It is default storage engine for MySQL 5.5 and later versions. File formats of this storage engine are .idb, .frm and .ibdata. InnoDB provides the compliant transaction function of ACID, along with foreign key support. It has the ability to maintain its own buffer pool for indexing in the main memory and data caching. Main goals of InnoDB storage engine are Portability, data security, OLTP orientation, performance, reliability, and scalability etc.

Key functional characteristics:

• Auto recovery from crash
• Efficient IO
• Full transactional support (COMMIT and ROLLBACK)
• MVCC (Multiversion Concurrency Control)
• Row-level locking
• Row data stored in the primary key format
• User can freely mix the InnoDB tables from other storage engines

Disadvantage of InnoDB Storage Engine

• It Doesn’t support Hash Indexes and Full-text search indexes
• No cluster database support
• It is not available in classic edition

The Architecture of InnoDB Storage Engine: InnoDB architecture is well-suited to modern, online transactional applications as well as embedded applications. Architecture of the InnoDB storage engine is shown in the below:

MyISAM Storage Engine: It was the default storage engine for the prior 5.5 version for MySQL database. MySQL uses the .frm file to store the definition of the tables so this is also supported by MyISAM. The main file extensions for this storage engine are.MYD (MYData) and .MYI (MYIndex). MyISAM is the improved version of MySQL, ISAM. After MySQL 3.23, MyISAM replaced ISAM as the default storage engine.

Advantages of MyISAM Storage Engine:

• Simplicity: It is very easy to use especially for a newbie. 
• Speed: It is very fast under the normal condition.
• Full-text Searching

Disadvantages of MyISAM Storage Engine: 

• It does not support transaction or foreign key constraints.
• It has a poor crash recovery which can be fixed using REPAIR TABLE, but it needs an additional administrative overhead. 
• It uses table locking feature. In this feature, when a row is inserted or updated then the other modification on the table helps up until that particular request has been completed.
• No order for the data storage

Falcon Storage Engine: Falcon storage engine was specially developed for MySQL RDBMS. When Oracle purchased MySQL then its development was stopped. The main feature of Falcon is transactions.

Advantages of Falcon Storage Engine:

• It supports COMMIT/ROLLBACK
• Row level concurrency by MVCC
• Non-blocking read & crash recovery
• Fine Grained multi-threading
• Use threads and processor for the data migration
• Support web application

Disadvantages of Falcon Storage Engine:

• It does not use clustered index architecture 
• It does not support “Dirty” reads isolation level
• It does not support statement based replication

MySQL Federated Storage Engine: This storage engine gives the facility to create the table form representation of foreign (Remote) tables.

Advantages of MySQL Federated Storage Engine:

• Useful for ad-hoc queries
• Server side replication filtering
• Useful for replicate the triggers actions from the other non-replicated database

Disadvantages of MySQL Federated Storage Engine:

• The remote server must be a MySQL server
• No support for transactions
• Federated tables do not support indexes
• It does not support HANDLER (It provides direct access to table storage engine interface)