[MySQL] Storage Engines

A storage engine is what stores, handles, and retrieves information from a table. There is no "perfect" or "recommended" storage engine to use, but for most applications the default MyISAM is fine. In MySQL there are 10 different storage engines, though all of them may not be available to you. To get a list of supported storage engines on your MySQL Server, you can do:

mysql -uroot -p

Password:

mysql> show engines;

This should provide a list of supported storage engines for your server. The standard engines supported by MySQL are:

MyISAM

InnoDB

MERGE

MEMORY (HEAP)

BDB (BerkeleyDB)

EXAMPLE

ARCHIVE

CSV

BLACKHOLE

ISAM

Benefits to selecting a storage engine comes down to the added benefits of speed and functionality,

Eg: If you store tore a lot of log data you might want to use the ARCHIVE storage engine which only supports INSERT and SELECT

MyISAM

• default storage engine
• not having TRANSACTION, and with having table-level locking as opposed to row-level locking
• best for performance and functionality
• great for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate.

InnoDB

• some additional time spent during initial setup
• ability to do row-level locking, as opposed to table-level locking, to increase performance time. This allows parallel INSERT/UPDATE/DELETE queries to be ran on the same table, unlike MyISAM where each query has to wait its turn to run
• foreign key functionality
• provides caching for data and indexes in memory, as well as on disk, which provides a large increase in performance gain

MERGE

The MERGE storage engine was added in MySQL 3.23.25. It enables users to have a collection of identical MyISAM tables to be handeled by a single table. There are constraints to this type, such as all tables needing to have the same definition, but the usefullness here quickly becomes apparently. If you store sales transactions on your site in a daily table, and want to pull a report for the month, this would allow you to execute a query against a single table to pull all sales for the month.

MEMORY (HEAP)

The HEAP storage engine, also referred to as MEMORY, provides in-memory tables. MySQL Server will retain the format of the tables in order to quickly create a "trash" table and access the information on the fly for better processing, it is not great for long term usage due to data integrity. Similar to InnoDB, this is not for the light of RAM.

BDB (BerkeleyDB)

The BDB handles transaction-safe tables and uses a hash based storage system. This allows for some of the quickest reading of data, especially when paired with unique keys. There are, however, many downfalls to the BDB system, including the speed on un-index rows, and this makes the BDB engine a less than perfect engine choice. Because of this, many people tend to overlook the BDB engine. I feel, however, that it does have a place in database design when the right situation calls for it.

EXAMPLE

This storage engine was added in MySQL 4.1.3. It is a "stub" engine that serves no real purpose, except to programmers. EXAMPLE provides the ability to create tables, but no information can be inserted or retrieved.

ARCHIVE

The ARCHIVE storage engine was added in MySQL 4.1.3 as well, and is used for storing large amounts of data without indexes in a very small footprint. This engine will only support INSERT and SELECT, and all information is compresses. This makes it the perfect storage engine for logs, point of sale transactions, accounting, etc. While this may seem very useful, keep in mind that when reading data the entire table must be de-compressed and read before data can be returned. Therefore, this is the ideal engine for storage that is only called on a low-rate basis.

CSV

CSV, added in MySQL 4.1.4, stores data in text files using comma-separated values. As such this is not an ideal engine for large data storage, tables requiring indexing, etc. The best use-case for this is transferring data to a spreadsheet for later use.

BLACKHOLE

Though seemingly poinless at first, the BLACKHOLE engine, which does not allow for any data to be stored or retrieved, is good for testing database structures, indexes, and queries. You can still run INSERTS against a BLACKHOLE table, with the knowledge that all inserts will vanish into the void.

ISAM

The original storage engine was ISAM, which managed nontransactional tables. This engine has since been replaced by MyISAM, and while MySQL Engineers recommend that it should no longer be used, it does have its place in this article for historical purposes. If you feel that you need to use ISAM, just remember that MyISAM is backwards compatiable and should provide you with everything and more.

Conclusion

In conclusion, while there is no perfect catchall storage engine, it can be fairly safe to say that InnoDB and MyISAM are the typical go-to for many applications and DBAs. It is good to note, however, that though they are a good catchall, they may not be ideal for every situation and there may be another storage engine available that will increase performance in your application/environment.