MySQL Forums
Forum List  »  InnoDB

Re: Performance: InnoDB or MyISAM?
Posted by: James Day
Date: January 31, 2005 07:35PM

There is no certain answer to your question. It depends on the queries you are making and the schema you are using. InnoDB has better crash recovery in my experience so if continuous availability and automatic recovery from crashes or power failures is a significant factor you might prefer InnoDB for that reason. It would be my first choice for any web site, for example, until I encountered a situation where MyISAM showed substantially better performance. If you'll be adding data in a time order but normally reporting in some other order, you might find the way InnoDB stores records physically with the primary key useful (a clustered index). Ignoring deletions, MyISAM would store the new records physically in the order in which they were added, though periodic ALTER TABLE ... ORDER BY statements can change that ordering for MyISAM (but not InnoDB).

MyISAM is likely to be smaller on disk, at least until the compressed engine for InnoDB is available. If you want fulltext search, you'll need MyISAM for those tables.

If none of those is vital to you, experiment. It's easy to use ALTER TABLE engine=InnoDB or engine=MyISAM.

Do yo have many concurrent inserts and selects? If yes, InnoDB is likely to be a better initial choice than MyISAM.

If you don't do much inserting but do lots of selects, MyISAM may well be the better choice, since it deosn't have any transaction overhead. The big gotcha for MyISAM with your data size may be the time it takes to check and fix tables after a crash. It's OK on small tables but when you get into the 50-200GB range that starts to be really slow compared to InnODB applying the transactions from its log.

Wikipedia, with perhaps 180GB of gzip compressed data, about 250-300GB uncompressed, uses InnoDB for almost all tables.

Options: ReplyQuote

Written By
January 31, 2005 12:49PM
Re: Performance: InnoDB or MyISAM?
January 31, 2005 07:35PM
February 01, 2005 07:59AM

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.