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 is 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 has been renamed 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.
The EXAMPLE storage engine was added in MySQL 4.1.3. It is a ``stub'' engine that does nothing. You can create tables with this engine, but no data can be stored into 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.
NDB Cluster is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in source code distributions as of MySQL 4.1.2 and binary distributions as of MySQL-Max 4.1.3.
The ARCHIVE storage engine was added in MySQL 4.1.3. It is used for storing large amounts of data without indexes in a very small footprint.
The CSV storage engine was added in MySQL 4.1.4. This engine stores data in text files using comma-separated-values format.
The FEDERATED storage engine was added in MySQL 5.0.3. This engine stores data in a remote database. In this release, it works with MySQL only, using the MySQL C Client API. Future releases will be able to connect to other data sources using other driver or client connection methods.

This chapter describes each of the MySQL storage engines except for InnoDB and NDB Cluster, which are covered in Chapter 15, The InnoDB Storage Engine and Chapter 16, MySQL Cluster.

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:

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;

ENGINE is the preferred term, but cannot be used before MySQL 4.0.18. TYPE is available beginning with MySQL 3.23.0, the first version of MySQL for which multiple storage engines were available.

If you omit the ENGINE or TYPE option, the default storage engine is used. By default this is MyISAM. You can change it by using the --default-storage-engine or --default-table-type server startup option, or by setting the storage_engine or table_type system variable.

When MySQL is installed on Windows using the MySQL Configuration Wizard, the InnoDB storage engine is the default instead of MyISAM. See Section 2.3.5.1, “Introduction”.

To convert a table from one type to another, use an ALTER TABLE statement that indicates the new type:

ALTER TABLE t ENGINE = MYISAM;
ALTER TABLE t TYPE = BDB;

See Section 13.1.5, “CREATE TABLE Syntax” and Section 13.1.2, “ALTER TABLE Syntax”.

If you try to use a storage engine that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table of type MyISAM. This behavior is convenient when you want to copy tables between MySQL servers that support different storage engines. (For example, in a replication setup, perhaps your master server supports transactional storage engines for increased safety, but the slave servers use only non-transactional storage engines for greater speed.)

This automatic substitution of the MyISAM table type when an unavailable type is specified can be confusing for new MySQL users. In MySQL 4.1 and up, a warning is generated when a table type is automatically changed.

MySQL always creates an .frm file to hold the table and column definitions. The table's index and data may be stored in one or more other files, depending on the table type. The server creates the .frm file above the storage engine level. Individual storage engines create any additional files required for the tables that they manage.

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 of your changes are reverted. (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. See Section 15.4, “InnoDB Configuration”.

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.

Previous / Next / Up / Table of Contents

User Comments

Warning: query failed: Unknown column 'user.firstname' in 'field list' in /data0/sites/live/web-main/lib/mysql-cxn.php on line 69

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /data0/sites/live/web-main/lib/docbook.php on line 245

Add your own comment.

Top / Previous / Next / Up / Table of Contents