MySQL storage engines

MySQL supports the following the table types or storage engines:
1. ISAM
2. MyISAM
3. InnoDB
4. BerkeleyDB(BDB)
5. MERGE
6. HEAP

While considering the table types you have to mainly focus on the following factors:
1. Transaction safe
2. Full text and search features

The default table type of MYSQL in MyISAM. This means that if you have created the table without mentioning the table type, then the type of that table will be MyISAM.

ISAM:
This is not a portable storage engine and is removed from MySQL version 5.x. Its functionality is now replaced by the MyISAM. It supports only 4GB size for storage.

MyISAM:
This is the default storage engine for the MYSQL. This is a fast storage engine but this storage engine is not transaction safe. The size of the MyISAM table varies with different operating systems and the data file too varies from system to system. With the table of this type you can allocate the 64 keys and the length of the key is 1024 bytes.

InnoDB:
Apart from MyISAM, the table of the Innodb type is transaction safe and offers row-level locking. Referential integrity is also supported by the InnoDB storage engine. Unlike  MYISAM, the size of the table is depends on the disk space. Drawback of the InnoDB is that it takes more disk space.

BDB:
This storage engine is transaction safe . It supports page level locking but the data file used in this storage engine is not portable.

Merge:
This storage engine is a collection of multiple MyISAM tables in a single table in order to remove the size limitation from the MyISAM storage engine.

Heap:
This is the fastest storage engine as the table of the HEAP storage engine is stored in memory rather than the disk space. Though this can cause data loss in case of power failure or out of memory error. Also the HEAP storage engine does not support the columns that have AUTO-INCREMENT, BLOB and TEXT as data types.

MyISAM:
This is the default storage engine for the MYSQL. This is the fast storage engine but this storage engine is not transaction safe. The size of the MyISAM table varies from operating system to operating system and the data file varies from system to system. With the table of this type you can allocate the 64 keys and the length of the key is 1024 bytes.

InnoDB:
Apart from MyISAM , the table of the Innodb type is transaction safe and offers row-level locking. Referential integrity is also supported by the InnoDB storage engine. Also diferent from the MYISAM the size of the table is depends on the disk space. Similar to MyISAM InnoDB varies from system to system. Drawback of the InnoDB is that it takes more disk space.

BDB:
This storage engine is transaction safe . It supports the page level locking but the data file used in this storage engine are not portable.
Merge:
This storage engine is nothing but the collection of the multiple MyISAM table in single table in order to remove the size limitation from the MyISAM storage engine.

Heap:
This is the fastest storage engine . The reason behind this is that the table of the HEAP storage engine is stored in memory rather than disk space. But this cause the data loss in case of power failure , out of memory error . Also the HEAP storage engine is not supporting the columns haiving AUTO-INCREMENT , BLOB and TEXT as data types.

This entry was posted in MySQL and tagged , , , , . Bookmark the permalink.

Leave a Reply