Since MySQL version 5.5, the InnoDB storage engine has been set as the default “out of the box.”
InnoDB is a transactional storage engine, 100% ACID-compliant, stable, robust, and entirely free. It has made remarkable progress since MySQL 4.x, with significant improvements in versions 5.1, 5.5, 5.6, and more recently 5.7.
The fact that InnoDB is the default doesn’t prevent us from creating (or altering) tables using the ENGINE option to reflect the storage engine that best fits our needs.
Traditional relational databases do not use the concept of storage engines (table types). This is an innovation and a merit of MySQL. Some see it as a flaw or a bug, but it’s actually a very powerful and welcome feature.
Declaring MyISAM dead is, to some extent, declaring dead one of MySQL’s most exciting features: the Storage Engine.
It’s true that most applications, or at least most tables, will be transactional, hence InnoDB. But there is plenty of room for MyISAM, and I doubt we are witnessing MyISAM’s death.
I like to share the example of Zabbix, an exceptional monitoring tool. Once, I maintained a 700 GB table. Simply changing the engine from InnoDB to MyISAM made its size drop to less than 100 GB. Think about it: if a server monitoring capture stops being saved or recorded, will that affect the company’s business? Why use transactions then?
MyISAM can be used in a somewhat “less noble” way to store data that may suffer from incompleteness, absence, redundancy, and integrity issues — but it is undeniably highly performant.
Between MyISAM and InnoDB, which should you choose? It’s simpler than it looks; just ask your entities or tables:
- Transactions: If data needs to be protected and orchestrated within transactions, use InnoDB.
- Referential Integrity (foreign keys): If referential integrity is never used, use MyISAM.
- Granularity: Row-level locking and lock-free reads, InnoDB.
- Performance: Ultra-fast reads and writes, definitely MyISAM.
- Automatic Recovery: If fast online recovery from data or index page problems is important, InnoDB.
- Footprint (disk space): Each InnoDB row occupies at least 1 KB. If you have many tables with rows smaller than 500 bytes, and footprint is critical, choose MyISAM.
The truth is, InnoDB and MyISAM are different beasts for different use cases.
I prefer a less passionate and more objective approach:
- InnoDB: for all transactional tables, absolutely all!
- MyISAM: for all non-transactional tables such as:
- Summaries
- Logs
- Data ingestion for future processing and consumption
- Intermediate tables (for reporting prep)
- Dashboards
- Queues
- Staging (ETL)
So, I choose MyISAM if most of these apply: it can be rebuilt, it is not the primary source of data, temporary or dynamic use (staging), ETL processes, and some data or integrity loss is acceptable.
The truth is there’s still a lot of space for MyISAM, and we won’t see its death anytime soon. So, don’t blow out the candles yet!
Visit our Blog
Learn more about databases
Learn about monitoring with advanced tools
Have questions about our services? Visit our FAQ
Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!
Discover the History of HTI Tecnologia
