Chapter 14 Storage Engines

Table of Contents

14.1 Comparing Transaction and Nontransaction Engines
14.2 Other Storage Engines
14.3 Setting the Storage Engine
14.4 Overview of MySQL Storage Engine Architecture
14.4.1 Pluggable Storage Engine Architecture
14.4.2 The Common Database Server Layer
14.5 The MyISAM Storage Engine
14.5.1 MyISAM Startup Options
14.5.2 Space Needed for Keys
14.5.3 MyISAM Table Storage Formats
14.5.4 MyISAM Table Problems
14.6 The InnoDB Storage Engine
14.6.1 Introduction to InnoDB
14.6.2 InnoDB Configuration
14.6.3 InnoDB Concepts and Architecture
14.6.4 InnoDB Transaction Model and Locking
14.6.5 InnoDB Tablespace Management
14.6.6 InnoDB Table Management
14.6.7 InnoDB Disk I/O and File Space Management
14.6.8 InnoDB Startup Options and System Variables
14.6.9 InnoDB Monitors
14.6.10 InnoDB Backup and Recovery
14.6.11 InnoDB and MySQL Replication
14.6.12 InnoDB Troubleshooting
14.7 The IBMDB2I Storage Engine
14.7.1 Installation
14.7.2 Configuration Options
14.7.3 Creating schemas and tables
14.7.4 Database/metadata management
14.7.5 Transaction behavior
14.7.6 Principles and Terminology
14.7.7 Notes and Limitations
14.7.8 Character sets and collations
14.7.9 Error codes and trouble-shooting information
14.8 The MERGE Storage Engine
14.8.1 MERGE Table Advantages and Disadvantages
14.8.2 MERGE Table Problems
14.9 The MEMORY Storage Engine
14.10 The EXAMPLE Storage Engine
14.11 The FEDERATED Storage Engine
14.11.1 FEDERATED Storage Engine Overview
14.11.2 How to Create FEDERATED Tables
14.11.3 FEDERATED Storage Engine Notes and Tips
14.11.4 FEDERATED Storage Engine Resources
14.12 The ARCHIVE Storage Engine
14.13 The CSV Storage Engine
14.13.1 Repairing and Checking CSV Tables
14.13.2 CSV Limitations
14.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 enables storage engines to be loaded into and unloaded from a running MySQL server.


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.

*************************** 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.1 - 7.1. It also contains a description of the pluggable storage engine architecture (see Section 14.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

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 14.1 Storage Engine Features

Storage limits256TBRAM64TBNone384EB
Locking granularityTableTableRowRowRow
Geospatial data type supportYesNoYesYesYes
Geospatial indexing supportYesNoYes[a]NoNo
B-tree indexesYesYesYesNoNo
T-tree indexesNoNoNoNoYes
Hash indexesNoYesNo[b]NoYes
Full-text search indexesYesNoYes[c]NoNo
Clustered indexesNoNoYesNoNo
Data cachesNoN/AYesNoYes
Index cachesYesN/AYesNoYes
Compressed dataYes[d]NoYes[e]YesNo
Encrypted data[f]YesYesYesYesYes
Cluster database supportNoNoNoNoYes
Replication support[g]YesYesYesYesYes
Foreign key supportNoNoYesNoNo
Backup / point-in-time recovery[h]YesYesYesYesYes
Query cache supportYesYesYesYesYes
Update statistics for data dictionaryYesYesYesYesYes

[a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher.

[b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

[c] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

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

[e] Compressed InnoDB tables require the InnoDB Barracuda file format.

[f] Implemented in the server (via encryption functions). Data-at-rest tablespace encryption is available in MySQL 5.7 and higher.

[g] Implemented in the server, rather than in the storage engine.

[h] Implemented in the server, rather than in the storage engine.