Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using MYSQL as my database for the current web project. And i am a newbie to MYSQL. Could any one explain me please what is the difference between INNODB and MYISAM.

I hope the answers will help me a lot.

Thanks in Advance..

Fero

share|improve this question
    
feel free to add a MySQL tagg as well :) –  Johan Aug 17 '09 at 13:29
    
Doesn't this belong on stackoverflow? –  ripper234 Aug 19 '09 at 12:08
add comment

4 Answers

up vote 13 down vote accepted

ISAM = Indexed Sequential Access Method and is essentially a flat file (for those DBAs who can remember, think Btrieve, or B-Tree). It's a very old technology - but don't let that put you off using it. Because it's a flat file (more on that later), it is not relational, and thus is not an RDBMS, and thus is more appropriate in some situations.

InnoDB is the full RDBMS like you are most likely familiar with. MyISAM can appear to be relational through another layer added on top that maintains your links, logic and referential integrity.

ISAM is brilliant if you have a LOT of records (like, 20 million), and the records are mostly stand-alone (i.e. you don't need to do lots of links to retrieve associated data). It relies VERY heavilly on indexes and if you don't have the right index, be prepared for very very long query times. Case in point: We had a Btrieve ISAM table with 20M+ records and to do a retrieve and filter data based on an accurate index was almost instant. Using the wrong index was literally 15 minutes.

InnoDB is great for if you have a lot of relational links. Table A references a field in Table B, which references Table C and D. InnoDB can fetch these records using all sorts of nice joining methods (hash joins, etc), whereas an ISAM database would have to run multiple sub-queries for every single row and match the records manually.

You will really have to do a course in databases if you want much more detail than that!

share|improve this answer
    
ISAM is just as relational as anything else, it's just not optimised for it. –  LapTop006 Aug 17 '09 at 12:17
    
thanks 4 your brief description. Now basically i am cleared –  user16614 Aug 18 '09 at 11:15
add comment

The most fundamental is that InnoDB is transactional. MyIsam isn't. MyIsam is generally a bit faster, so if you don't need transactions that is generally your best bet. For detailed descriptions you should read the MySQL documentation.

share|improve this answer
add comment

These days unless you're using a table for log style data (many more INSERT's then SELECT's, no transactions) InnoDB is usually faster, more reliable, has more features, etc.

The only other feature MyISAM has is the fulltext search which is fine for basic use, but most people use some from of Lucene for anything serious.

Either way you must tune MySQL as the defaults are essentially set for a 32MB pentium that's shared with other services.

share|improve this answer
add comment

Generally, the rule of thumb is, if you want speed, use MyISAM and if you want stability, use InnoDB. Something to do with atomicity if I remember correctly.

share|improve this answer
3  
Excecpt that's no longer true, InnoDB is actually faster in most cases. –  LapTop006 Aug 17 '09 at 12:16
1  
can someone add a link some benchmarks or something...? –  Johan Aug 17 '09 at 13:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.