There are several storage engines currently supported by MySQL:
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.
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.
"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:
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 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:
The most disadvantage things of merge:
It's better use for logging operations only.
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.
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.