MySQL Storage Engines and Table Types

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 non-transaction-safe tables:

The original storage engine was ISAM, which managed non-transactional tables. This engine has been replaced by MyISAM and should no longer be used. It is deprecated in MySQL 4.1, and will be removed in MySQL 5.0.

In MySQL 3.23.0, the MyISAM and HEAP storage engines were introduced. MyISAM is an improved replacement for ISAM. The HEAP storage engine provides in-memory tables. The MERGE storage engine was added in MySQL 3.23.25. It allows a collection of identical MyISAM tables to be handled as a single table. All three of these storage engines handle non-transactional tables, and all are included in MySQL by default. Note that the HEAP storage engine now is known as the MEMORY engine.

The InnoDB and BDB storage engines that handle transaction-safe tables were introduced in later versions of MySQL 3.23. Both are available in source distributions as of MySQL 3.23.34a. BDB is included in MySQL-Max binary distributions on those operating systems that support it. InnoDB also is included in MySQL-Max binary distributions for MySQL 3.23. Beginning with MySQL 4.0, InnoDB is included by default in all MySQL binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.

When you create a new table, you can tell MySQL what type of table to create by adding an ENGINE or TYPE table option to the CREATE TABLE statement:

A database may contain tables of different types. Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs): 

• Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log. 
• You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled). 
• You can execute ROLLBACK to ignore your changes (if autocommit is disabled). 
• If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.) 
• Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads. 

Note that to use the InnoDB storage engine in MySQL 3.23, you must configure at least the innodb_data_file_path startup option. In 4.0 and up, InnoDB uses default configuration values if you specify none. 

Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead: 

• Much faster 
• Lower disk space requirements 
• Less memory required to perform updates 

You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best of both worlds. However, within a transaction with autocommit disabled, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back.

Happy Exploring!