(mysql.info.gz) Storage engines
(mysql.info.gz) SQL Syntax
14 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
* 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 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
* `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
* 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 InnoDB and
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
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
When MySQL is installed on Windows using the MySQL Configuration Wizard,
the `InnoDB' storage engine will be the default instead of `MyISAM'.
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 `CREATE TABLE' CREATE TABLE. and `ALTER TABLE' ALTER
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
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
* If an update fails, all your changes will be restored. (With
non-transaction-safe tables, all changes that have taken place are
* 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.
`InnoDB' configuration 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.
* MyISAM storage engine The `MyISAM' Storage Engine
* MERGE storage engine The `MERGE' Storage Engine
* MEMORY storage engine The `MEMORY' (`HEAP') Storage Engine
* BDB storage engine The `BDB' (`BerkeleyDB') Storage Engine
* EXAMPLE storage engine The `EXAMPLE' Storage Engine
* FEDERATED storage engine The `FEDERATED' Storage Engine
* ARCHIVE storage engine The `ARCHIVE' Storage Engine
* CSV storage engine The `CSV' Storage Engine
* ISAM storage engine The `ISAM' Storage Engine
(mysql.info.gz) SQL Syntax
automatically generated byinfo2html