14.1 Introduction to InnoDB 1.1

14.1.1 Features of the InnoDB Storage Engine
14.1.2 InnoDB as the Default MySQL Storage Engine
14.1.3 Checking InnoDB Availability
14.1.4 Turning Off InnoDB
14.1.5 Obtaining and Installing the InnoDB Storage Engine
14.1.6 Viewing the InnoDB Storage Engine Version Number
14.1.7 Compatibility Considerations for Downgrade and Backup
14.1.8 InnoDB Storage Engine Change History
14.1.9 Third-Party Software Contributions

InnoDB 1.1 combines the familiar reliability and performance of the InnoDB storage engine, with new performance and usability enhancements. InnoDB 1.1 includes all the features that were part of the InnoDB Plugin for MySQL 5.1, plus new features specific to MySQL 5.5 and higher.

Beginning with MySQL version 5.5, InnoDB is the default storage engine, rather than MyISAM. Issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.

Key Advantages of InnoDB

Key advantages of InnoDB tables include:

To determine whether your server supports InnoDB use the SHOW ENGINES statement. See Section, “SHOW ENGINES Syntax”.

Table 14.1 InnoDB Storage Engine Features

Storage limits64TBTransactionsYesLocking granularityRow
MVCCYesGeospatial data type supportYesGeospatial indexing supportYes[a]
B-tree indexesYesT-tree indexesNoHash indexesNo[b]
Full-text search indexesYes[c]Clustered indexesYesData cachesYes
Index cachesYesCompressed dataYes[d]Encrypted data[e]Yes
Cluster database supportNoReplication support[f]YesForeign key supportYes
Backup / point-in-time recovery[g]YesQuery cache supportYesUpdate statistics for data dictionaryYes

[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 InnoDB tables require the InnoDB Barracuda file format.

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

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

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

The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. When the innodb_file_per_table setting is enabled, each new InnoDB table and its associated indexes are stored in a separate file. When the innodb_file_per_table option is disabled, InnoDB stores all its tables and indexes in the single system tablespace, which may consist of several files (or raw disk partitions). InnoDB tables can handle large quantities of data, even on operating systems where file size is limited to 2GB.

To compare the features of InnoDB with other storage engines provided with MySQL, see the Storage Engine Features table in Chapter 15, Alternative Storage Engines.

InnoDB Enhancements and New Features

The InnoDB storage engine in MySQL 5.5 releases includes a number performance improvements that in MySQL 5.1 were only available by installing the InnoDB Plugin. This latest InnoDB (now known as InnoDB 1.1) offers new features, improved performance and scalability, enhanced reliability and new capabilities for flexibility and ease of use. Among the top features are Fast Index Creation, table and index compression, file format management, new INFORMATION_SCHEMA tables, capacity tuning, multiple background I/O threads, multiple buffer pools, and group commit. For more information about these features, see Section 14.1.1, “Features of the InnoDB Storage Engine”.

For additional information about InnoDB enhancements and new features in MySQL 5.5, also refer to:

Additional Resources