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.8.5.6, “Using myisamchk for Crash Recovery”. You can also compress MyISAM tables with myisampack to take up much less space. See Section 8.2, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.

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:

For the MyISAM storage engine, there's a dedicated forum available on http://forums.mysql.com/list.php?21.

Error: Couldn't repair table: test.g00pages

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

clients are using or haven't closed the table properly

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

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 cannot merge tables in which the columns are listed in a different order, do not 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 — Generate Compressed, Read-Only MyISAM Tables”. Differences in table options such as AVG_ROW_LENGTH, MAX_ROWS, or PACK_KEYS do not matter.

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. 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 take place in the first or last table of the UNION list. Use a value of FIRST or LAST to cause inserts to be made in the first or last table, respectively. If you do not specify an INSERT_METHOD option or if you 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 issue queries that operate on the group of tables as a whole:

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 perform one of the following:

MERGE tables can help you solve the following problems:

The disadvantages of MERGE tables are:

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, MEMORY is the preferred term, although HEAP remains supported for backwards compatibility.

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;

As indicated by their name, MEMORY tables are stored in memory and use hash indexes by default. 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 themselves continue to exist because their definitions are stored in .frm files on disk, but they are empty when the server restarts.

This example 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:

For the MEMORY storage engine, there's a dedicated forum available on http://forums.mysql.com/list.php?92.

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 MySQL source distributions 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 is patched to make it work with MySQL. You cannot use a non-patched version of BDB with MySQL.

We at MySQL AB work 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 continue to improve and optimize it.)

When it comes to support for any problems involving BDB tables, we are committed to helping our users locate the problem and create reproducible test cases. Any such test case is forwarded to Sleepycat, who in turn 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.

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

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

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

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

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

The EXAMPLE storage engine was added in MySQL 4.1.3. It is a “stub” engine that does nothing. Its purpose 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.

To examine the source for the EXAMPLE engine, look in the sql/examples directory of a source distribution for MySQL 4.1.3 or newer.

To enable this storage engine, use the --with-example-storage-engine option to configure when you build MySQL.

When you create an EXAMPLE table, the server creates a table definition file in the database directory. The file begins with the table name and has an .frm extension. No other files are created. No data can be stored into the table or retrieved from it.

mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.78 sec)

mysql> INSERT INTO test VALUES(1),(2),(3);
ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option

mysql> SELECT * FROM test;
Empty set (0.31 sec)

The EXAMPLE storage engine does not support indexing.

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.

To enable this storage engine, use the --with-archive-storage-engine option to configure when you build MySQL. You can see if this storage engine is available with this statement:

mysql> SHOW VARIABLES LIKE 'have_archive';

When you create an ARCHIVE table, the server creates a table definition file in the database directory. The file begins with the table name and has an .frm extension. The storage engine creates other files, all having names beginning with the table name. The data and metadata files have extensions of .ARZ and .ARM. A .ARN file may appear during optimization operations.

The ARCHIVE engine supports only INSERT and SELECT (no deletes, replaces, or updates). It does support ORDER BY operations, BLOB fields, and basically all data types except geometry data types (see Section 17.4.1, “MySQL Spatial Data Types”). The ARCHIVE engine uses row-level locking.

Storage: Records are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression. Use of OPTIMIZE TABLE can analyze the table and pack it into a smaller format (for a reason to use OPTIMIZE TABLE, see below). There are several types of insertions that are used:

Retrieval: On retrieval, records are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used. To fix any compression issues that have occurred you can always do an OPTIMIZE TABLE (REPAIR TABLE also is supported). The number of rows in ARCHIVE tables reported by SHOW TABLE STATUS is always accurate. See Section 13.5.2.6, “REPAIR TABLE Syntax”, Section 13.5.2.5, “OPTIMIZE TABLE Syntax”, Section 13.5.4.18, “SHOW TABLE STATUS Syntax”.

For the ARCHIVE storage engine, there's a dedicated forum available on http://forums.mysql.com/list.php?112.

The CSV storage engine was added in MySQL 4.1.4. This engine stores data in text files using comma-separated-values format.

To enable this storage engine, use the --with-csv-storage-engine option to configure when you build MySQL.

When you create a CSV table, the server creates a table definition file in the database directory. The file begins with the table name and has an .frm extension. The storage engine also creates a data file. Its name begins with the table name and has a .CSV extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in CSV format.

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+------+------------+
| i    | c          |
+------+------------+
|    1 | record one |
|    2 | record two |
+------+------------+
2 rows in set (0.00 sec)

If you examine the test.CSV file in the database directory created by executing the preceding statements, its contents should look like this:

"1","record one"
"2","record two"

The CSV storage engine does not support indexing.

The BLACKHOLE storage engine was added in MySQL 4.1.11. This engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return the empty set:

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
Empty set (0.00 sec)

When you create a BLACKHOLE table, the server creates a table definition file in the database directory. The file begins with the table name and has an .frm extension. There are no other files associated with the table.

The BLACKHOLE storage engine supports all kinds of indexing.

To enable this storage engine, use the --with-blackhole-storage-engine option to configure when you build MySQL. The BLACKHOLE storage engine is available in MySQL-supplied server binaries beginning with MySQL 4.1.12; you can determine whether or not your version supports this engine by viewing the output of SHOW ENGINES or SHOW VARIABLES LIKE 'have%'.

Inserts into a BLACKHOLE table do not store any data, but if the binary log is enabled, the SQL statements are logged (and replicated to slave servers). This can be useful as a repeater or filter mechanism. For example, suppose that your application requires slave-side filtering rules, but transfering all binlog data to the slave first results in too much traffic. In such a case, it is possible to set up on the master host a “dummy” slave process whose default storage engine is BLACKHOLE, depicted as follows:

The master writes to its binary log. The “dummy” mysqld process acts as a slave, applying the desired combination of replicate-do and replicate-ignore rules, and writes a new, filtered binlog of its own. (See Section 6.8, “Replication Startup Options”.) This filtered log is provided to the slave.

Since the dummy process does not actually store any data, there is little processing over head incurred by running the additional mysqld process on the replication master host. This type of setup can be repeated with additional replication slaves.

Other possible uses for the BLACKHOLE storage engine include:

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 is deprecated. As of MySQL 4.1, it's included in the source but not enabled in binary distributions. It is not available 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 MyISAM 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 14.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.8.5.6, “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: