Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities.
By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.
Each of these different techniques and suites of functionality within the MySQL system is referred to as a storage engine (also known as a table type). By default, MySQL comes with a number of different storage engines pre-configured and enabled in the MySQL server. You can select the storage engine to use on a server, database and even table basis, providing you with the maximum amount of flexibility when it comes to choosing how your information is stored, how it is indexed and what combination of performance and functionality you want to use with your data.
Below is a list of popular storage engines for MySQL and their functionalities.
###ARCHIVE
The ARCHIVE engine is optimized for managing large amounts of data designated as stored for archived purposes. Data stored using the ARCHIVE engine can only be inserted and selected, and not deleted nor modified.
###BLACKHOLE
The BLACKHOLE storage engine accepts inserted data without error but does not store it, instead deleting it upon acceptance. While seemingly useless, BLACKHOLE can actually serve several practical roles, ranging from facilitating data replication to assisting in the identification of bottlenecks (due to the ability to use BLACKHOLE to remove the storage engine from the bottleneck candidates).
###CSV
Comma-separated values (CSV) format is a common storage solution supported by many applications. MySQL’s CSV storage engine manages data in this format, the data files of which can subsequently be read from and written to by applications such as Microsoft Excel.
###EXAMPLE
EXAMPLE is a featureless storage engine with the sole purpose of providing developers with a skeleton for writing their own storage engines. It is incapable of storing data.
###Falcon
New to MySQL 6.0, Falcon is optimized for modern database environments requiring maximum data retrieval and update performance without sacrificing transactional/logging features.
###FEDERATED
Introduced in MySQL 5.0, the FEDERATED storage engine can pool remote MySQL databases together under the guise of a single logical database by creating pointers to these remote tables.
###InnoDB
MySQL’s most popular transactional storage solution, InnoDB offers complete commit, rollback, and crash recovery features alongside attractive performance capabilities. InnoDB serves as MySQL’s default storage engine on the Windows platform.
###Maria
Introduced in MySQL 6.0.6, Maria is intended to ultimately serve as MySQL’s default transactional and non-transactional storage engines.
###MEMORY
The MEMORY storage engine stores data within system memory (RAM), resulting in volatile although extremely fast data access.
###MERGE
The MERGE storage engine is useful for accessing a group of identical MyISAM tables as if the data resided within a single table structure. Such a configuration might be useful when accessing large amounts of sales data which has been separately stored by month according to an aptly-named table.
###MyISAM
MyISAM is MySQL’s default storage engine. Although incapable of supporting transactions, MyISAM is optimized for high traffic environments and is very simple to manage.