MySql Storage Engines

A storage engine is a software system that stores, handles, and retrieves information from a table. MySQL supports several storage engines that act as handlers for different table types. There are two types of storage engines in MySQL.

  • Transactional
  • Non-transactional
The default storage engine for MySQL is MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB. To get a list of supported storage engines by MySql use command:



The standard engines supported by MySQL are:
  • MyISAM
  • InnoDB
  • MERGE
  • MEMORY
  • CSV
  • Archive
  • Federated
  • Blackhole
  • Example

MyISAM

It is fast and original storage engine, it does not support transactions. MyISAM provides table-level locking. MyISAM is typically the best for performance and functionality. MyISAM type is great for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate.


InnoDB

InnoDB is the most widely used storage engine with transaction support. One major difference is the ability to do row-level locking, as opposed to table-level locking, to increase performance time. This allows parallel INSERT/UPDATE/DELETE queries on the same table, unlike MyISAM where each query has to wait its turn to run.


MERGE

MERGE storage engine was added in MySQL 3.23.25. Merge operates on underlying MyISAM tables, Merge tables help manage large number of data more easily.


MEMORY

MEMORY storage engine creates tables in memory. It provide table-level locking. It does not support transactions. Memory storage engine is used for creating temporary tables or quick lookups. The data is lost when the database is restarted.


CSV

The CSV storage engine was added in MySQL 4.1.4, it stores data in text files with .csv extension, It provides great flexibility, because data in this format is easily integrated into other applications.


Archive

The ARCHIVE storage engine was added in MySQL 4.1.3. It does not support transactions. It is used for storing and retrieving large data.


Federated

Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.


Blackhole

The BLACKHOLE storage engine was added in MySQL 4.1.11. It accepts data but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally.

Example

The EXAMPLE storage engine was added in MySQL 4.1.3. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. You can create tables with this engine, but no data will stored or retrieved from them.

Specifying and changing(Altering) storage engines

  • This query will create new table with storage engine = "MyISAM"
    • mysql> CREATE TABLE Test(Id INT(10) PRIMARY KEY, Name VARCHAR(50)) ENGINE='MyISAM';
  • If we do not specify the storage engine explicitly, then the default storage engine is used.
    • mysql> SELECT ENGINE FROM information_schema.TABLES
    -> WHERE TABLE_SCHEMA='Test'
    -> AND TABLE_NAME='test';
  • This query will ALTER (change) the ENGINE
    • mysql> ALTER TABLE Cars ENGINE='MyISAM';