Choosing the Right Storage Engine

You are viewing an old version of this article. View the current version here.

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

  • 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, useful, for example, if you want to run complex filtering rules on a slave without incurring any overhead on a master.
  • Cassandra is a NoSQL engine.
  • 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. XtraDB is a performance enhanced fork of InnoDB.
  • 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.
  • 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; Aria is MariaDB's more modern improvement.
  • OQGRAPH allows you to handle hierarchies (tree structures) and complex graphs (nodes having many connections in several directions).
  • ScaleDB is a commercial storage engine suited for large-scale online transaction processing and data warehousing.
  • 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.
  • XtraDB, a performance enhanced fork of InnoDB, is a good general transaction storage engine and usually the best choice if unsure. It is MariaDB's default engine.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.