Choosing the Right Storage Engine

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

Topic List

General Purpose

• is a good general transaction storage engine, and the best choice in most cases. It is the default storage engine.

• , MariaDB's more modern improvement on , has a small footprint and allows for easy copying between systems.

• 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.

• is no longer available. It was a performance-enhanced fork of InnoDB and was MariaDB's default engine until

Scaling, Partitioning

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

• uses partitioning to provide data sharding through multiple servers.

• utilizes a massively parallel distributed data architecture and is designed for big data scaling to process petabytes of data.

• The storage engine is a collection of identical tables that can be used as one. "Identical" means that all tables have identical column and index information.

Compression / Archive

• enables greater compression than InnoDB, as well as less write amplification giving better endurance of flash storage and improving overall throughput.

• The storage engine is, unsurprisingly, best used for archiving.

Connecting to Other Data Sources

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

• The 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.

Search Optimized

Search engines optimized for search.

• is used as a proxy to run statements on a remote Sphinx database server (mainly useful for advanced fulltext searches).

• provides fast CJK-ready full text searching using column store.

Cache, Read-only

• 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 and other storage engines having good caching, there is less need for this engine than in the past.

Other Specialized Storage Engines

• is a read-only storage engine that stores its data in Amazon S3.

• 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 storage engine accepts data but does not store it and always returns an empty result. This can be useful in environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.

Alphabetical List

• The storage engine is, unsurprisingly, best used for archiving.

• , MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copy between systems.

• The storage engine accepts data but does not store it and always returns an empty result. This can be useful in environments, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.

_{This page is licensed: CC BY-SA / Gnu FDL}