A storage engine is a software module that has MySQL components, and that can handle the Create, Read, and Update operations for different table types to store and manage information in a database.
There are five types of tables supported by MySQL:
ISAM
- ISAM stands for Indexed Sequential Access Method.
- It has been deprecated and removed from version 5.5
- All functionality of ISAM tables is replace by MyISAM.
- The size of an ISAM table is barely 4GB.
- ISAM is not portable.
Advantages of MyISAM,
- Provides fast access to records
- Sequential access and random access.
- ISAM is a combination of sequential and direct.
Disadvantages of MyISAM
- It takes too much extra storage for index files.
- It takes too much time for maintaining index.
ISAM is required expensive hardware.
MyISAM
- If we do not specify table type when creating table at that time MySQL by default creates MyISAM type table.
- The size of MyISAM table is dependant on the OS and the data file is portable from system to system.
- MyISAM table works very fast but the transaction is not safe.
Advantages of MyISAM
- MyISAM is simple to design and create, if you are new then we would suggest you start with MyISAM.
- MyISAM is faster than InnoDB in general conditions.
- Full-text indexing / searching.
Disadvantages of MyISAM
- MyISAM doesn’t support safe transactions.
- Poor crash recovery and poor data integrity.
- When the entire table is locked, at that time MyISAM is slower than InnoDB tables because they are frequently inserted and updated.
InnoDB
- InnoDB provides the MySQL transaction safe storage engine with commit, rollback, and crash recovery capabilities.
- This is the first storage engine management in MySQL which supports foreign key.
- InnoDB transaction is safe and supports row level locking.
- It supports foreign key.
Advantages of InnoDB
- When processing large data volumes InnoDB has the maximum performance.
- For optimizing queries based on primary keys InnoDB tables arrange your data on the disk.
The disadvantage of InnoDB
- Disadvantage of InnoDB in compare with MyISAM are it take more space on disk.
HEAP
- Creates tables and those tables are stored in memory.
- If your data crashes due to hardware or power issues, or if in any circumstances MYSQL server runs out of memory or restarts, memory table data is lost.
- These tables work as temporary areas or read-only cache which is pulled from other tables.
- It doesn’t support column with
- Auto Increment
- BOB (Binary Object)
- Text character string
MERGE Storage Engine
- Collection of identical MyISAM tables is the MERGE storage engine that can be used as a single table.
- So it removes the limitation from MyISAM table.