Engines

There are several storage engines currently supported by MySQL:

• The most ones : InnoDB and MyISAM
• The alternative ones : Memory, CSV, BlackHole, Archive, Merge, Federated

InnoDB

InnoDB is a general-purpose storage engine that balances high reliability and high performance. It's the default storage engine in MySQL (current version is 5.7).

InnoDB follow the ACID model with transaction feature, rollback, commit, crash-recovery capabilities.

InnoDB store data of table in 'table space'. It optimize structure of storage and rearrange data to optimize query bases on primary keys.

MyISAM

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 format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.

MyISAM does not support transaction. It means, if there is crash while execute records, table can not rollback to previous state.

MyISAM also support full-text-search indexs.

Memory

"The MeMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables."

Because of table in MEMORY storage engine is locate in memory or machine, the accessing to data is fast, save time. So it's consider for storing data that high available, frequently update/read.

Consider about memory storage engine:

• 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.

CSV

The CSV storage engine stores data in text files using comma-separated values format.

It save table data in plain text file with extension .csv. The table definition still save in .frm file as usal.

CSV engine is very simple, it does not support index.

It's rarely used

MERGE

MERGE storage engine is a collection of identical MyISAM tables that can be used as one. The tables can merge must be the same column, column definition, primary key, index.

The most advantage things of merge:

• Easily manage a set of log tables. For example, you can put data from different months into separate tables, and then create a MERGE table to use them as one.
• Obtain more speed. You can split a large read-only table based on some criteria, and then put individual tables on different disks. A MERGE table structured this way could be much faster than using a single large table.
• Perform more efficient searches. If you know exactly what you are looking for, you can search in just one of the underlying tables for some queries and use a MERGE table for others. You can even have many different MERGE tables that use overlapping sets of tables.
• Perform more efficient repairs. It is easier to repair individual smaller tables that are mapped to a MERGE table than to repair a single large table.

The most disadvantage things of merge:

• Can only merge MyISAM table.
• Cost memory.
• Can use full text search index

It's better use for logging operations only.

FEDERATED

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

When you create a table using one of the standard storage engines (such as MyISAM, CSV or InnoDB), the table consists of the table definition and the associated data. When you create a FEDERTED table, the table definition is the same, but the physical storage of the data is handled on a remote server.

FEDERATED table does not have index.

It's not good for performance because it's not have index and have to load remotely.

BLACKHOLE/ ARCHIVE

ARCHIVE ENGINE : This storage engine produces special-purpose tables that store large amounts of unindexed data in a very small footprint.

BLACKHOLE: This storage engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result. But it's query log still save and replicate to slave database.

It's useful with master-slave database structure and good for logging operations.