MySQL supports various table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:
- ISAM
- MyISAM
- InnoDB
- BerkeleyDB (BDB)
- MERGE
- HEAP
The most important feature to make all the table types above distinction is transaction-safe or not. Only InnoDB and BDB tables are transaction safe and only MyISAM tables support full-text indexing and searching feature. MyISAM is also the default table type when you create table without declaring which storage engine to use. Here are some major features of each table types:
ISAM
ISAM had been deprecated and removed from version 5.x. All of its functionality is replaced by MyISAM.
MyISAM
MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data files are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.
InnoDB
Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it takes more disk space.
BDB
BDB is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.
MERGE
Merge table type is added to treat multiple MyISAM tables as a single table so it removes the size limitation from MyISAM tables.
HEAP
Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when there is power failure and sometimes it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.
