MySQL - Storage Engine

InnoDB is a transaction-safe engine. It also support row level locking. It does not support full-text search.

MyISAM is a high-performance engine. It supports full-text searching, but does not support transactional processing or row level locking.

MEMORY is functionally equivalent to MyISAM, but data is stored in memory instead of on disk. It is extremely fast, and ideally suited for temporary tables.

A database can consist of tables each with different engine type. Foreign keys however can't span engine types. That is, a table using one engine cannot have a foreign key referring to a table that use another engine.

Each table has a format (.frm) file in the database directory regardless of storage engine. This file stores the definition of the table's structure, and is created by the server.

Each storage engine has a particular set of operational characteristics. Engines may create additional disk files to accompany the .frm file, but the types of files that they create to manage data and index storage vary per engine. Storage engines differ in other ways as well, such as in the way that they use locking to manage query contention, or in whether the tables that they provide are transactional or non-transactional. These engine properties have implications for query processing performance, concurrency, and deadlock prevention.

When you create a table, you can choose what storage engine to use.

MyISAM table-level locking works best if your application mostly read from the table (few writes). Use InnoDB if your application update the table many times. InnoDB's use of row-level locking and multi-versioning provides good concurrency for a mix of retrievals and updates.

CREATE TABLE t (i INT) ENGINE = InnoDB;

If you create a table without specifying a storage engine, the server creates the table using the default engine, which is given by the value of storage_engine system variable.

To determine which storage engine is used for a given table, you can use the SHOW CREATE TABLE or the SHOW TABLE STATUS:

SHOW CREATE TABLE City;
SHOW TABLE STATUS LIKE 'CountryLanguage';

Some storage engines are always available, such as MyISAM, MERGE, and MEMORY. Other engines are optional. Support for optional engines typically can be selected when MySQL is configured and built. Compiled-in optional engines can typically be enabled or disabled with a server startup option.

InnoDB storage engine is included in all binary distributions. If you build MySQL from source, InnoDB is included by default, unless you specify the —without-innodb configuration option. For a server that has InnoDB storage engine included, support may be disabled at startup with the —skip-innodb option.

To reduce memory usage, don't configure unneeded storage engines into the server. This requires that you compile from source rather than using a precompiled binary distribution. If you are using binary distribution, you can disable optional engines with a startup option.

To see what storage engines are compiled into your server and whether they are available at runtime:

SHOW ENGINES;

MyISAM
MERGE
InnoDB
MEMORY
FEDERATED
NDBCluster
BDB
ARCHIVE
CSV
BLACKHOLE
EXAMPLE

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License