Storage Engines

MySQL Server uses a pluggable storage engine architecture: each table is created with a specific storage engine, chosen with ENGINE = engine_name in CREATE TABLE, and different tables in the same database – even the same query – can mix engines freely. Engines differ in whether they support transactions, what locking granularity they use, which index types they offer, and how (or whether) they persist data to disk.

SHOW ENGINES;
SHOW ENGINE INNODB STATUS;

Lists the storage engines supported by the running server and their default/support status, and shows detailed operational information for a specific engine. See also Information Commands.

SHOW ENGINES;

SHOW ENGINES;

Engine	Support	Comment	Transactions	XA	Savepoints
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys	YES	YES	YES
MRG_MYISAM	YES	Collection of identical MyISAM tables	NO	NO	NO
MEMORY	YES	Hash based, stored in memory, useful for temporary tables	NO	NO	NO
BLACKHOLE	YES	/dev/null storage engine (anything you write to it disappears)	NO	NO	NO
MyISAM	YES	MyISAM storage engine	NO	NO	NO
CSV	YES	CSV storage engine	NO	NO	NO
ARCHIVE	YES	Archive storage engine	NO	NO	NO
PERFORMANCE_SCHEMA	YES	Performance Schema	NO	NO	NO
FEDERATED	NO	Federated MySQL storage engine	NULL	NULL	NULL

Each engine below has its own feature-comparison table covering storage limits, transaction support, locking granularity, MVCC, index types, caching, compression, encryption, and clustering/replication support. Which engine to choose depends on the workload: InnoDB is the right default for almost all general-purpose, transactional, multi-user workloads, while the other engines serve narrower purposes – temporary in-memory scratch tables, compact archival storage, or connecting to remote data.

InnoDB

Balancing high reliability and performance, InnoDB is MySQL's default storage engine.

Advantages of InnoDB include:

DML operations follow the ACID (atomicity, consistency, isolation, durability) model, with commit, rollback, and crash-recovery capabilities. See Transactions.
Row-level locking and Oracle-style consistent reads increase performance and multi-user concurrency. See Locks.
Tables arrange data on disk to optimize queries based on primary keys.
FOREIGN KEY constraints are supported.
InnoDB tables can be freely mixed with tables from other storage engines, even within the same statement – for example, joining an InnoDB table with a MEMORY table in a single query.
InnoDB is designed for maximum performance when processing large data volumes.
InnoDB maintains its own buffer pool for caching data and indexes in main memory.
Tables can handle large amounts of data even on operating systems where file size is limited to 2GB.

Feature	InnoDB
Storage limits	64TB
Transactions	Yes
Locking granularity	Row
MVCC	Yes
Geospatial data type support	Yes
Geospatial indexing support	Yes
B-tree indexes	Yes
T-tree indexes	No
Hash indexes	No (internal adaptive hash index only, not user-visible)
Full-text search indexes	Yes
Clustered indexes	Yes
Data caches	Yes
Index caches	Yes
Compressed data	Yes
Encrypted data	Yes
Cluster database support	No
Replication support	Yes
Foreign key support	Yes
Backup / point-in-time recovery	Yes
Query cache support	Yes
Update statistics for data dictionary	Yes

-- InnoDB is the default engine, but it can be specified explicitly
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE = InnoDB;

-- ACID transaction using InnoDB's row-level locking and MVCC
START TRANSACTION;
UPDATE orders SET total = total - 10.00 WHERE id = 1;
UPDATE orders SET total = total + 10.00 WHERE id = 2;
COMMIT;

-- Detailed engine status, including buffer pool and lock information
SHOW ENGINE INNODB STATUS;

-- InnoDB is the default engine, but it can be specified explicitly
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE = InnoDB;

-- ACID transaction using InnoDB's row-level locking and MVCC
START TRANSACTION;
UPDATE orders SET total = total - 10.00 WHERE id = 1;
UPDATE orders SET total = total + 10.00 WHERE id = 2;
COMMIT;

-- Detailed engine status, including buffer pool and lock information
SHOW ENGINE INNODB STATUS;

MyISAM

