MySQL 5 Storage Engines

MySQL 5 Storage Engines

MySQL 5 offers a number of new storage engines (previously called table types). In addition to the default MyISAM storage engine, and the InnoDB, BDB, HEAP and MERGE storage engines, there are four new types: CSV, ARCHIVE, FEDERATED and EXAMPLE, as well as a new name for the HEAP storage engine. It is now called the MEMORY storage engine. None of the new types are available by default - you can check for sure with the SHOW ENGINES statement. Here is what is on my default version of MySQL Max:
mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+
Engine Support Comment
+------------+---------+------------------------------------------------------------+
MyISAM DEFAULT Default engine as of MySQL 3.23 with great performance
HEAP YES Alias for MEMORY
MEMORY YES Hash based, stored in memory, useful for temporary tables
MERGE YES Collection of identical MyISAM tables
MRG_MYISAM YES Alias for MERGE
ISAM NO Obsolete storage engine, now replaced by MyISAM
MRG_ISAM NO Obsolete storage engine, now replaced by MERGE
InnoDB YES Supports transactions, row-level locking, and foreign keys
INNOBASE YES Alias for INNODB
BDB YES Supports transactions and page-level locking
BERKELEYDB YES Alias for BDB
NDBCLUSTER NO Clustered, fault-tolerant, memory-based tables
NDB NO Alias for NDBCLUSTER
EXAMPLE NO Example storage engine
ARCHIVE NO Archive storage engine
CSV NO CSV storage engine
+------------+---------+------------------------------------------------------------+
To add support for the missing storage engines, you currently need to build MySQL


The FEDERATED storage engine
Added in MySQL 5.0.3, to make use of it you need to use the --with-federated-storage-engine option to configure when building MySQL. The FEDERATED storage engine allows you to access data from a table on another database server. That table can make use of any storage engine. Let's see it in action. First, CREATE a table on a remote server (you can do this on the same server for testing purposes, but doing so is fairly pointless otherwise).CREATE TABLE myisam_table (f1 INT, PRIMARY KEY(f1))
ENGINE=MYISAM;
Assuming that the default is set to create MyISAM tables (FEDERATED tables can access tables of any type), the above statement creates a definition file (.frm), an index file (.MYI) and a data file (.MYD). If you had created an InnoDB file, MySQL would create a definition (.frm) and index and data file (.idb). Now create the FEDERATED table on another server. The original table must always exist first: CREATE TABLE federated_table (f1 INT, PRIMARY KEY(f1))
ENGINE=FEDERATED
COMMENT='mysql://username:password@hostname.co.za:3306/dbname/myisam_table';

The ARCHIVE storage engine
Added in MySQL 4.1.3, the archive storage engine lives up to its name by storing large amounts of data without taking up too much space. It too makes no use of any sort of indexing, and there are no means to repair the table should it become corrupted during a crash. To enable this storage engine, use the -with-archive-storage-engine configure option when building MySQL.
mysql> CREATE TABLE archive_names(firstname CHAR(30), surname CHAR(40), age INT) ENGINE = ARCHIVE;