Go to the first, previous, next, last section, table of contents.

15 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:

This chapter describes each of the MySQL storage engines except for InnoDB, which is covered in section 16 The InnoDB Storage Engine.

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 table type is usually MyISAM. This can be changed by setting the table_type system variable.

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 14.2.5 CREATE TABLE Syntax and section 14.2.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 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):

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 16.4 InnoDB Configuration.

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

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.

15.1 The MyISAM Storage Engine

MyISAM is the default storage engine as of MySQL 3.23. It is based on the ISAM code but has many useful extensions.

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition. The data file has an `.MYD' (MYData) extension. The index file has an `.MYI' (MYIndex) extension,

To specify explicitly that you want a MyISAM table, indicate that with an ENGINE or TYPE table option: 

CREATE TABLE t (i INT) ENGINE = MYISAM;
CREATE TABLE t (i INT) TYPE = MYISAM;

Normally, the ENGINE or TYPE option is unnecessary; MyISAM is the default storage engine unless the default has been changed.

You can check or repair MyISAM tables with the myisamchk utility. See section 5.6.2.7 Using myisamchk for Crash Recovery. You can compress MyISAM tables with myisampack to take up much less space. See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.

The following characteristics of the MyISAM storage engine are improvements over the older ISAM engine:

MyISAM also supports the following features, which MySQL will be able to use in the near future:

15.1.1 MyISAM Startup Options

The following options to mysqld can be used to change the behavior of MyISAM tables:

--myisam-recover=mode
Set the mode for automatic recovery of crashed MyISAM tables.
--delay-key-write=ALL
Don't flush key buffers between writes for any MyISAM table. Note: If you do this, you should not use MyISAM tables from another program (such as from another MySQL server or with myisamchk) when the table is in use. Doing so will lead to index corruption. Using --external-lock will not help for tables that use --delay-key-write.

See section 5.2.1 mysqld Command-Line Options.

The following system variables affect the behavior of MyISAM tables:

bulk_insert_buffer_size
The size of the tree cache used in bulk insert optimization. Note: This is a limit per thread!
myisam_max_extra_sort_file_size
Used to help MySQL to decide when to use the slow but safe key cache index creation method. Note: This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
myisam_max_sort_file_size
Don't use the fast sort index method to create an index if the temporary file would become larger than this. Note: This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
myisam_sort_buffer_size
Set the size of the buffer used when recovering tables.

See section 5.2.3 Server System Variables.

Automatic recovery is activated if you start mysqld with the --myisam-recover option. In this case, when the server opens a MyISAM table, it checks whether the table is marked as crashed or whether the open count variable for the table is not 0 and you are running the server with --skip-external-locking. If either of these conditions is true, the following happens:

If the recovery wouldn't be able to recover all rows from a previous completed statement and you didn't specify FORCE in the value of the --myisam-recover option, automatic repair aborts with an error message in the error log: 

Error: Couldn't repair table: test.g00pages