MyISAM is based on the older (and no longer available) ISAM storage engine, with many useful extensions. It has no transaction support and uses table-level locking, making it best suited to read-heavy or single-writer workloads rather than the general-purpose, highly concurrent use cases InnoDB is designed for.

Feature	MyISAM
Storage limits	256TB
Transactions	No
Locking granularity	Table
MVCC	No
Geospatial data type support	Yes
Geospatial indexing support	Yes
B-tree indexes	Yes
T-tree indexes	No
Hash indexes	No
Full-text search indexes	Yes
Clustered indexes	No
Data caches	No
Index caches	Yes
Compressed data	Yes (read-only, via myisampack)
Encrypted data	Yes
Cluster database support	No
Replication support	Yes
Foreign key support	No
Backup / point-in-time recovery	Yes
Query cache support	Yes
Update statistics for data dictionary	Yes

MyISAM tables store data across three files: a format/definition file, a data file (.MYD), and an index file (.MYI) – see Backup and Recovery for backing them up by direct file copy, and Utilities for the myisamchk and myisampack maintenance tools. Because it lacks transactions and row-level locking, MyISAM does not support GTID-based Replication.

CREATE TABLE audit_archive (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(64),
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT (event_name)
) ENGINE = MyISAM;

-- Table-level locking: a write lock blocks all other readers/writers
LOCK TABLES audit_archive WRITE;
INSERT INTO audit_archive (event_name) VALUES ('server_start');
UNLOCK TABLES;

-- Maintenance specific to MyISAM
CHECK TABLE audit_archive;
REPAIR TABLE audit_archive;
OPTIMIZE TABLE audit_archive;

CREATE TABLE audit_archive (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(64),
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT (event_name)
) ENGINE = MyISAM;

-- Table-level locking: a write lock blocks all other readers/writers
LOCK TABLES audit_archive WRITE;
INSERT INTO audit_archive (event_name) VALUES ('server_start');
UNLOCK TABLES;

-- Maintenance specific to MyISAM
CHECK TABLE audit_archive;
REPAIR TABLE audit_archive;
OPTIMIZE TABLE audit_archive;

MEMORY

The MEMORY storage engine (formerly known as HEAP) stores table contents entirely in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, MEMORY tables should be used only as temporary work areas or read-only caches for data pulled from other tables.

MEMORY performance is limited by contention: single-thread execution and table-lock overhead when processing updates restrict scalability as load increases, especially for statement mixes that include writes. Despite processing entirely in memory, MEMORY tables are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload – the table locking involved in updates can slow down concurrent access from multiple sessions.

The maximum size of a MEMORY table is governed by the max_heap_table_size system variable, which defaults to 16MB.

Feature	MEMORY
Storage limits	RAM (bounded by max_heap_table_size)
Transactions	No
Locking granularity	Table
MVCC	No
Geospatial data type support	No
Geospatial indexing support	No
B-tree indexes	Yes
T-tree indexes	No
Hash indexes	Yes
Full-text search indexes	No
Clustered indexes	No
Data caches	N/A
Index caches	N/A
Compressed data	No
Encrypted data	Yes
Cluster database support	No
Replication support	Yes
Foreign key support	No
Backup / point-in-time recovery	Yes
Query cache support	Yes
Update statistics for data dictionary	Yes

-- A hash-indexed lookup table kept entirely in RAM
CREATE TABLE session_lookup (
    session_id CHAR(32) PRIMARY KEY,
    user_id INT NOT NULL,
    INDEX (user_id) USING HASH
) ENGINE = MEMORY;

-- Cap the table's size explicitly for this session
SET SESSION max_heap_table_size = 64 * 1024 * 1024; -- 64MB

INSERT INTO session_lookup VALUES ('a1b2c3d4e5f6', 42);
SELECT user_id FROM session_lookup WHERE session_id = 'a1b2c3d4e5f6';

-- Data is lost on restart -- MEMORY tables are for caches/scratch space only

-- A hash-indexed lookup table kept entirely in RAM
CREATE TABLE session_lookup (
    session_id CHAR(32) PRIMARY KEY,
    user_id INT NOT NULL,
    INDEX (user_id) USING HASH
) ENGINE = MEMORY;

