Lucky asked me the other day if there was any reason to use MyISAM instead of InnoDB for a MySQL database. Scum thought my response was useful, so I’m posting it in case it would be useful for anyone else:

From what I understand, there are still several advantages to MyISAM. If your database load is primarily read or primarily write activity, MyISAM is better. For mixed loads, InnoDB wins. Also, InnoDB comes with a lot of overhead of journaling and stuff to make it a “real” database. MyISAM is pretty simple. If you don’t need all those features, you might be better off with MyISAM.

In InnoDB’s favor:

Obviously if you need anything along the lines of transactions, foreign key constraints, or some general feeling of safety that your data will actually survive after something goes wrong, InnoDB is the answer.

MyISAM has no cache for data. Some or all of its index can get cached, but data retrieval always results in a disk hit. The kernel can help, but it’s still less than ideal. InnoDB uses the same structure to store the index and the data, so make your buffer pool size large enough, and the whole thing can be in RAM.

MyISAM tables get fragmented easily. They have a pretty boring mechanism of marking rows as deleted then filling them back in with new data, fragmenting as needed. It will stay that way until you do an OPTIMIZE TABLE. I don’t remember exactly how things work with InnoDB, but the problem isn’t nearly as bad, and you don’t need to do periodic table optimizations.

InnoDB has row-level locks, MyISAM has to lock the entire table whenever an update occurs. Obviously this is important for a database with mixed read/write activity because you can update part of a table while letting things continue on the rest of the table. There are several caveats to this rule. InnoDB locks have lots of subtleties and you’ll probably end up locking more of the table than you thought you were. Also, MyISAM does support a mode where a table can have INSERT and SELECT statements occurring concurrently, but you need to have very few UPDATE/DELETE statements for it to work.

That’s what I can think of off the top of my head. In general, I’d say go with InnoDB unless you have a good reason. It’ll give you a “real” database. If you don’t need a relational database (which I’m increasingly convinced is the case), use MongoDB or something. As always, the final answer depends on the characteristics of the particular application: benchmarks are your friend. Oh, and MySQL Performance Blog is an excellent resource for everything on the topic of MySQL tuning.