Home | OEDBA

Choosing the Right Storage Engine

Contents

A high-level overview of the main reasons for choosing a particular storage engine:

Topic List

General Purpose

InnoDB is a good general transaction storage engine, and, from MariaDB 10.2, the best choice in most cases. It is the default storage engine from MariaDB 10.2. For earlier releases, XtraDB was a performance enhanced fork of InnoDB and is usually preferred.
XtraDB is the best choice in MariaDB 10.1 and earlier in the majority of cases. It is a performance-enhanced fork of InnoDB and is MariaDB's default engine until MariaDB 10.1.
Aria, MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copying between systems.
MyISAM has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes. Aria is MariaDB's more modern improvement.

Scaling, Partitioning

When you want to split your database load on several servers or optimize for scaling. We also suggest looking at Galera, a synchronous multi-master cluster.

Spider uses partitioning to provide data sharding through multiple servers.
ColumnStore utilizes a massively parallel distributed data architecture and is designed for big data scaling to process petabytes of data.
The MERGE storage engine is a collection of identical MyISAM tables that can be used as one. "Identical" means that all tables have identical column and index information.
TokuDB is a transactional storage engine which is optimized for workloads that do not fit in memory, and provides a good compression ratio. TokuDB has been deprecated by its upstream developers, and is disabled in MariaDB 10.5, and removed in MariaDB 10.6

Compression / Archive

MyRocks enables greater compression than InnoDB, as well as less write amplification giving better endurance of flash storage and improving overall throughput.
The Archive storage engine is, unsurprisingly, best used for archiving.
TokuDB is a transactional storage engine which is optimized for workloads that do not fit in memory, and provides a good compression ratio. TokuDB has been deprecated by its upstream developers, and is disabled in MariaDB 10.5, and removed in MariaDB 10.6

Connecting to Other Data Sources

When you want to use data not stored in a MariaDB database.

CONNECT allows access to different kinds of text files and remote resources as if they were regular MariaDB tables.
The CSV storage engine can read and append to files stored in CSV (comma-separated-values) format. However, since MariaDB 10.0, CONNECT is a better choice and is more flexibly able to read and write such files.
FederatedX uses libmysql to talk to the data source, the data source being a remote RDBMS. Currently, since FederatedX only uses libmysql, it can only talk to another MySQL RDBMS.
CassandraSE is a storage engine allowing access to an older version of Apache Cassandra NoSQL DBMS. It was relatively experimental, is no longer being actively developed and has been removed in MariaDB 10.6.

Search Optimized

Search engines optimized for search.

SphinxSE is used as a proxy to run statements on a remote Sphinx database server (mainly useful for advanced fulltext searches).
Mroonga provides fast CJK-ready full text searching using column store.

Cache, Read-only

MEMORY does not write data on-disk (all rows are lost on crash) and is best-used for read-only caches of data from other tables, or for temporary work areas. With the default InnoDB and other storage engines having good caching, there is less need for this engine than in the past.

Other Specialized Storage Engines

S3 Storage Engine is a read-only storage engine that stores its data in Amazon S3.
Sequence allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment, creating virtual, ephemeral tables automatically when you need them.
The BLACKHOLE storage engine accepts data but does not store it and always returns an empty result. This can be useful in replication environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.
OQGRAPH allows you to handle hierarchies (tree structures) and complex graphs (nodes having many connections in several directions).

Alphabetical List

The Archive storage engine is, unsurprisingly, best used for archiving.
Aria, MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copy between systems.
The BLACKHOLE storage engine accepts data but does not store it and always returns an empty result. This can be useful in replication environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.
CassandraSE is a storage engine allowing access to an older version of Apache Cassandra NoSQL DBMS. It was relatively experimental, is no longer being actively developed and has been removed in MariaDB 10.6.
ColumnStore utilizes a massively parallel distributed data architecture and is designed for big data scaling to process petabytes of data.
CONNECT allows access to different kinds of text files and remote resources as if they were regular MariaDB tables.
The CSV storage engine can read and append to files stored in CSV (comma-separated-values) format. However, since MariaDB 10.0, CONNECT is a better choice and is more flexibly able to read and write such files.
FederatedX uses libmysql to talk to the data source, the data source being a remote RDBMS. Currently, since FederatedX only uses libmysql, it can only talk to another MySQL RDBMS.
InnoDB is a good general transaction storage engine, and, from MariaDB 10.2, the best choice in most cases. It is the default storage engine from MariaDB 10.2. For earlier releases, XtraDB was a performance enhanced fork of InnoDB and is usually preferred.
The MERGE storage engine is a collection of identical MyISAM tables that can be used as one. "Identical" means that all tables have identical column and index information.
MEMORY does not write data on-disk (all rows are lost on crash) and is best-used for read-only caches of data from other tables, or for temporary work areas. With the default InnoDB and other storage engines having good caching, there is less need for this engine than in the past.
Mroonga provides fast CJK-ready full text searching using column store.
MyISAM has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes. Aria is MariaDB's more modern improvement.
MyRocks enables greater compression than InnoDB, as well as less write amplification giving better endurance of flash storage and improving overall throughput.
OQGRAPH allows you to handle hierarchies (tree structures) and complex graphs (nodes having many connections in several directions).
S3 Storage Engine is a read-only storage engine that stores its data in Amazon S3.
Sequence allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment, creating virtual, ephemeral tables automatically when you need them.
SphinxSE is used as a proxy to run statements on a remote Sphinx database server (mainly useful for advanced fulltext searches).
Spider uses partitioning to provide data sharding through multiple servers.
TokuDB is a transactional storage engine which is optimized for workloads that do not fit in memory, and provides a good compression ratio. TokuDB has been deprecated by its upstream developers, and is disabled in MariaDB 10.5, and removed in MariaDB 10.6
XtraDB is the best choice in MariaDB 10.1 and earlier in the majority of cases. It is a performance-enhanced fork of InnoDB and is MariaDB's default engine until MariaDB 10.1.