How to choose the right MySQL storage engine

MySQL has a variety of storage engines:

MyISAM, InnoDB, MERGE, MEMORY (HEAP), BDB (BerkeleyDB), EXAMPLE, Federated, ARCHIVE, CSV, Blackhole.

MySQL supports several storage engines as processors for different types of tables. The MySQL storage engine includes the engine that handles the transaction security table and the engine that handles the non-transactional security table:

MyISAM Manage non-transaction tables. It provides high-speed storage and retrieval, as well as full-text search capabilities. MyISAM is supported in all MySQL configurations and is the default storage engine unless you configure MySQL by default to use another engine.

The memory storage engine provides an in-memory table. The merge storage engine allows the collection to be processed by the same MyISAM table as a separate table. Like MyISAM, memory and the merge storage engine handle non-transaction tables, both of which are included by default in MySQL.

Note: The memory storage engine is officially identified as the heap engine.

The InnoDB and BDB storage engines provide transaction security tables. BDB is included in the Mysql-max binary distribution that is released for the operating system that supports it. InnoDB is also included in all MySQL 5.1 binary distributions by default, and you can configure MySQL to allow or disable any engine as you prefer.

The example storage engine is a "stub" engine, and it does nothing. You can use this engine to create a table, but no data is stored in or retrieved from it. The purpose of this engine is to serve an example in the MySQL source code, which demonstrates how to start writing a new storage engine. Again, its main interest is for developers.

NDB cluster is a storage engine that is used by MySQL cluster to implement tables that are split onto multiple computers. It is available in the Mysql-max 5.1 binary distribution. This storage engine is currently supported only by Linux, Solaris, and Mac OS X. In the future MySQL distribution, we want to add other platforms to support this engine, including Windows.

The archive storage engine is used to easily overwrite large amounts of stored data without indexing.

The CSV storage engine stores data in a comma-delimited format in a text file.

The Blackhole storage engine accepts but does not store the data, and the retrieval always returns an empty set.

The Federated storage Engine has the data in the remote database. In MySQL 5.1, it works only with MySQL, using the MySQL C Client API. In a future distribution, we would like to have it connect to another data source using a different drive or client connection method.

When you create a new table, you can tell MySQL what type of table you want to create by adding a engine or type option to the CREATE TABLE statement:

CREATE TABLE T (i INT) ENGINE = INNODB;

CREATE TABLE T (i INT) TYPE = MEMORY;

Although type is still supported in MySQL 5.1, engine is now the preferred term.