MyISAM vs. InnoDB: The Problem

All MySQL database administrators are aware of its prominent storage engines – InnoDB (some are also aware of its modification made by Percona – XtraDB), MEMORY, CSV, MERGE, ARCHIVE, FEDERATED, BLACKHOLE, and so on.

Different storage engines are responsible for making different parts of MySQL work – and while InnoDB is widely considered to be one of the most prominent storage engines in the entire infrastructure of MySQL, for some time it also had a primary competitor to itself – MyISAM.

What is MyISAM?

MyISAM was a default storage engine employed by MySQL until MySQL 5.5 rolled around. Some developers have considered MyISAM obsolete long before it was pronounced as such (MyISAM was being used as a default storage engine until December of 2009), and, for some, this storage engine still has its upsides.

MyISAM vs. InnoDB

One upside offered by MyISAM is the fact that it actually displays the number of rows used by its tables (the storage engine stores the row count inside of the table’s metadata), but other than that, the storage engine doesn’t have many use cases. This storage engine was used in the past mostly because of the fact that some MySQL engineers believed that the storage engine is a good competitor to InnoDB since older versions of InnoDB couldn’t offer such a wide range of choices, but now even the biggest skeptics of InnoDB agree that MyISAM is obsolete. Here are a couple of reasons why:

MyISAM	InnoDB
Non-transactional storage engine – if any writes need to be rolled back, we need to do so manually.	Transactional storage engine – automatically rolls back write operations if they are not completed.
Stores files related to the tables powered by the storage engine in separate files – data is stored in a file named after a table and has the extension .MYD (.MYData), and indexes are stored in a file having an extension .MYI (.MYIndex.)	Powered by ibdata1 – the ibdata1 file stores all of the data, indexes, and metadata stored in tables powered by InnoDB. With that being said, InnoDB can be made to store only metadata inside of ibdata1 thus making the file significantly smaller.
Prone to crashes due to not supporting ACID principles.	Fully supports ACID principles, though the support can be traded for performance by modifying a couple of variables such as innodb_flush_log_at_trx_commit.
Uses table-level locking and doesn’t support row-level locking.	Uses row-level locking – doesn’t lock the entire table when operations are in progress.
Doesn’t support foreign keys.	Supports foreign keys.
Supports full-text search – this property of MyISAM was one of the primary arguments advocating the use of the storage engine in the past, however, it’s mostly no longer true.	Supports full-text search as of MySQL 5.6.4 eliminating the argument about full-text search.

Table Corruption

Many developers would say that MyISAM should be avoided like the plague simply for the reasons stated above – however, another very prominent problem with the storage engine is that its tables are very prone to table corruption. Because MyISAM does not support an ACID model (see above), it’s recommended to avoid using it on mission-critical systems. No ACID support means that if we run long-running queries and leave them running overnight and out electricity goes out, we can be sure that our data is not getting recovered – an error message of such nature may be a very good indicator that our database is in line for trouble as well:

Incorrect key file for table ‘demo_table’. Try to repair it

The error above is a huge red flag because it means that one or more of our MyISAM-based tables are corrupted and we need to load data into it from scratch, or, as MySQL suggests, repair it, whichever option is more feasible.

Contrary to popular belief,  MyISAM tables can get corrupted very quickly and for a multitude of reasons including, but not limited to:

1. Failing hardware.
2. The MySQL daemon failing in the middle of any read or write operation.
3. Bugs in the MySQL code pertaining to MyISAM or its functionality.

Nowadays, even the best MySQL enthusiasts are not looking upon MyISAM favourably – since almost all of its features (full-text indexes, etc.) are now available in InnoDB as well, there’s no reason to use it as your primary storage engine for any reason.

Summary

Nowadays, many developers aim to get the best out of both storage engines. Indeed, many developers avoid MyISAM like the plague for the reasons listed above – however, for some, MyISAM’s ability to store data inside of separate files (such a capability can be achieved by enabling the innodb_file_per_table parameter) and its exclusive ability to display the row count inside of tables let some developers give the storage engine the benefit of the doubt regardless.

However, after taking everything into consideration, we suggest avoiding MyISAM – employ InnoDB or its Perconian counterpart – XtraDB – and you should do well!

After making adequate choices in regards to the storage engines available in MySQL, though, don’t forget that securing the infrastructure of your company as a whole is critically important too – make use of the capabilities provided by BreachDirectory’s API, make sure you and your team are safe by running a search through hundreds of data breaches, and we’ll see you in the next one!