How to choose the right MySQL storage engine

MySQL supports several storage engines as processors for different table types. The MySQL storage engine includes an engine for processing transaction security tables and an engine for processing non-transaction security tables:

MyISAM manages 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 to use another engine by default.

◆ MEMORY storage engine provides "in memory" tables. The MERGE storage engine allows collections to be processed with the same MyISAM table as a single table. Just like MyISAM1, the MEMORY and MERGE storage engines handle non-transactional tables, and both are included in MySQL by default.

Note: the MEMORY storage engine is officially identified as the HEAP engine.

◆ InnoDB and BDB storage engines provide transaction security tables. BDB is included in the MySQL-Max 2 base distribution released for the operating system that supports it. InnoDB is also included in the MySQL 5.12 base distribution by default, and you can configure MySQL to allow or disable any 1 engine to your liking.

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

NDB Cluster is a storage engine used by MySQL Cluster to split tables on multiple computers. It is available in the MySQL-Max 5.12 base distribution. This storage engine is currently supported by Linux, Solaris, and Mac OS X only. In future MySQL distributions, we would like to add support for this engine from other platforms, including Windows.

◆ the ARCHIVE storage engine is used to cover a large amount of stored data very small without index.

◆ CSV storage engine stores data in a comma-separated format in a text file.

◆ BLACKHOLE storage engine accepts but does not store data, and retrieval always returns an empty set.

The FEDERATED storage engine stores data in a remote database. In MySQL 5.1, it only works with MySQL1, using MySQL C Client API. In future distributions, we want to have it connect to another data source using another 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 an 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.

How do you choose the best storage engine for you?

The following storage engines are most commonly used:

◆ MyISAM: the default MySQL plug-in storage engine, which is one of the most commonly used storage engines in Web, data warehousing and other application environments. Note that you can easily change the default storage engine for the MySQL server by changing the STORAGE_ENGINE configuration variable.

◆ InnoDB: for transaction applications, it has many features, including ACID transaction support.

◆ BDB: a transaction engine that can replace InnoDB and supports COMMIT, ROLLBACK and other transaction features.

◆ Memory: save all data in RAM to provide fast access when you need to quickly find references and other similar data.

◆ Merge: allows MySQL DBA or developers to logically combine the 1 series equivalent MyISAM tables in 1 and reference them as an object. Suitable for VLDB environments such as data warehousing.

◆ Archive: provides a perfect solution for storing and retrieving a large amount of rarely quoted history, archiving, or security audit information.

◆ Federated: the ability to link multiple detached MySQL servers to create a logical database from multiple physical servers. 10 points is suitable for distributed or data mart environments.

◆ Cluster/NDB: MySQL's cluster database engine is especially suitable for applications with high performance lookup requirements, which also require the highest uptime and availability.

◆ Other: other storage engines include CSV (which refers to a comma-separated file used as a database table), Blackhole (which temporarily disallows application input to the database), and Example engine (which helps you quickly create custom plug-in storage engines).

It is important to remember that you do not have to use the same storage engine for the entire server or schema, but you can use a different storage engine for each table in the schema.