If you specify FORCE, a warning like this is written instead: 

Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if the automatic recovery value includes BACKUP, the recovery process creates files with names of the form `tbl_name-datetime.BAK'. You should have a cron script that automatically moves these files from the database directories to backup media.

15.1.2 Space Needed for Keys

MyISAM tables use B-tree indexes. You can roughly calculate the size for the index file as (key_length+4)/0.67, summed over all keys. This is for the worst case when all keys are inserted in sorted order and the table doesn't have any compressed keys.

String indexes are space compressed. If the first index part is a string, it will also be prefix compressed. Space compression makes the index file smaller than the worst-case figure if the string column has a lot of trailing space or is a VARCHAR column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.

In MyISAM tables, you can also prefix compress numbers by specifying PACK_KEYS=1 when you create the table. This helps when you have many integer keys that have an identical prefix when the numbers are stored high-byte first.

15.1.3 MyISAM Table Storage Formats

MyISAM supports three different storage formats. Two of them (fixed and dynamic format) are chosen automatically depending on the type of columns you are using. The third, compressed format, can be created only with the myisampack utility.

When you CREATE or ALTER a table that has no BLOB or TEXT columns, you can force the table format to FIXED or DYNAMIC with the ROW_FORMAT table option. This causes CHAR and VARCHAR columns to become CHAR for FIXED format or VARCHAR for DYNAMIC format.

In the future, you will be able to compress or decompress tables by specifying ROW_FORMAT={COMPRESSED | DEFAULT} to ALTER TABLE. See section 14.2.5 CREATE TABLE Syntax.

15.1.3.1 Static (Fixed-Length) Table Characteristics

Static format is the default for MyISAM tables. It is used when the table contains no variable-length columns (VARCHAR, BLOB, or TEXT). Each row is stored using a fixed number of bytes.

Of the three MyISAM storage formats, static format is the simplest and most secure (least subject to corruption). It is also the fastest of the on-disk formats. The speed comes from the easy way that rows in the data file can be found on disk: When looking up a row based on a row number in the index, multiply the row number by the row length. Also, when scanning a table, it is very easy to read a constant number of records with each disk read operation.

The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format MyISAM file. In this case, myisamchk can easily determine where each row starts and ends, so it can usually reclaim all records except the partially written one. Note that MyISAM table indexes can always be reconstructed based on the data rows.

General characteristics of static format tables:

15.1.3.2 Dynamic Table Characteristics

Dynamic storage format is used if a MyISAM table contains any variable-length columns (VARCHAR, BLOB, or TEXT), or if the table was created with the ROW_FORMAT=DYNAMIC option.

This format is a little more complex because each row has a header that indicates how long it is. One record can also end up at more than one location when it is made longer as a result of an update.

You can use OPTIMIZE TABLE or myisamchk to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation.

General characteristics of dynamic-format tables:

15.1.3.3 Compressed Table Characteristics

Compressed storage format is a read-only format that is generated with the myisampack tool.

All MySQL distributions as of version 3.23.19 include myisampack by default. (This is when MySQL was placed under the GPL.) For earlier versions, myisampack was included only with licenses or support agreements, but the server still can read tables that were compressed with myisampack. Compressed tables can be uncompressed with myisamchk. (For the ISAM storage engine, compressed tables can be created with pack_isam and uncompressed with isamchk.)

Compressed tables have the following characteristics:

15.1.4 MyISAM Table Problems

The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted.

15.1.4.1 Corrupted MyISAM Tables

Even though the MyISAM table format is very reliable (all changes to a table made by an SQL statement are written before the statement returns), you can still get corrupted tables if some of the following things happen:

Typical symptoms for a corrupt table are:

You can check whether a MyISAM table is okay with the CHECK TABLE statement. You can repair a corrupted MyISAM table with REPAIR TABLE. When mysqld is not running, you can also check or repair a table with the myisamchk command. See section 14.5.2.3 CHECK TABLE Syntax, section 14.5.2.6 REPAIR TABLE Syntax, and section 5.6.2.1 myisamchk Invocation Syntax.

If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of a server crash. You can verify this easily by looking for a recent restarted mysqld message in the error log. If there is such a message, it is likely that that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation, which is a bug. You should try to create a reproducible test case that demonstrates the problem. See section A.4.2 What to Do If MySQL Keeps Crashing and section D.1.6 Making a Test Case If You Experience Table Corruption.

15.1.4.2 Problems from Tables Not Being Closed Properly

Each MyISAM index (`.MYI') file has a counter in the header that can be used to check whether a table has been closed properly. If you get the following warning from CHECK TABLE or myisamchk, it means that this counter has gone out of sync: 

clients are using or haven't closed the table properly

This warning doesn't necessarily mean that the table is corrupted, but you should at least check the table to verify that it's okay.

The counter works as follows:

In other words, the counter can go out of sync only under these conditions:

15.2 The MERGE Storage Engine

The MERGE storage engine was introduced in MySQL 3.23.25. It is also known as the MRG_MyISAM engine. The code is now reasonably stable.

A MERGE table is a collection of identical MyISAM tables that can be used as one. ``Identical'' means that all tables have identical column and index information. You can't merge tables in which the columns are packed differently, don't have exactly the same columns, or have the indexes in different order. However, any or all of the tables can be compressed with myisampack. See section 8.2 myisampack, the MySQL Compressed Read-only Table Generator.

