Re: Comparison Betweeb MyISAM and INNO DB

[James Day]

MyISAM has been around longer. MyISAM tables are usually smaller (until compression is added to InnoDB). There are still some non-MySQL AB distributions which don't include InnoDB, making life unnecessarily painful for those who need transactions.

Whether InnoDB is faster or slower depends on the application. If you're exploiting the way InnoDB clusters records by primary key it can be far faster than MyISAM, which usually orders them by time added. There's some overhead for transaction handling and maintaining ACID guarantees in InnoDB, which MyISAM as a non-ACID engine doesn't have.

MyISAM features include smaller index and record sizes, so you can get greater cache efficiency. Can be very significant sometimes.

InnoDB is far more reliable in practice for large or heavily updated tables because it has far better crash recovery than MyISAM. Becomes particularly important for large tables which are frequently updated, where MyISAM index rebuilds can take hours or days while an InnoDB log apply can take seconds or minutes. For small tables with infrequent changes this matters less because the chance of an unflushed update is lower and the MyISAM repair time is lower.

You can mix and match, testing each engine type to see which mixture produces the best results. You might use MyISAM for a seldom-changed catalogue of goods and InnoDB for handling orders.

Personally, I tend to use InnoDB until I have a demonstrated reason to use a different engine. The greater reliability matters a lot to my particular situation, which features table sizes in the tens of gigabytes, frequent updates and a need for rapid crash recovery.