An Introduction To MySQL Storage Engines
MySQL provides support for thirteen different
storage engines which act as varying table type handlers. Most people who use
MySQL on a regular basis already know about the two most common storage
engines, MyISAM and InnoDB. Most of the time, the default storage engine as
defined by the store_engine option in the MySQL config file is typically
MyISAM, and this is usually what most people go with. In fact, many people do
not even take the time to select a storage-engine, and just use the default.
You can also assign a storage engine to a specific table with the following
syntax: CREATE TABLE tablename (column1, column2, [etc...]) ENGINE =
[storage_engine].
What is a Storage Engine?
For those of you who do not know, 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:
For those of you who do not know, 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:
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
The
benefits to selecting a storage engine comes down to the added benefits of
speed and functionality. For example, if you store a lot of log data you might
want to use the ARCHIVE storage engine which only supports INSERT and SELECT.
All storage engines can be selected per server, per database and per table,
giving you fine-grained control over your schema. MySQL storage engines is a
major reason why MySQL has gained such popularity over the years, as opposed to
other single-storage-engine supporting databases.
MyISAM
As stated earlier, MyISAM is the typical default storage engine for MySQL servers. The MyISAM type is actually a branch, or child, of the ISAM engine. If you are ok with not having TRANSACTION, and with having table-level locking as opposed to row-level locking, MyISAM is typically the best for performance and functionality. The MyISAM type is great for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate. Other options might want to be considered with very high INSERT/UPDATE queries due to the restrictions of table-level locking causing a decrease in performance.
The max number of rows supported for MyISAM is ~4.29E+09 with up to 64 indexs per table. It is also good to note that fields of TEXT/BLOB can be fully indexed for cases such as searching.
InnoDB
Compared to MyISAM, InnoDB provides many more feature to increase performance. There is some additional time spent during initial setup, as opposed to MyISAM, but the benefits far outweigh the time spent. One major difference is the 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.
Additionally, InnoDB provides foreign key functionality. This allows you to ensure that dependent data in table one is present before inserting data into table two. Likewise, it prevents data in table one from being deleted if there is data in table two that depends on it.
InnoDB also provides caching for data and indexes in memory, as well as on disk, which provides a large increase in performance gain. For those low on RAM this might not be the ideal solution, but with memory as cheap as it is this is becoming less of an issue.
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.
MyISAM
As stated earlier, MyISAM is the typical default storage engine for MySQL servers. The MyISAM type is actually a branch, or child, of the ISAM engine. If you are ok with not having TRANSACTION, and with having table-level locking as opposed to row-level locking, MyISAM is typically the best for performance and functionality. The MyISAM type is great for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate. Other options might want to be considered with very high INSERT/UPDATE queries due to the restrictions of table-level locking causing a decrease in performance.
The max number of rows supported for MyISAM is ~4.29E+09 with up to 64 indexs per table. It is also good to note that fields of TEXT/BLOB can be fully indexed for cases such as searching.
InnoDB
Compared to MyISAM, InnoDB provides many more feature to increase performance. There is some additional time spent during initial setup, as opposed to MyISAM, but the benefits far outweigh the time spent. One major difference is the 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.
Additionally, InnoDB provides foreign key functionality. This allows you to ensure that dependent data in table one is present before inserting data into table two. Likewise, it prevents data in table one from being deleted if there is data in table two that depends on it.
InnoDB also provides caching for data and indexes in memory, as well as on disk, which provides a large increase in performance gain. For those low on RAM this might not be the ideal solution, but with memory as cheap as it is this is becoming less of an issue.
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.
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.
slot—Slot number of the interface. 
