Thursday, January 29, 2015

InnoDB or MyISAM, That's the question

Currently MySQL Supports two storage engines InnoDB (default for MySQL 5.5+) and MyISAM. When you are creating a table you may be prompted with the question which one I should choose and why? This article is an attempt to shed some light to clear your thoughts make the correct decision.

Before go any further let's see what a Storage Engine actually does. In simple terms it is the most integral part of any database engine. If we put it to a definition  "Storage engine is what stores, handles, and retrieves information from a table". The marvel of MySQL we could have different storage engines for different tables. Even though there are plenty of storage engines supported by MySQL, this article will be focused on InnoDB and MyISAM. Which are the most common and frequently used storage engines.

MyISAM

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.

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.

No comments:

Post a Comment