Nostalgia: What Happened to MyISAM?

InnoDB is now the primary storage engine that MySQL can be proud of – however, it was not always that way; in the past, MyISAM was widely regarded as the better choice. It was used for various projects ranging from data analysis to developing simple content management systems for forums. Today, we will explore the database engine's storied past and what the future may hold for MyISAM.
​

Why MyISAM?

​
In the past, MyISAM was widely regarded as the storage engine of choice mainly because it was said to be more simple to design and maintain. It supported full-text indexes and took up less space on the disk than InnoDB did. MyISAM was also considered ideal for applications mainly focused on reads and was said to be faster than InnoDB due to its architecture.
​
Here’s a table outlining MyISAM performance in key areas (as compared with InnoDB) that might help you understand why MyISAM used to be the primary MySQL database engine:
​

The majority of these statements were indeed true for MyISAM-based tables. In the past, people thought MyISAM was faster than InnoDB and suitable for heavy read-operations, so MyISAM was a frequent choice for solutions ranging from content management systems to smaller search engines. MyISAM was also generally used by developers beginning their journey inside the MySQL world. Since MyISAM has long been considered simpler than InnoDB, some developers even wrote third-party tools and solutions tailored towards MyISAM.
​

The InnoDB Switch

​
InnoDB replaced MyISAM when MySQL 5.5 was released in 2010 – since then, it has been getting less and less attention from database administrators and developers alike. Here are a couple of reasons as to why:
​

As you can see, while MyISAM definitely had some features introduced earlier on that made it superior to its counterpart, InnoDB quickly caught up. MyISAM, as noted in one of our previous blog posts, nowadays should only be a viable option when the primary query being run is a basic COUNT(*), but other than that, it doesn’t have many use cases in the MySQL world.
​

Why InnoDB?

​
As MySQL continued to develop, the MySQL crew started putting the storage engine further and further in a dark alley – MySQL now refers to MyISAM as an “alternative storage engine,” letting developers and MySQL DBAs know that it’s not a viable option – not anymore. In MySQL’s mind, users should elect to use InnoDB instead. Indeed, when we look into some of the system tables in MySQL 8.0 and up, we will see that all of them are based upon InnoDB and no longer on MyISAM as some of them were before – coincidence?
​

Somewhat paradoxically, InnoDB is frequently referred to as the primary MySQL database engine after MyISAM. A big part of that is because the engine comes with ACID compliance by default, meaning that all of its transactions are “safe” even when interrupted; ACID will ensure that your data keeps being integral and valid despite any power failures, errors, interruptions, or any other mishaps.
​

However, ACID compliance is not the root cause, since InnoDB comes with a variety of other features too. To make the switch easier for you, learn how to look into the functionalities of the data file path to set the size and the path of the file where InnoDB holds its data, the buffer pool size (a cache for InnoDB’s data), the log file and buffer sizes (log files are important when recovering from a crash), and keep an eye on disk I/O as well – specifically, the flushing methods. If you find it viable to trade-off time for data consistency, look into O_DIRECT too – if things are the other way around for you, try using O_DSYNC. If that did not convince you, keep in mind that InnoDB is also a viable option for big data – we have explained why earlier, so if you want to dig deeper into this subject, you're more than welcome to do that.
​

Remember that InnoDB, unlike MyISAM, also comes with a different way of locking – while MyISAM’s table-level locking capabilities may have some merits, it’s not a very viable option in the long run. However, MyISAM’s table locking may be very useful if we want to avoid deadlocks – since a deadlock refers to a situation where one or more locks are waiting for one another to complete and neither ever doing so, table-level locking might be helpful, but only as a final straw – deadlocks can also be avoided by ensuring that only one process uses a resource at a certain time. To learn more, look into Coffman’s conditions – that’s one of them.
​

What Does the Future Hold?

​
After seeing all of the rapid advancements of InnoDB and its replacement of MyISAM, you might have caught yourself thinking – what does the future hold? You might not be so sure, and we aren’t either – however, what we can be sure of is that MyISAM is being outpaced in every aspect. So, if you find yourself still using the engine, switch to InnoDB:
​

ALTER TABLE demo_myisam ENGINE = InnoDB;
​

It’s as simple as that!
​

Conclusion

​
We hope that this blog post has provided you with some insight into how MySQL used MyISAM in the past and why it’s no longer doing so – consider upgrading your database instances to InnoDB, and don’t forget that there also are tools that may help you deal with your SQL-related issues quickly and in style.

Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com