Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 26.9Mb
PDF (A4) - 27.0Mb
PDF (RPM) - 25.4Mb
HTML Download (TGZ) - 6.3Mb
HTML Download (Zip) - 6.4Mb
HTML Download (RPM) - 5.4Mb
Man Pages (TGZ) - 160.1Kb
Man Pages (Zip) - 263.2Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  Alternative Storage Engines

Chapter 15 Alternative Storage Engines

Storage engines are MySQL components that handle the SQL operations for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle nontransaction-safe tables. InnoDB is the default storage engine as of MySQL 5.5.5 (The CREATE TABLE statement in MySQL 5.5 creates InnoDB tables by default.)

MySQL uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.

To determine which storage engines your server supports, use 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.

*************************** 1. row ***************************
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

This chapter covers use cases for special-purpose MySQL storage engines. It does not cover the default InnoDB storage engine or the NDB storage engine which are covered in Chapter 14, The InnoDB Storage Engine and Chapter 18, MySQL NDB Cluster 7.2. For advanced users, this chapter also contains a description of the pluggable storage engine architecture (see Section 15.2, “Overview of MySQL Storage Engine Architecture”).

For information about features offered in commercial MySQL Server binaries, see MySQL Editions, on the MySQL website. The storage engines available might depend on which edition of MySQL you are using.

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

MySQL 5.5 Supported Storage Engines

  • InnoDB: The default storage engine as of MySQL 5.5.5. InnoDB is 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. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.

  • MyISAM: The MySQL storage engine 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 prior to MySQL 5.5.5.

  • 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: Enables 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.

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


    The NDB storage engine is not supported in standard MySQL 5.5 releases. Currently supported NDB Cluster releases include MySQL NDB Cluster 7.0 and MySQL NDB Cluster 7.1, which are based on MySQL 5.1, and MySQL NDB Cluster 7.2, which is based on MySQL 5.5. While based on MySQL Server, these releases also contain support for NDB.

  • 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. The following table provides an overview of some storage engines provided with MySQL, with clarifying notes following the table.

Table 15.1 Storage Engines Feature Summary

Feature MyISAM Memory InnoDB Archive NDB
B-tree indexes Yes Yes Yes No No
Backup/point-in-time recovery (note 1) Yes Yes Yes Yes Yes
Cluster database support No No No No Yes
Clustered indexes No No Yes No No
Compressed data Yes (note 2) No Yes Yes No
Data caches No N/A Yes No Yes
Encrypted data Yes (note 3) Yes (note 3) Yes (note 4) Yes (note 3) Yes (note 3)
Foreign key support No No Yes No Yes (note 5)
Full-text search indexes Yes No Yes (note 6) No No
Geospatial data type support Yes No Yes Yes Yes
Geospatial indexing support Yes No Yes (note 7) No No
Hash indexes No Yes No (note 8) No Yes
Index caches Yes N/A Yes No Yes
Locking granularity Table Table Row Row Row
MVCC No No Yes No No
Replication support (note 1) Yes Limited (note 9) Yes Yes Yes
Storage limits 256TB RAM 64TB None 384EB
T-tree indexes No No No No Yes
Transactions No No Yes No Yes
Update statistics for data dictionary Yes Yes Yes Yes Yes


1. Implemented in the server, rather than in the storage engine.

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

3. Implemented in the server via encryption functions.

4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.

5. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

6. InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.

7. InnoDB support for geospatial indexing is available in MySQL 5.7 and later.

8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

9. See the discussion later in this section.