Skip navigation links
MySQL 5.1 Reference Manual :: 13 Storage Engines
13.1 Comparing Transaction and Nontransaction Engines »
Section Navigation      [Toggle]

Chapter 13. Storage Engines

Table of Contents     [+/-]

13.1. Comparing Transaction and Nontransaction Engines
13.2. Other Storage Engines
13.3. Setting the Storage Engine
13.4. Overview of MySQL Storage Engine Architecture     [+/-]
13.5. The MyISAM Storage Engine     [+/-]
13.6. The InnoDB Storage Engine     [+/-]
13.7. The IBMDB2I Storage Engine     [+/-]
13.8. The MERGE Storage Engine     [+/-]
13.9. The MEMORY (HEAP) Storage Engine
13.10. The EXAMPLE Storage Engine
13.11. The FEDERATED Storage Engine     [+/-]
13.12. The ARCHIVE Storage Engine
13.13. The CSV Storage Engine     [+/-]
13.14. The BLACKHOLE Storage Engine

MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle nontransaction-safe tables.

As of MySQL 5.1, MySQL Server uses a pluggable storage engine architecture that allows storage engines to be loaded into and unloaded from a running MySQL server.

Note

Prior to MySQL 5.1.38, the pluggable storage engine architecture is supported on Unix platforms only and pluggable storage engines are not supported on Windows.

To determine which storage engines your server supports by using the SHOW ENGINES statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine. 

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
...

This chapter describes each of the MySQL storage engines except for NDBCLUSTER, which is covered in Chapter 17, MySQL Cluster NDB 6.X/7.X. It also contains a description of the pluggable storage engine architecture (see Section 13.4, “Overview of MySQL Storage Engine Architecture”).

For information about storage engine support offered in commercial MySQL Server binaries, see MySQL Enterprise Server 5.1, on the MySQL Web site. The storage engines available might depend on which edition of Enterprise Server you are using.

For answers to some commonly asked questions about MySQL storage engines, see Section A.2, “MySQL 5.1 FAQ — Storage Engines”.

MySQL 5.1 supported storage engines

  • MyISAM — The default MySQL storage engine and the one that is used the most in Web, data warehousing, and other application environments. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.

  • InnoDB — A transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

  • Memory — Stores all data in RAM for extremely fast access in environments that require quick lookups of reference and other like data. This engine was formerly known as the HEAP engine.

  • Merge — Allows a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object. Good for VLDB environments such as data warehousing.

  • Archive — Provides the perfect solution for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

  • Federated — Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Very good for distributed or data mart environments.

  • NDBCLUSTER (also known as NDB) — This clustered database engine is particularly suited for applications that require the highest possible degree of uptime and availability.

    Note

    The NDBCLUSTER storage engine is not supported in standard MySQL 5.1 releases. MySQL Cluster users wishing to upgrade from MySQL 5.0 should instead migrate to MySQL Cluster NDB 6.2 or 6.3; these are based on MySQL 5.1 but contain the latest improvements and fixes for NDBCLUSTER.

  • CSV — The CSV storage engine stores data in text files using comma-separated values format. You can use the CSV engine to easily exchange data between other software and applications that can import and export in CSV format.

  • Blackhole — The Blackhole storage engine accepts but does not store data and retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally.

  • Example — The Example storage engine is “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.

It is important to remember that you are not restricted to using the same storage engine for an entire server or schema: you can use a different storage engine for each table in your schema.

Choosing a Storage Engine

The various storage engines provided with MySQL are designed with different use-cases in mind. To use the pluggable storage architecture effectively, it is good to have an idea of the advantages and disadvantages of the various storage engines. The following table provides an overview of some storage engines provided with MySQL:

Table 13.1. Storage Engine Features

Feature MyISAM Memory InnoDB Archive NDB
Storage limits 256TB RAM 64TB None 384EB
Transactions No No Yes No Yes
Locking granularity Table Table Row Row Row
MVCC No No Yes No No
Geospatial datatype support Yes No Yes Yes Yes
Geospatial indexing support Yes No No No No
B-tree indexes Yes Yes Yes No Yes
Hash indexes No Yes No No Yes
Full-text search indexes Yes No No No No
Clustered indexes No No Yes No No
Data caches No N/A Yes No Yes
Index caches Yes N/A Yes No Yes
Compressed data Yes[a] No Yes[b] Yes No
Encrypted data[c] Yes Yes Yes Yes Yes
Cluster database support No No No No Yes
Replication support[d] Yes Yes Yes Yes Yes
Foreign key support No No Yes No No
Backup / point-in-time recovery[e] Yes Yes Yes Yes Yes
Query cache support Yes Yes Yes Yes Yes
Update statistics for data dictionary Yes Yes Yes Yes Yes

[a] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

[b] Compressed InnoDB tables are supported only by InnoDB Plugin.

[c] Implemented in the server (via encryption functions), rather than in the storage engine.

[d] Implemented in the server, rather than in the storage product

[e] Implemented in the server, rather than in the storage product

User Comments

Posted by Adrian Singer on January 26 2008 1:15pm[Delete] [Edit]

More information about how to pick the best MySQL Storage engine for your real life scenario:

http://www.softwareprojects.com/resources/programming/t-mysql-storage-engines-1470.html

Add your own comment.