InnoDB has been the default storage engine for MySQL since version 5.5 (July 2010!).
If you are still using MyISAM as the storage engine for your tables, it might be time to consider migrating to InnoDB. InnoDB offers several advantages over MyISAM:
- MVCC,
- support for ACID transactions,
- row-level locking,
- crash recovery capabilities,
- foreign keys,
- hot backups,
- clone
In addition, compared to MyISAM, InnoDB is more reliable and less prone to data corruption.
The InnoDB storage engine also offers the ability to perform hot backups and supports the use of the amazing CLONE feature.
However, we still hear people using MyISAM because of some myths.
Let’s have a look at some of these myths:
Performance
Performance is the most important and “MyISAM is faster”…
In general if we compare the performance, since 5.7, InnoDB is faster. DimitriK illustrated this in this post. And in 8.0 the InnoDB performance kept improving.
We also get feedback that for temporary tables MyISAM is better… in MySQL 8.0, the storage engine for on-disk internal temporary tables is now only InnoDB. You can discover in this post why InnoDB is now the default also for on-disk internal temporary tables.
Before MySQL 8.0, there was still one single case where MyISAM was performing better than InnoDB: High Insert Rate, during bulk load. This was because MyISAM does not have MVCC and is not durable. With MySQL 8.0 you can choose to disable InnoDB durability disabling the redo logs, giving you high insert rate for the time of the bulk load.
Previously, before we added the parallel execution, COUNT(*) was faster in MyISAM. This is not the case anymore.
InnoDB is designed to take advantage of multi-core CPUs, which means that it can use multiple cores for parallel processing and achieve better performance on modern hardware.
Storage footprint
InnoDB consumes more diskspace than MyISAM. Even when pages are compressed but this is also the price to pay to fully support MVCC. JFG explains this very clearly in his blog post. Storage is not as expensive as it was decades ago and it we need to choose between diskpace consumption with row locking and durability…
Partitioning
In MySQL 8.0, each storage engine must provide Native Partitioning and currently only InnoDB delivers it. So if your workload requires partitioning, MyISAM is not an option if you plan to migrate to 8.0.
Native GIS
“GIS is only supported in MyISAM” this is also a statement we sometimes hear. In MySQL 8.0 we have made major advances in InnoDB’s support for GIS as you can discover it in these slides from Norvald.
InnoDB covers all you need related to GIS.
Full Text Search
Since MySQL 5.6 already, InnoDB supports Full Text Search (FTS), and it’s since then scaling 3x times better than on MyISAM (check it here). Of course in MySQL 8.0, we continued to support InnoDB FTS with support for CJK, ngram, mecab (see Matt Lord’s slides if you plan to start with FT).
High Availability
MySQL InnoDB Cluster (for HA), and InnoDB CluserSet (for DR), both based on Group Replication and require InnoDB.
If you are looking for a real High Availability and/or Disaster Recovery solution, MyISAM is not an option at all.
Cloud
None of the cloud managed MySQL Services include MyISAM. And this is also the case with MySQL Database Service in Oracle Cloud Infrastructure.
If all the major cloud providers have decided that MyISAM should not be part of their offering, why would you ?
Converting tables from MyISAM to InnoDB
There is a dedicated and detailed section in the MySQL Documentation: Converting Tables from MyISAM to InnoDB.
The migration in many cases is not complex and can be summarized by:
alter table <table_name> engine=InnoDB;And when you are done, don’t forget to:
set persist_only disabled_storage_engines='MyISAM';Conclusion
Migrating from MyISAM to InnoDB brings significant benefits to your MySQL database. InnoDB offers better performance, reliability, and scalability, making it the default choice for modern applications. InnoDB is the only possible choice if you care about your data and plan to use High Availability and Disaster Recovery.
