Storage engines or table types in MySQL are used to define how a database table will store data.

Each storage engine has advantages and disadvantages and it is important to understand what they mean, and when to use it in order to achieve the best possible performance of our database.

These storage engines are provided by MySQL:

MyISAM

MyISAM is based on ISAM storage engine and it is optimized for compression and speed.

These engines are transferable between platforms, they may be compressed to save space, and they are able to repair errors (if any) at the startup.

A big advantage of MyISAM engines is also a large storage capacity of 256TB.The disadvantage is that they are not transaction friendly.

Before MySQL 5.5, MyISAM was a default storage engine installed at the installation of MySQL server.

InnoDB

The InnoDB tables are used in a need of good / optimal performance that is also fully ACID-compliant and supports transactions. These engines support foreign keys, commit, rollback, and roll-forward operations. They are also portable between platforms, and the total possible size of an InnoDB table is 64TB.

Since the MySQL 5.5 and higher, the InnoDB became a default storage engine.

MERGE

This engine is a virtual engine that combines multiple MyISAM tables with similar structures into a single table. This table does not have its own indexes, but it uses other tables' indexes. The MySQL allows usage of SELECT, DELETE, UPDATE, and INSERT statements on the merged tables; while DROP statement only drops the MERGE specification (the tables will not be affected).

Other name for MERGE types is MRG_MyISAM.

MEMORY

These tables will be stored in memory so they will be faster than MyISAM ones. The lifetime of the Memory tables depends on the up-time of the database server.

The older name for MEMORY storage is HEAP.

ARCHIVE

The ARCHIVE engine is good for storing a large number of records, which will be saved as compressed format to save space. The archive engine compresses an inserted record and decompresses it using zlib library as it is read.

This engine allows usage of only INSERT and SELECT statements and does not support indexing, therefore forcing us to scan a full table each time.

BLACKHOLE

The BLACKHOLE storage engine is created to accept data without actually storing it, therefore simulating a "black hole". Although it sounds confusing, these engines may be used as repeaters or filters. To simplify the idea, we may create a BLACKHOLE table that will receive data from a master table, filter it the way we want, and replicate (send) to a slave database on another location used as, let say, a backup.

These engines may be approached by using INSERT statements, but not by using UPDATE or DELETE ones.

CSV

The CSV engine is a Comma-Separated Values files format. As this format is widely used in spreadsheets (such as MS EXCEL), it is a convenient table type for migration of non-SQL application.

CSV tables do not support NULL data type, and require a full table scan.

FEDERATED

This engine allows managing data from a remote MySQL server without using clusters or replication technology. The local federated tables do not store data, but rather pull data directly from a remote table.

Creating table with MEMORY storage engine:

CREATE TABLE lookup

   (id INT, INDEX USING HASH (id))

   ENGINE = MEMORY;

CREATE TABLE lookup

   (id INT, INDEX USING BTREE (id))

   ENGINE = MEMORY;