mySQL Storage Engines

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 nontransaction-safe tables:

• MyISAM manages nontransactional tables. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. MyISAM is supported in all MySQL configurations, and is the default storage engine unless you have configured MySQL to use a different one by default.
  
• The MEMORY storage engine provides in-memory tables. The MERGE storage engine allows a collection of identical MyISAM tables to be handled as a single table. Like MyISAM, the MEMORY and MERGE storage engines handle nontransactional tables, and both are also included in MySQL by default.
  
• The InnoDB and BDB storage engines provide transaction-safe tables. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
  
• The EXAMPLE storage engine is a “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in 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 developers.
  
• NDBCLUSTER (also known as NDB) is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in MySQL 5.0 binary distributions. This storage engine is currently supported on a number of Unix platforms. Experimental support for Windows is available beginning in MySQL Cluster NDB 7.0; however, we do not intend to backport this functionality to MySQL 5.0.
  MySQL Cluster is covered in a separate chapter of this Manual.
  
• The ARCHIVE storage engine is used for storing large amounts of data without indexes with a very small footprint.
  
• The CSV storage engine stores data in text files using comma-separated values format.
  
• The BLACKHOLE storage engine accepts but does not store data and retrievals always return an empty set.
  
• The FEDERATED storage engine was added in MySQL 5.0.3. This engine stores data in a remote database. Currently, it works with MySQL only, using the MySQL C Client API. In future releases, we intend to enable it to connect to other data sources using other drivers or client connection methods.
  

To determine which storage engines your server supports by using the SHOW ENGINES statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine.

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
 Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
 Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
 Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)