-- Cap the table's size explicitly for this session
SET SESSION max_heap_table_size = 64 * 1024 * 1024; -- 64MB

INSERT INTO session_lookup VALUES ('a1b2c3d4e5f6', 42);
SELECT user_id FROM session_lookup WHERE session_id = 'a1b2c3d4e5f6';

-- Data is lost on restart -- MEMORY tables are for caches/scratch space only

Query OK, 1 row affected

user_id
42

CSV

The CSV storage engine stores table data in plain text files using comma-separated values format – the same .CSV file that other tools such as spreadsheet programs can read directly. This makes it convenient for exchanging data with external, non-MySQL processes, but it comes without indexing or transactional guarantees.

Feature	CSV
Storage limits	None
Transactions	No
Locking granularity	Table
MVCC	No
Geospatial data type support	Yes
Geospatial indexing support	No
B-tree indexes	No
T-tree indexes	No
Hash indexes	No
Full-text search indexes	No
Clustered indexes	No
Data caches	No
Index caches	No
Compressed data	No
Encrypted data	No
Cluster database support	No
Replication support	Yes
Foreign key support	No
Backup / point-in-time recovery	Yes (copy the .CSV file directly)
Query cache support	No
Update statistics for data dictionary	Yes

Because CSV tables have no indexes, every query against one performs a full table scan; they are best used as a staging area for importing/exporting data rather than for querying directly – see Backup and Recovery for LOAD DATA and SELECT ... INTO OUTFILE, which produce and consume the same delimited text format.

CREATE TABLE feed_import (
    sku VARCHAR(20),
    price DECIMAL(10,2),
    updated_at DATETIME
) ENGINE = CSV;

-- The underlying file (feed_import.CSV in the database directory) can be
-- edited directly with any external tool, then read back immediately
SELECT * FROM feed_import;

-- Typically used only as a staging table before loading into InnoDB
CREATE TABLE feed_final LIKE feed_import;
ALTER TABLE feed_final ENGINE = InnoDB;
INSERT INTO feed_final SELECT * FROM feed_import;

CREATE TABLE feed_import (
    sku VARCHAR(20),
    price DECIMAL(10,2),
    updated_at DATETIME
) ENGINE = CSV;

-- The underlying file (feed_import.CSV in the database directory) can be
-- edited directly with any external tool, then read back immediately
SELECT * FROM feed_import;

-- Typically used only as a staging table before loading into InnoDB
CREATE TABLE feed_final LIKE feed_import;
ALTER TABLE feed_final ENGINE = InnoDB;
INSERT INTO feed_final SELECT * FROM feed_import;

BLACKHOLE

The BLACKHOLE storage engine accepts data but throws it away – no data is ever stored, and retrievals always return an empty result.

Because writes to a BLACKHOLE table are still recorded in the binary log, it is used as a lightweight relay in some Replication topologies: a server can be configured to write all events through a BLACKHOLE table purely to forward them downstream, without duplicating the data locally. It is also occasionally used to benchmark server overhead independent of storage cost, since writes complete without any actual persistence work.

Feature	BLACKHOLE
Storage limits	0 (no data is stored)
Transactions	No
Locking granularity	Table
MVCC	No
Geospatial data type support	Yes
Geospatial indexing support	No
B-tree indexes	No
T-tree indexes	No
Hash indexes	No
Full-text search indexes	No
Clustered indexes	No
Data caches	No
Index caches	No
Compressed data	No
Encrypted data	No
Cluster database support	No
Replication support	Yes
Foreign key support	No
Backup / point-in-time recovery	N/A (no data to back up)
Query cache support	No
Update statistics for data dictionary	No

CREATE TABLE relay_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payload JSON
) ENGINE = BLACKHOLE;

-- The insert succeeds and is written to the binary log, but no row is stored
INSERT INTO relay_events (payload) VALUES ('{"type":"order_created"}');

SELECT COUNT(*) FROM relay_events; -- always 0

CREATE TABLE relay_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payload JSON
) ENGINE = BLACKHOLE;

-- The insert succeeds and is written to the binary log, but no row is stored
INSERT INTO relay_events (payload) VALUES ('{"type":"order_created"}');

SELECT COUNT(*) FROM relay_events; -- always 0

