MyISAM or InnoDB MySQL engine?

If you’re curious about the difference of MySQL engines, here they are.

Comparing and contrasting what MyISAM is and InnoDB. First ISAM is the acronym for Indexed Sequential Access Method, generally it is fast, compressable, and allows full text searching. Second InnoDB is transaction safe and includes automatic foreign key checking so it’s a bit slower.

The important part is that don’t spend too much time deciding which one to choose, because you can change table type later with the ALTER TABLE statement. Instead you should focus on optimizing tables for performance such as using fixed with fields versus variable width (varchar). Now for the technical details.

MyISAM

MyISAM tables are optimized for flexibility and compression. They can hold upto 256TB of data but are not transactional-safe, meaning if the power goes out in a middle of a write, the data is gone. This is especially important for financial data. The schema is flexible so it is good for beginners learning about databases. Foreign key relationships do not have to be setup for the database to function, however this can cause poor performance and unreliable data. The simplicity allows lower use of hardware resources and another benefit is the table search ability for full-text.

InnoDB

InnoDB includes automatic foreign key support also a constraint, is better for reliability therefore it passes the ACID test, and supports up to 64TB of data. It is stated that InnoDB is slower than MyISAM because the foreign key checking has operational overhead. However, there’s a benchmark performed by Vadim Tkachenko that shows similar speeds. I consider it inconclusive considering that Vadim only performed READ queries. The ACID test is an acronym for the following:

Atomicity – a transaction should be either completely executed or not at all.
Consistency – transactions should always leave the database in a valid state meaning the data written should be valid.
Isolation – if there are concurrent transactions isolation is ensuring each transaction is separate as if executed serially.
Durability – once a transaction is committed or written it should remain stored in any situation.

It is considered transaction safe because a query or multiple queries are not automatically executed. They have to be committed and can be rolled back. The integrity of data is higher because InnoDB is strict with field properties while MyISAM can be loose, therefore schemas should be planned carefully.

References:
http://www.mysqltutorial.org/understand-mysql-table-types-innodb-myisam.aspx

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons