MySQL Storage Engines

Overview

In this tutorial, we will learn about MySQL Storage Engines. MySQL supports many storage engines. MySQL allows us to choose from any of them when creating a table. Each storage engine has specific characteristics and features.

MySQL Storage Engines

When we create a database table using MySQL, we can choose the storage engine to use. The choice of storage engine made should be according to the best fit of the needs of the application.

Before we can use the storage engine, it should be compiled into the MySQL server and enabled to use. MySQL server uses a modular approach for storage engines. Each storage engine is a software module that is compiled into the server. We can enable storage engines at configuration time or at runtime. Some most common storage engines in MySQL are as follows:

MySQL Storage Engines	Description
InnoDB	ACID compliant transactional storage engine. Supports transactions, row-level locking, and foreign keys.
MyISAM	Fast storage engine for ready heavy applications.
MEMORY	In memory storage engine. Hash based, stored in memory, useful for temporary tables
FEDERATED	Access to database tables located remotely
EXAMPLE	Storage engine used only for development and testing
BLACKHOLE	Data stored in a table disappears because the engine discards it
CSV	Stores data in comma-separated values format as plain text
NDB	MySQL’s cluster storage engine
ARCHIVE	Archival storage engine. Mostly used for large number of records that will never be altered.

SHOW ENGINES MySQL Command

To specify the storage engine, we can use the ENGINE clause of the CREATE TABLE statement. If we create a table without the explicit ENGINE option, the MySQL database server creates the table using the default storage engine. The default storage engine is defined by the default_storage_engine system variable.

To know the default storage engine on the MySQL server, issue the following command using the MySQL client.

mysql> SHOW VARIABLES LIKE ‘default_storage_engine’;

Default Storage Engine MySQL