Query OK, 1 row affected

COUNT(*)
0

MERGE

The MERGE storage engine, also known as MRG_MyISAM, presents a collection of identical MyISAM tables as a single virtual table. "Identical" means every underlying table has the same column and index definitions.

CREATE TABLE t1 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20)) ENGINE=MyISAM;

CREATE TABLE t2 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20)) ENGINE=MyISAM;

INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');

CREATE TABLE total (
   a INT NOT NULL AUTO_INCREMENT,
   message CHAR(20), INDEX(a))
   ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Two identically-structured MyISAM tables combined into a single queryable MERGE table. INSERT_METHOD=LAST directs new rows inserted through total into t2, the last table in the UNION list.

Feature	MERGE
Storage limits	Sum of the underlying MyISAM tables (each up to 256TB)
Transactions	No
Locking granularity	Table
MVCC	No
Geospatial data type support	No
Geospatial indexing support	No
B-tree indexes	Yes
T-tree indexes	No
Hash indexes	No
Full-text search indexes	No
Clustered indexes	No
Data caches	No
Index caches	Yes
Compressed data	No
Encrypted data	No
Cluster database support	No
Replication support	Yes
Foreign key support	No
Backup / point-in-time recovery	No (back up the underlying MyISAM tables instead)
Query cache support	Yes
Update statistics for data dictionary	No

MERGE tables do not support Partitioning. As with MyISAM, a MERGE table's underlying tables must not be transactional, and it inherits MyISAM's table-level locking.

CREATE TABLE sales_2023 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2)
) ENGINE = MyISAM;

CREATE TABLE sales_2024 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2)
) ENGINE = MyISAM;

CREATE TABLE sales_all (
    id INT NOT NULL AUTO_INCREMENT,
    amount DECIMAL(10,2),
    INDEX(id)
) ENGINE = MERGE UNION = (sales_2023, sales_2024) INSERT_METHOD = LAST;

INSERT INTO sales_all (amount) VALUES (150.00); -- lands in sales_2024
SELECT SUM(amount) FROM sales_all;               -- queries both years at once

CREATE TABLE sales_2023 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2)
) ENGINE = MyISAM;

CREATE TABLE sales_2024 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2)
) ENGINE = MyISAM;

CREATE TABLE sales_all (
    id INT NOT NULL AUTO_INCREMENT,
    amount DECIMAL(10,2),
    INDEX(id)
) ENGINE = MERGE UNION = (sales_2023, sales_2024) INSERT_METHOD = LAST;

INSERT INTO sales_all (amount) VALUES (150.00); -- lands in sales_2024
SELECT SUM(amount) FROM sales_all;               -- queries both years at once

Query OK, 1 row affected

SUM(amount)
150.00

FEDERATED

The FEDERATED storage engine accesses data on a remote MySQL server without using replication or cluster technology. No data is stored in the local table; querying a local FEDERATED table transparently pulls the data from the remote (federated) table on demand.

To enable FEDERATED, the server binary must be started with the --federated option.

Feature	FEDERATED
Storage limits	Depends on the remote table's engine
Transactions	No
Locking granularity	N/A (delegated to the remote server)
MVCC	No
Geospatial data type support	No
Geospatial indexing support	No
B-tree indexes	No
T-tree indexes	No
Hash indexes	No
Full-text search indexes	No
Clustered indexes	No
Data caches	No
Index caches	No
Compressed data	No
Encrypted data	No
Cluster database support	No
Replication support	Yes (locally – the remote server replicates independently)
Foreign key support	No
Backup / point-in-time recovery	No (back up the remote table on its own server)
Query cache support	No
Update statistics for data dictionary	No

-- On the remote server: an ordinary table to expose
CREATE TABLE remote_db.customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE = InnoDB;

-- On the local server (started with --federated): a table pointing at it
CREATE TABLE local_customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE = FEDERATED
  CONNECTION = 'mysql://fed_user:password@remotehost:3306/remote_db/customers';

-- Queries against local_customers transparently hit the remote table
SELECT * FROM local_customers WHERE id = 1;

-- On the remote server: an ordinary table to expose
CREATE TABLE remote_db.customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE = InnoDB;

