MyISAM has historically been MySQL’s default storage engine, and plenty of setups never bother to reconsider it. Choosing a storage engine is trickier than it looks, but it is worth examining—especially for the two most common options, MyISAM and InnoDB.
A useful starting point is a set of practical questions:
- Does your database use foreign keys?
- Do you need transaction support?
- Do you need full-text indexing?
- What kinds of queries dominate your workload?
- How large is your data?
Thinking through those questions can point you in the right direction, though it will not settle every case. If you need transactions or foreign keys, InnoDB is usually the better fit. If you need full-text indexing, MyISAM often looks like the straightforward choice because support is built in. In practice, however, most projects are not testing full-text search against two million rows. Even if the result is slower, Sphinx can be used to provide full-text indexing on top of InnoDB.
Data size is a major factor. Large datasets tend to favor InnoDB because it supports transactions and crash recovery. The size of the database also affects how long recovery takes. InnoDB can use its transaction log to restore data, which is generally much faster; MyISAM might need hours or even days for a recovery that InnoDB can complete in minutes.
Your habits when working with tables can also have a large effect on performance. For example, COUNT() is very fast on MyISAM tables and can be painful on InnoDB. Primary-key lookups are extremely fast with InnoDB, but long primary keys can create performance problems. Bulk inserts tend to be faster on MyISAM, while updates are faster on InnoDB—especially when concurrency is high.
So which engine should you use? As a rule of thumb, MyISAM may be a good fit for smaller applications or projects. Large environments can certainly succeed with MyISAM, but that is not always the case. If you are planning a project with very large data volumes and you need transaction processing or foreign-key support, you should go straight to InnoDB. Keep in mind that InnoDB tables require more memory and storage, and converting a 100GB MyISAM table to InnoDB can be a very unpleasant experience.
