- ISAM
- MyISAM
- InnoDB
- BerkeleyDB (BDB)
- MERGE
- HEAP
- 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.
ISAM
ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.
MyISAM
- This is default type when you create table.
- MyISAM table work very fast but not transaction-safe.
- The size of table depends on the OS and the data file are portable.
- Hard size - 64 keys per table and maximum key length of 1024 bytes.
InnoDB
- InnoDB table are transaction safe.
- 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.
Disadvantage - In comparison with MyISAM is it take 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 remove 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 the power failure and sometime it can cause the server run out of memory.
Disadvantage: Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.
