MyISAM vs InnoDB what's the difference and which one to use?

MyISAM and InnoDB are two most popular storage engines for MySQL database. Understand their differences helps you make the right choice. This post shows some important aspects of the two storage engines, the pros and cons to help you make the decision.

MyISAM is good for read heavy load and InnoDB is good at write heavy load

Why MyISAM is widely used even it has so little features? The reason is most data in applications are almost read only. Think about the blog application, the blog posts are written or updated very rarely, most of them never updated after first publishing, even they have updates occasionally it's way too less than read.

Sometimes, that's all you need, a place to store data and make a lot of read queries to it. MyISAM was designed exactly for that purpose.

For data that needs a lot of updates and writes, transactions, InnoDB is better. Not only it support concurrently write to rows to increase performance, it also keep the data integrity and consistent which MyISAM can't help you.

Table level locking vs row level locking

One of the most used arguments against MyISAM is it uses table level locking and InnoDB uses row level locking. In fact, not all operations in InnoDB are row level locking, it's true only when the query is a primary key or index based query.

MySQL InnoDB not row level locking for all queries.

If your data needs to write concurrently, InnoDB is better, because for such tasks, MyISAM only support sequential queries.

MyISAM don't support ACID

MyISAM is not a real relational database, write operations first write to cache then flushed to disk, you can lose data when power off before the flush. It don't guarantee data integrity.

InnobDB is durable, it can recover from power outage. Because it maintains log files.

MyISAM works well with defaults, InnoDB needs tunings

MyISAM has less features and simpler architecture, there aren't so many things you can or need to adjust it, it can work very well without maintain or tunings. The primary way to tune MyISAM is add appropriate indexes to table.

InnoDB is sophisticated, it supports all advanced features: clustered indexes, transactions, ACID, hot backup, row level locking, B+ tree indexes, etc. But the performance can vary aggressively between a fine tuned InnoDB table and a poorly tuned. Tuning InnoDB table can gives as much as 10-50 times performance gain. It's your responsibility to monitor and tune the performance of InnoDB table to keep it running with best performance, with great power comes great responsibility. Make sure you have all the necessary expertise and knowledge before using InnoDB.

Migration

For MyISAM it's easy, just copy the data and index files to another database. But it don't support hot backup, to copy the data, you must first offline the database.

InnoDB save multiple version of rows, you can visit database with a consistent view even the backup is in progress.