Online Documentation for SQL Manager for MySQL
Storage engines reference
MySQL provides several storage engines that act as handlers for different table types. All MySQL storage engines include:
- those that handle transaction-safe tables;
- those that handle non-transaction-safe tables.
ISAM
It was the original storage engine which managed non-transactional tables. ISAM was deprecated in MySQL 4.1, and is not supported in the latest versions of the server. This engine was replaced with MyISAM.
MyISAM
It is the table handler that replaced ISAM. MyISAM manages non-transactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities.
Each MyISAM table is stored on disk in three files:
*.frm - the file that stores the table format;
*.MYD (MYData) - the data file;
*.MYI (MYIndex) - the index file.
HEAP (also known as MEMORY)
This storage engine handle non-transactional tables and provides in-memory tables, i.e. the table data are only stored in memory.
Each MEMORY table is associated with one disk file. The file name begins with the table name and has the *.frm extension to indicate that it stores the table definition.
MEMORY tables use hash indexes by default, which makes them fast, and very useful for creating temporary tables.
MERGE (also known as MRG_MyISAM)
This storage engine handles a collection of identical (i.e. having identical column and index information) MyISAM tables that can be used as one table.
When one creates a MERGE table, two files are created on disk:
*.frm - the file that stores the table format;
*.MRG - the file which contains the names of the tables that should be used as one.
InnoDB
This storage engine handles transaction-safe tables with row locking and foreign key support (since MySQL version 3.23.44 only).
Being a transaction-safe (ACID compliant) storage engine, InnoDB has commit, rollback, and crash recovery capabilities. Locking is performed on the row level; also, an Oracle-style consistent non-locking read in SELECT statements is provided. These features increase multi-user concurrency and performance.
The FOREIGN KEY constraints supported by InnoDB can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement.
InnoDB has been designed for maximum performance when processing large data volumes. Tables and indexes are stored in a tablespace which may consist of several files (or raw disk partitions). InnoDB tables can be of any size even on operating systems where file size is limited to 2GB.
BDB (also known as BerkeleyDB)
This storage engine is provided by Sleepycat Software. The BerkeleyDB transactional storage engine handles transaction-safe tables with page locking. BDB tables are capable of COMMIT and ROLLBACK operations on transactions.
BerkeleyDB is not supported in the latest versions of the server.
EXAMPLE
This is a stub engine: its purpose is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines.
An EXAMPLE table is stored in a table format (*.frm) file on disk. No data can be stored into the table, and retrievals return an empty result.
Indexing is not supported either.
As such, the EXAMPLE storage engine is primarily of interest to developers.
FEDERATED
This storage engine was added in MySQL 5.0.3. Data are stored in a remote database, and the FEDERATED storage engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology.
When using a FEDERATED table, queries on the local server are automatically executed on the remote (federated) tables. No data are stored in the local tables.
The FEDERATED table definition is the same as with standard storage engines (such as MyISAM or InnoDB, where each table consists of the table definition and the associated data), but the physical storage of the data is handled on a remote server.
SELECT statements and INSERT, UPDATE, DELETE operations are sent to the remote server for execution, where they update the data file on the remote server or return matching rows from the remote server.
ARCHIVE
This storage engine is used for storing large amounts of data without indexes with a very small footprint.
When an ARCHIVE table is created, the server creates the following files in the database directory:
*.frm - a table format file;
*.ARZ - data files;
*.ARM - metadata files;
*.ARN - the file that appears during optimization operations.
Rows are compressed as they are inserted. The ARCHIVE engine uses zlib lossless data compression. On retrieval, rows are uncompressed on demand; there is no row cache.
CSV
This storage engine stores data in text files using comma-separated values format.
When an CSV table is created, the server creates two files in the database directory:
*.frm - a table format file;
*.CSV - a data file (a plain text file);
*.CSM - a Meta-file that stores the state of the table and the number of rows that exist in the table (in MySQL 5.1.9 and later).
When data are stored into the table, the storage engine saves them into the data file in the comma-separated values format.
The CSV storage engine does not support indexing.
BLACKHOLE
This storage engine accepts data but throws it away and does not store it, therefore retrievals always return an empty result.
When an BLACKHOLE table is created, the server creates a table format (*.frm) file in the database directory.
The BLACKHOLE storage engine supports all kinds of indexes.
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.
FALCON
This storage engine has been designed to meet modern database requirements, and particularly for use within high-volume web serving or other environment that requires high performance, while still supporting the transactional and logging functionality required in this environment. Falcon has been specially developed for systems that are able to support larger memory architectures and multi-threaded or multi-core CPU environments. Most 64-bit architectures are ideal platforms for the FALCON engine, where there is a larger available memory space and 2-, 4- or 8-core CPUs available. Falcon supports all of the standard column data types supported by MySQL.
NDB Cluster
It is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers.
GEMINI
It is a table type developed by NuSphere Company. GEMINI is not released under an Open Source license.
For details refer to the official MySQL documentation.