When you create a MERGE table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition, and an `.MRG' file contains the names of the tables that should be used as one. (Originally, all used tables had to be in the same database as the MERGE table itself. This restriction has been lifted as of MySQL 4.1.1.)

You can use SELECT, DELETE, UPDATE, and (as of MySQL 4.0) INSERT on the collection of tables. For the moment, you must have SELECT, UPDATE, and DELETE privileges on the tables that you map to a MERGE table.

If you DROP the MERGE table, you are dropping only the MERGE specification. The underlying tables are not affected.

When you create a MERGE table, you must specify a UNION=(list-of-tables) clause that indicates which tables you want to use as one. You can optionally specify an INSERT_METHOD option if you want inserts for the MERGE table to happen in the first or last table of the UNION list. If you don't specify any INSERT_METHOD option or specify it with a value of NO, attempts to insert records into the MERGE table result in an error.

The following example shows how to create a MERGE table: 

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Note that the a column is indexed in the MERGE table, but is not declared as a PRIMARY KEY as it is in the underlying MyISAM tables. This is necessary because a MERGE table cannot enforce uniqueness over the set of underlying tables.

After creating the MERGE table, you can do things like this: 

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 3 | t1      |
| 1 | Testing |
| 2 | table   |
| 3 | t2      |
+---+---------+

Note that you can also manipulate the `.MRG' file directly from outside of the MySQL server: 

shell> cd /mysql-data-directory/current-database
shell> ls -1 t1 t2 > total.MRG
shell> mysqladmin flush-tables

To remap a MERGE table to a different collection of MyISAM tables, you can do one of the following:

MERGE tables can help you solve the following problems:

The disadvantages of MERGE tables are:

15.2.1 MERGE Table Problems

The following are the known problems with MERGE tables:

15.3 The MEMORY (HEAP) Storage Engine

The MEMORY storage engine creates tables with contents that are stored in memory. Before MySQL 4.1, MEMORY tables are called HEAP tables. As of 4.1, HEAP is a synonym for MEMORY, and MEMORY is the preferred term.

Each MEMORY table is associated with one disk file. The filename begins with the table name and has an extension of `.frm' to indicate that it stores the table definition.

To specify explicitly that you want a MEMORY table, indicate that with an ENGINE or TYPE table option: 

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

MEMORY tables are stored in memory and use hash indexes. This makes them very fast, and very useful for creating temporary tables! However, when the server shuts down, all data stored in MEMORY tables is lost. The tables continue to exist because their definitions are stored in the `.frm' files on disk, but their contents will be empty when the server restarts.

Here is an example that shows how you might create, use, and remove a MEMORY table: 

mysql> CREATE TABLE test TYPE=MEMORY
    ->     SELECT ip,SUM(downloads) AS down
    ->     FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;

MEMORY tables have the following characteristics:

15.4 The BDB (BerkeleyDB) Storage Engine

Sleepycat Software has provided MySQL with the Berkeley DB transactional storage engine. This storage engine typically is called BDB for short. Support for the BDB storage engine is included in the MySQL source distribution starting from version 3.23.34a and is activated in MySQL-Max binary distributions.

BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions. The MySQL source distribution comes with a BDB distribution that has a couple of small patches to make it work more smoothly with MySQL. You can't use a non-patched BDB version with MySQL.

We at MySQL AB are working in close cooperation with Sleepycat to keep the quality of the MySQL/BDB interface high. (Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered gamma quality. We are improving and optimizing it.)

When it comes to support for any problems involving BDB tables, we are committed to helping our users locate the problem and create a reproducible test case. Any such test case will be forwarded to Sleepycat, which in turn will help us find and fix the problem. As this is a two-stage operation, any problems with BDB tables may take a little longer for us to fix than for other storage engines. However, we anticipate no significant difficulties with this procedure because the Berkeley DB code itself is used in many applications other than MySQL. See section 1.4.1 Support Offered by MySQL AB.

For general information about Berkeley DB, please visit the Sleepycat Web site, http://www.sleepycat.com/.

15.4.1 Operating Systems Supported by BDB

Currently, we know that the BDB storage engine works with the following operating systems:

BDB does not work with the following operating systems:

Note: The preceding lists are not complete. We will update them as we receive more information.

If you build MySQL from source with support for BDB tables, but the following error occurs when you start mysqld, it means BDB is not supported for your architecture: 

bdb: architecture lacks fast mutexes: applications cannot be threaded
Can't init databases

In this case, you must rebuild MySQL without BDB table support or start the server with the --skip-bdb option.

15.4.2 Installing BDB

If you have downloaded a binary version of MySQL that includes support for Berkeley DB, simply follow the usual binary distribution installation instructions. (MySQL-Max distributions include BDB support.)

If you build MySQL from source, you can enable BDB support by running configure with the --with-berkeley-db option in addition to any other options that you normally use. Download a distribution for MySQL 3.23.34 or newer, change location into its top-level directory, and run this command: 

shell> ./configure --with-berkeley-db [other-options]

For more information, see section 2.2.5 Installing MySQL on Other Unix-Like Systems, section 5.1.2 The mysqld-max Extended MySQL Server, and See section 2.3 MySQL Installation Using a Source Distribution.

15.4.3 BDB Startup Options

The following options to mysqld can be used to change the behavior of the BDB storage engine:

--bdb-home=path
The base directory for BDB tables. This should be the same directory you use for --datadir.
--bdb-lock-detect=method
The BDB lock detection method. The option value should be DEFAULT, OLDEST, RANDOM, or YOUNGEST.
--bdb-logdir=path
The BDB log file directory.
--bdb-no-recover
Don't start Berkeley DB in recover mode.
--bdb-no-sync
Don't synchronously flush the BDB logs.
--bdb-shared-data
Start Berkeley DB in multi-process mode. (Don't use DB_PRIVATE when initializing Berkeley DB.)
--bdb-tmpdir=path
The BDB temporary file directory.
--skip-bdb
Disable the BDB storage engine.

See section 5.2.1 mysqld Command-Line Options.

The following system variable affects the behavior of BDB tables:

bdb_max_lock
The maximum number of locks you can have active on a BDB table.

See section 5.2.3 Server System Variables.

If you use the --skip-bdb option, MySQL will not initialize the Berkeley DB library and this will save a lot of memory. However, if you use this option, you cannot use BDB tables. If you try to create a BDB table, MySQL will create a MyISAM table instead.

Normally, you should start mysqld without the --bdb-no-recover option if you intend to use BDB tables. However, this may give you problems when you try to start mysqld if the BDB log files are corrupted. See section 2.4.2.3 Starting and Troubleshooting the MySQL Server.

With the bdb_max_lock variable, you can specify the maximum number of locks that can be active on a BDB table. The default is 10,000. You should increase this if errors such as the following occur when you perform long transactions or when mysqld has to examine many rows to execute a query: 

bdb: Lock table is out of available locks
Got error 12 from ...

You may also want to change the binlog_cache_size and max_binlog_cache_size variables if you are using large multiple-statement transactions. See section 5.8.4 The Binary Log.

15.4.4 Characteristics of BDB Tables

Each BDB table is stored on disk in two files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition, and a `.db' file contains the table data and indexes.

To specify explicitly that you want a BDB table, indicate that with an ENGINE or TYPE table option: 

CREATE TABLE t (i INT) ENGINE = BDB;
CREATE TABLE t (i INT) TYPE = BDB;

BerkeleyDB is a synonym for BDB in the ENGINE or TYPE option.

The BDB storage engine provides transactional tables. The way you use these tables depends on the autocommit mode:

See section 14.4.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax.

The BDB storage engine has the following characteristics:

15.4.5 Things We Need to Fix for BDB

15.4.6 Restrictions on BDB Tables

The following list indicates restrictions that you must observe when using BDB tables:

15.4.7 Errors That May Occur When Using BDB Tables

15.5 The ISAM Storage Engine

The original storage engine in MySQL was the ISAM engine. It was the only storage engine available until MySQL 3.23, when the improved MyISAM engine was introduced as the default. ISAM now is deprecated. As of MySQL 4.1, it's included in the source but not enabled in binary distributions. It will disappear in MySQL 5.0. Embedded MySQL server versions do not support ISAM tables by default.

Due to the deprecated status of ISAM, and because MyISAM is an improvement over ISAM, you are advised to convert any remaining ISAM tables to MySAM as soon as possible. To convert an ISAM table to a MyISAM table, use an ALTER TABLE statement: 

mysql> ALTER TABLE tbl_name TYPE = MYISAM;

For more information about MyISAM, see section 15.1 The MyISAM Storage Engine.

Each ISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition. The data file has an `.ISD' extension. The index file has an `.ISM' extension.

ISAM uses B-tree indexes.

You can check or repair ISAM tables with the isamchk utility. See section 5.6.2.7 Using myisamchk for Crash Recovery.

ISAM has the following properties:

Many of the properties of MyISAM tables are also true for ISAM tables. However, there are also many differences. The following list describes some of the ways that ISAM is distinct from MyISAM:

Go to the first, previous, next, last section, table of contents.