MySQL Forums
Forum List  »  InnoDB

Re: Comparison Betweeb MyISAM and INNO DB
Posted by: James Day
Date: July 17, 2005 02:28AM

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.

Options: ReplyQuote

Written By
Re: Comparison Betweeb MyISAM and INNO DB
July 17, 2005 02:28AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.