MySQL Table Types

MySQL supports several table types which allow to user to optimize the database. There are the following table types are available in MySQL:

1. ISAM
2. MyISAM
3. InnoDB
4. BerkeleyDB (BDB)
5. MERGE
6. HEAP

ISAM: ISAM had been deprecated and removed from version 5.x. All of it functionality integral replace by MyISAM. ISAM table has a hard size 4GB and is not portable.

MyISAM: MyISAM table type is default type when you make table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table relies upon the operating system and the data file are portable from system to system. MyISAM tables support full-text indexing and searching feature.

InnoDB: InnoDB table are transaction safe, holds row-level locking and foreign keys are supported in this type of 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.

BDB: BDB is similar to InnoDB in transaction safe. It carries page level locking but data file are not portable.

MERGE: Merge table type is contributed to cover 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. Heap tables do not back up columns with AUTO_INCREMENT, BLOB and TEXT characteristics.