If you’ve been a frequent user of MySQL now or in the past, you know that there are multiple storage engines MySQL allows us to choose from. The most frequent is InnoDB which is known as a “high performance” storage engine. If InnoDB is optimized properly via the parameters provided by MySQL in my.cnf, the storage engine can do wonders.
MyISAM is considered more obsolete though, but it has its own use cases including providing an exact count of the rows in a specific table and storing database files in a recoverable format since the files provided by MyISAM are not bound to any other files such as ibdata1 provided by InnoDB.
What Alternative Storage Engines are Provided by MySQL?
InnoDB is the default storage engine provided by MySQL – that’s a given. However, there are a bunch of other storage engines provided by the RDBMS, including the following:
Percona XtraDB
This storage engine is only available on Percona’s infrastructure (i.e., only for people using Percona Server). It is still essentially meant to be a fit for those who want InnoDB capabilities on their infrastructure.
It’s considered an advanced version of InnoDB because it’s designed for scale on any hardware, and it has a couple of other features designed for high performance and capability. It’s also fully backward compatible with InnoDB, so no worries if you want to switch back towards InnoDB after you’ve tried the storage engine and disliked it.
MyISAM
This storage engine was the default storage engine until MySQL 5 rolled around so it’s old as a piece of bread, and most database administrators advise against using it. In fact, the newest version of MySQL considers this storage engine to be obsolete as it’s not reliable, and also prone to crashes due to how it’s built.
However, it has a couple of upsides – those include the fact that all files relevant to MyISAM are stored in. MYD (.MYData) files alongside MYI (.MYIndex) files an d they are not dependent on any one file to work, unlike InnoDB is dependent on ibdata1.
The storage engine is also renowned for its capability to actually store row count of tables inside of its own metadata (that’s why InnoDB doesn’t show the row count and MyISAM does), but that’s really the extent of the usefulness of this storage engine. Users of MyISAM will face frequent crashes, data corruption, and other issues, which means that this storage engine is not a fit for those who are looking for performance capabilities neither now, or anytime in the future. It’s useful if we want to play around with it though!
MEMORY
As the name suggests, this storage engine stores all of its data inside of the memory. The storage engine is designed to be a special-purpose storage engine as its all of its contents stored in memory, unlike other storage engines. Such an approach has a glaring flaw – if our server is shut down, all of the data is gone, but on the other hand, the storage engine is very suitable as a temporary working realm or as a read-only cache for data.
CSV
This storage engine is known for its capability to store data in .CSV files together with comma-separated values. There’s not much that can be said here: .csv files usually consist of data values that are separated by a comma (“,”), and that’s the primary reason the storage engine is called that way. Such a storage engine isn’t used very frequently, but it can be useful for some corner-cases of project development, for example, for bulk data imports, or simply to make the job easier for an accountant working with Excel – that way files can be imported straight into MySQL without any additional hassle.
ARCHIVE
This storage engine, as the name already suggests, is suitable for archival operations. The primary use case of this storage engine is to store large amounts of data that isn’t accessed frequently – for such a use case, this storage engine is golden because it’s footprint on the disk is almost non-existent allowing DBAs and server admins to focus on other tasks rather than scaling up the server simply to support an archive of data.
FEDERATED
This storage engine allows us to access data in a remote database without using replication or clustering in the fashion that if we query a local table with a FEDERATED storage engine, MySQL automatically pulls data from other tables running the same engine. As easy as that! One thing to note, though, is that the FEDERATED storage engine would not usually be enabled by default, but it can be enabled by running MySQL after using the –federated option.
BLACKHOLE
If there’s a storage engine in MySQL that makes you question the existence of the universe, it’s BLACKHOLE. Literally – the storage engine is called after a black hole in the universe in the fashion that it accepts data, but never stores it inside of itself. You might question yourself and wonder what it’s use case is, but MySQL explains it in a simple fashion that probably doesn’t warrant further explanation at all – such a storage engine should be used performance bottlenecks in MySQL that are not related to storage engines, or used for running triggers on data that shouldn’t be kept.
EXAMPLE
Last but not least, there’s an example in the MySQL infrastructure letting you get a grasp of how storage engines are built in the first place. That’s the job of the EXAMPLE storage engine – this storage engine doesn’t store data (or, better put, it doesn’t have any functionality at all), but rather, it’s intended to become an example on how to build or improve on the functionality of storage engines yourself. If you want an example that depicts how to build storage engines in the MySQL source code, look no further! Glance at the MySQL documentation and start building one yourself.
Exploring MySQL Beyond Storage Engines
Should you want to explore MySQL beyond it’s storage engine capabilities, be advised that it’s a dangerous world out there in the sense that there’s quite a bit of information that might not be true – to get up to date information, consider reading up on blogs such as the one provided by MariaDB, Percona, and other database vendors.
Of course, the dbWatch blog is a very good place to start – it will provide you information on various kinds of database management systems aside from MySQL, and provide you information on how to best solve the issues related to any database management system you elect to use.
Grab a free trial of dbWatch to see how your databases fare against issues targeting them free!
