MySQL Table Types (Storage Engine)

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.


Next Recommended Reading MYSQL Database Backup / Restore