Some DBAs go their whole career using just one or two storage engines. In this article, we’ll take a peek at four remarkable storage engines you might have overlooked, all of which ship with MySQL 5.5.
What’s a Storage Engine?
Different applications create different data with different needs. Some applications require consistency and crash safety, others require speed, others require vast storage and can accept slow queries. MySQL’s pluggable storage engines let DBAs pick a storage model that fits the data while the application continues to use the same MySQL client libraries and SQL statements.
Ol’ Reliable MyISAM and InnoDB
Most MySQL developers choose between MyISAM and InnoDB. MyISAM is fast, and has more than a decade of developer and DBA goodwill behind it. InnoDB brings transaction support for full ACID compliance, and includes row-level locks and caching that can make it even faster than MyISAM under concurrent load.
Ultimately both these storage engines make wise compromises for the vast majority of everyday workloads. But when you have an extraordinary problem, you might turn to one of these:
Memory
The Memory storage engine is a good fit for super fast access to information that is either ephemeral (like session management) or exists elsewhere (like a temporary table). Memory tables have no disk-based permanence; if MySQL crashes or the server reboots, that data is gone. Note that you can get better performance than Memory tables, along with higher scale and permanence, if you make the jump to MySQL Cluster—but that’s a little more involved than switching storage engines.
Archive
The Archive engine is ideal for tables that you append lots of data to, but hardly ever read, like logs. It only supports INSERT and SELECT; it does not support UPDATE, REPLACE, or DELETE, or even ORDER BY. INSERTed rows are compressed before being appended to the table on disk. The engine provides no row cache and no indexes; SELECT statements always perform a complete table scan, uncompressing the table as they go. As a result, the data takes up very little space, but will be very slow to retrieve.
CSV
The CSV engine stores data in a comma-separated value file on disk. A CSV file provides an easy compatibility point to share this data with other systems (like Excel). The benefit of using the CSV engine (instead of import/export options like load data infile and select into outfile) is that the underlying CSV file is kept continuously up to date, while being manipulated with standard SQL statements.
Blackhole
Blackhole is a storage engine that doesn’t actually store anything. SELECT, UPDATE, and DELETE always return 0 rows. Any INSERT succeeds, but the data is thrown away. It’s commonly used in building a Replication Relay: the relay copies events from the master’s binlog to its own binlog, then downstream slaves can read from the relay’s binlog. But no one cares if the relay applied the master’s changes to itself, so the relay can use the Blackhole engine to dramatically reduce I/O. You can get hands-on experience building a relay with the blackhole engine in our online MySQL Replication course.
Thanks for reading! If you’d like to get a little better at MySQL every week, you should sign up for our MySQL Tip of the Week mailing list. Not only will it make you smarter, we periodically send subscribers discounts for our courses!