MySQL has six table types: ISAM, MyISAM, InnoDB, BDB, MERGE, and HEAP.
Only InnoDB and BDB tables are transaction safe.
Only MyISAM tables support full-text indexing and searching.
ISAM
ISAM had been deprecated and superceded by MyISAM.
ISAM tables have a hard size limit of 4GB.
ISAM tables are not portable.
You can have a maximum of 16 keys per table and a maximum key length of 256 bytes ( characters ).
MyISAM
MyISAM is the default table type. It is very fast, but not transaction safe.
MyISAM tables support table compression.
The size of MyISAM tables is limited only by the operating system, and this can be worked around with MERGE tables.
The data files that store MyISAM tables are portable from system to system.
You can have a maximum of 64 keys per table and a maximum key length of 1024 bytes.
InnoDB
InnoDB tables are transaction safe.
InnoDB supports row-level locking.
There is no theoretical maximum table size because tables may be stored in more than one file.
InnoDB provides consistent nonlocking reads in SELECT .
InnoDB tables are portable from system to system.
InnoDB tables take more disk space than MyISAM tables.
Foreign keys are supported between InnoDB tables.
BDB
Like InnoDB tables, BDB tables are transaction safe. BDB tables are not as widely used with MySQL as InnoDB.
BDB supports page-level locking.
BDB tables are not portable.
MERGE
MERGE tables are used to treat multiple MyISAM tables as a single table, and therefore, the maximum file size limitation is removed from MyISAM tables.
HEAP
HEAP tables are stored only in memory and need to be limited in size to avoid running out of memory.
Data stored in a HEAP table is volatile and will be lost in the event of a power failure.
HEAP tables are super fast, as long as you have enough physical memory to keep them.
HEAP tables do not support AUTO_INCREMENT , TEXT , or BLOB .