-- On the local server (started with --federated): a table pointing at it
CREATE TABLE local_customers (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) ENGINE = FEDERATED
  CONNECTION = 'mysql://fed_user:password@remotehost:3306/remote_db/customers';

-- Queries against local_customers transparently hit the remote table
SELECT * FROM local_customers WHERE id = 1;

EXAMPLE

The EXAMPLE storage engine is a stub engine that does nothing; it exists in the MySQL source code as an illustration of how to begin writing a new storage engine.

When an EXAMPLE table is created, the server creates a table format file in the database directory, named after the table with a format-file extension; no other files are created. No data can actually be stored into the table, and retrievals always return an empty result – it exists purely for developers studying or prototyping the storage engine API, not for application use.

Feature	EXAMPLE
Storage limits	N/A (no data is stored)
Transactions	No
Locking granularity	Table
MVCC	No
Geospatial data type support	No
Geospatial indexing support	No
B-tree indexes	No
T-tree indexes	No
Hash indexes	No
Full-text search indexes	No
Clustered indexes	No
Data caches	No
Index caches	No
Compressed data	No
Encrypted data	No
Cluster database support	No
Replication support	No
Foreign key support	No
Backup / point-in-time recovery	No
Query cache support	No
Update statistics for data dictionary	No

CREATE TABLE stub_demo (
    id INT PRIMARY KEY,
    note VARCHAR(50)
) ENGINE = EXAMPLE;

INSERT INTO stub_demo VALUES (1, 'hello'); -- accepted, but nothing is stored
SELECT * FROM stub_demo;                    -- always returns an empty result

CREATE TABLE stub_demo (
    id INT PRIMARY KEY,
    note VARCHAR(50)
) ENGINE = EXAMPLE;

INSERT INTO stub_demo VALUES (1, 'hello'); -- accepted, but nothing is stored
SELECT * FROM stub_demo;                    -- always returns an empty result

Query OK, 1 row affected

id	note

Empty set (0 rows)

Storage engines can be mixed freely within a database, and even within a single query, and an existing table's engine can be changed with ALTER TABLE ... ENGINE = ...:

-- List the storage engines available on this server (see the SHOW ENGINES
-- example above for its output)
SHOW ENGINES;

-- Mixing engines within a single database, and even a single query
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2)
) ENGINE = InnoDB;

CREATE TABLE session_cache (
    session_id CHAR(32) PRIMARY KEY,
    payload VARCHAR(255)
) ENGINE = MEMORY;

SELECT o.id, s.payload
FROM orders AS o
JOIN session_cache AS s ON s.session_id = CAST(o.customer_id AS CHAR);

-- Changing an existing table's storage engine
ALTER TABLE session_cache ENGINE = InnoDB;

-- List the storage engines available on this server (see the SHOW ENGINES
-- example above for its output)
SHOW ENGINES;

-- Mixing engines within a single database, and even a single query
CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10,2)
) ENGINE = InnoDB;

CREATE TABLE session_cache (
    session_id CHAR(32) PRIMARY KEY,
    payload VARCHAR(255)
) ENGINE = MEMORY;

SELECT o.id, s.payload
FROM orders AS o
JOIN session_cache AS s ON s.session_id = CAST(o.customer_id AS CHAR);

-- Changing an existing table's storage engine
ALTER TABLE session_cache ENGINE = InnoDB;

Feature	ARCHIVE
Storage limits	None
Transactions	No
Locking granularity	Row
MVCC	No
Geospatial data type support	No
Geospatial indexing support	No
B-tree indexes	No
T-tree indexes	No
Hash indexes	No
Full-text search indexes	No
Clustered indexes	No
Data caches	No
Index caches	No
Compressed data	Yes
Encrypted data	Yes
Cluster database support	No
Replication support	Yes
Foreign key support	No
Backup / point-in-time recovery	Yes
Query cache support	Yes
Update statistics for data dictionary	Yes

Plugins	<<<	Administration	>>>	Environments
last update: 17 Aug 2026

MENU

Storage Engines

InnoDB

MyISAM

MEMORY

CSV

ARCHIVE

BLACKHOLE

MERGE

FEDERATED

EXAMPLE