Run the following command

mysql> SHOW VARIABLES LIKE ‘have_blackhole_engine’;

The BLACKHOLE storage engine accepts data but does not store anything . So on retrieval it will return nothing .

When we create BLACKHOLE table the server will create file with name starts with table name with extension .frm . No other tables will be generated as it stores nothing .

It supports all types of indexes .

Though nothing is stored , the query statements get stored if binary logs are enabled .

It does not support AUTO INCREMENT

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

When you create an ARCHIVE table, the server creates a table format 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, respectively. An .ARN file may appear during optimization operations.

The ARCHIVE engine supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It does support ORDER BY operations, BLOB columns

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

When you create an ARCHIVE table, the server creates a table format 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, respectively. An .ARN file may appear during optimization operations.

The ARCHIVE engine supports INSERT and SELECT, but not DELETE, REPLACE, or UPDATE. It does support ORDER BY operations, BLOB columns

It is a storage engine that accesses data in tables of remote databases rather than in local tables.The FEDERATED storage engine is available beginning with MySQL 5.0.3.

This 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 is stored on the local tables.

The EXAMPLE storage engine is a stub engine that do 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 MySQL source distribution.

When you create an EXAMPLE table, the server creates a table format 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. Retrievals return an empty result.

MySQL with the Berkeley DB transactional storage engine is called BDB for short.

BDB tables may have a greater chance of surviving crashes and are also capable of COMMIT and ROLLBACK operations on transactions.

Note that as of MySQL 5.1, its not supported  .

The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.The MEMORY storage engine associates each table with one disk file. The file name begins with the table name and has an extension of .frm to indicate that it stores the table definition.ExampleCREATE TABLE t (i INT) ENGINE = MEMORY;

As indicated by the engine name, MEMORY tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in MEMORY tables are 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.

MEMORY tables can have up to 64 indexes per table

MEMORY tables can have up to 16 columns per index

MEMORY tables can have maximum key length of 3072 bytes.

Space for MEMORY tables is allocated in small blocks. Tables use 100% dynamic hashing for inserts.

The MEMORY storage engine supports both HASH and BTREE indexes. You can specify one or the other for a given index by adding a USING clause

MEMORY tables can have nonunique keys.

Columns that are indexed can contain NULL values.

MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.

MEMORY tables cannot contain BLOB or TEXT columns.

MEMORY includes support for AUTO_INCREMENT columns.

The server needs sufficient memory to maintain all MEMORY tables that are in use at the same time.

The MERGE storage engine is also known as the MRG_MyISAM engine and it 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 MyISAM 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 MyISAM tables can be compressed withWhen 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 format, and an .MRG file contains the names of the underlying MyISAM tables that should be used as one. The tables do not have to be in the same database as the MERGE table.You can use SELECT, DELETE, UPDATE, and INSERT on MERGE tables. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.

Use of DROP TABLE with a MERGE table drops only the MERGE specification. The underlying tables are not affected.

We can create MERGE table using the option UNION={list-of-tables} that indicates which MyISAM tables to use. You can optionally specify an INSERT_METHOD option to control how inserts into the MERGE table take place. Use a value of FIRST or LAST to cause inserts to be made in the first or last underlying table, respectively. If you specify no INSERT_METHOD option or if you specify it with a value of NO, inserts into the MERGE table are not permitted and attempts to do so result in an error.

Example

CREATE TABLE blog (  id  INT NOT NULL AUTO_INCREMENT,  comment CHAR(50), INDEX(id)) ENGINE=MERGE UNION=(blog1,blog2) INSERT_METHOD=LAST;

Here blog1,blog2 should have identical column and index information .