MySQL storage engine is a software module that a database management system uses to create, update and read data from a database.
=> From MySQL 5.5 and later version, the default storage engine is innoDB. Before of MySQL 5.5, MyISAM was the default storage engine.
Choosing the right storage engine is an important strategic decision as it impact future development.
There are two types of storage engines:
1. Transactional Storage Engines:
Transactional storage engines allows the database operations to rollback if they fail to complete.
2. Non-transactional Storage Engines:
There is no rollback or commit operations. In order to perform rollback operations the user will have to write their own codes.
MySQL provides various storage engines for its tables :
- Memory (Heap)
InnoDB is the most widely used storage engine. It is the default storage engine since MySQL 5.5.
- It supports transactions.
- Full ACID complaint storage engine. (Atomicity, Consistency, Isolation, Durability)
- Commit, rollback and crash-recovery.
- Row-level locking.
- Supports foreign-keys referential integrity constraints
- It increases multi-user concurrency.
- It also checks and repairs InnoDB tables at startup.
MyISAM storage engine was the default storage engine used up to MySQL version 5.5. It is faster database engine as compared to others storage engine.
- In MyISAM, tables are optimized for compression and speed.
- It supports full text search indexes.
- Supports table-level locking.
- MyISAM storage engine doesn’t support transaction.
The memory storage engine tables are stored in memory and use hash indexes.
- Memory tables are faster than MyISAM tables.
- Memory storage engine is formerly known as HEAP.
- In memory storage engine, the data can be crashed due to hardware or power issues.
- It can be used only for temporary work areas or read-only catches.
The CSV storage engine stores data in tables in “Comma-separated-values(CSV)” file format.
- Using CSV, you can easily migrate data from non-SQL applications to tables.
- The CSV files are not indexed. So, the data should be stored in InnoDB table until the import/export process.
The Merge storage engine, introduced in MySQL to provides a way to combine many identical MyISAM tables that can be used as single table.
In Merge storage engine, You can use SELECT, DELETE, UPDATE and INSERT statements on the collection of tables.
The archive storage engine uses the gzip to compress rows. It is mainly used to store large amount of data , without indexes.
In Archive storage engine, only the data can be inserted.
- It supports INSERT and SELECT only.
- Data is compressed in Archive storage engine.
- It does not support foreign keys.
- It does not support transactions.
- There is no storage limit.
- In archive storage engine, row-locking is supported.
The Federated MySQL storage engine enables you to access data from a remote MySQL database.
In Federated mysql storage engine, remote server must be a MySQL server.
- In Federated mysql engine, table does not support indexes.
- It supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, and indexes.
- It does not support ALTER table statement or any other DDL statements.
- It does support DROP TABLE statement.
- It does not support Transaction.
The Blackhole storage engine, behaves as a “black hole” that means accepts data only but does not store it.
- It returns an empty result.
- INSERT, UPDATE and DELETE statements works with the Blackhole storage engine.
- It does not support foreign key.
- It does not maintain the AUTO_INCREMENT value.