Difference Between InnoDB and MyISAM

  • The big difference between MySQL Table Type MyISAM and InnoDB is that InnoDB supports transaction
  • InnoDB supports some newer features: Transactions, row-level locking, foreign keys
  • InnoDB is for high volume, high performance

Most people use MyISAM if they need speed and InnoDB for data integrity. You can use more than one or any combination of these table types in your database. Remember to asses the needs of your application before building it. Even though MyISAM is faster than InnoDB in the MySQL world, InnoDB is fast compared to any database engine.With InnoDB you get transactions, speed and integrity three features not usually used in the same sentence.

InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Fully integrated with MySQL Server, the InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be of any size even on operating systems where file size is limited to 2GB.

So, what do you think about those engines?? Please feel free to discuss it.

For more information visit http://dev.mysql.com/

Thanks

35 Comments »

  1. totally bingo

    totally bingo

  2. sripathi said

    i need more information about those structures and transactions, like which best for tractions…..

  3. senthil said

    Since we have higher performance with added features in Innodb than myISAM .why should we have it seperated ? rather than giving Innodb as updated version of myISAM . Any how people going to use Innodb only. why does myISAM still exist?

  4. [...] reading Tapos Blog post about the differences between InnoDB and MyISAM database engines i found out that InnoDB does not [...]

  5. MyISAM databases support the MATCH and AGAINST keywords for searching, very useful for that purpose, but the InnoDB offers better speed and space consumption.

  6. Sitthykun said

    can you tell me, what size of the maximum storage of MySQL?
    both innodb and myisam.

    cheer!

  7. Joey said

    My experience with MyISAM and InnoDB, is that MyISAM is optimal for big nasty logs and/or if you need to do a relevancy-relied full text search (as another author stated, with the help of MATCH and AGAINST). This compared to InnoDB that is optimal when it comes to handling and storing integers, using transaction-critical applications, etc.

    Oh, and a protip to all developers: when logging things, make it log things efficient; don’t make the table bigger than it needs to be. Start by using INET_ATON to store IP-adresses as 32-bit integers instead of 16 digit char. Always mind the scale of volumes for those poor DBA’s sake ;-)
    Also, check out the facebook-patch for InnoDB – they’ve really made some cool optimizations worth checking out.

    cheers
    /Joey

  8. author said

    comment

  9. a said

    a a a

  10. Showket said

    This article is really help me to understanding InnoDB n MyISAM. Thanks

  11. Thanks for the same , this really helped me

  12. Atish Mahadik said

    Thanks for the same, i want to join this blog to share & communicate solve problems

  13. Lawrence said

    I think MyIsam is for small business while InnoDB is more for commercial use because of its data integrity. Overall I think one would make a decision based on how they feel about the data they are trying to store within the db. Just my thoughts… Open to adverse comments. Thanks Lawrence Owens

  14. Ankit said

    I am trying to fetch data from the database and trying to generate excel sheet but it takes more than 15 mints. Now in which way MyISAM and InnoDB can be useful. Please reply ASAP.

  15. Syntax3rror said

    Thanks.

  16. hey brothers!!!
    I am a newbie…. please tell me what is the difference mySql free version and paid version…???
    1) What more facility I’ll get if I use paid version…
    2) What is the limitation of free version…
    3) I am building a software that will store billions of data in the database… in this case if use free version what will be the possible problem in the long run…

    please brother… if possible email me:>> tanvir_mailbox@yahoo.com

  17. Rahul said

    Thanks for this post. I got your link by searching in google for “InnoDB vs. MyISAM”.

    This is helpful, I have a clear understanding about these two.

  18. I appreciate this article, the way how you have sumed up everything into such simple words, now I can go full confidence in my tomorrow’s database viva. Thank you very much sir. I do have a question sir, can you please refer a book to me where i can study database working in depth at physical, logical level. Thanks once again.

  19. great post. I was little confused about the difference of innodb and MYISAM and now it is clear.
    Thanks/

  20. Very useful post. I really enjoyed reading your blog. This post clear me about all the difference between MYSIAM and INNODB. Thanks

  21. Useful post… your writing-style is pretty much helpful.

  22. Tom Watson said

    Very useful post. I really appreciate your blog.
    Thanks

  23. Bo soon PArk said

    Thanks for your post that give me some clues for my system.

  24. [...] Difference Between InnoDB and MyISAM [...]

  25. sam said

    Can i get a proper distinguish between Innodb and myisam pointwise?

  26. I read this post fully on the topic of the resemblance of most up-to-date
    and earlier technologies, it’s awesome article.

  27. Hey there! I’ve been following your website for a long time now and finally got the courage to go ahead and give you a shout out from Dallas Texas! Just wanted to mention keep up the fantastic job!

  28. Hurrah! After all I got a web site from where I
    know how to genuinely take valuable information concerning
    my study and knowledge.

  29. We stumbled over here coming from a different web address and thought I may as well check things out.
    I like what I see so now i’m following you. Look forward to looking at your web page repeatedly.

  30. I do not know whether it’s just me or if everybody else encountering issues with your blog. It appears as if some of the text in your content are running off the screen. Can somebody else please comment and let me know if this is happening to them as well? This might be a problem with my internet browser because I’ve had this happen previously.
    Thanks

  31. I used to be recommended this web site by my cousin. I am no longer certain whether or
    not this publish is written by way of him as no one else recognise such specified approximately my trouble.
    You are incredible! Thanks!

  32. These are truly great ideas in concerning blogging. You have touched some fastidious factors here.
    Any way keep up wrinting.

  33. Sammy said

    I know this if off topic but I’m looking into starting my own blog and was wondering what all is required to get set up? I’m assuming having a blog like yours would
    cost a pretty penny? I’m not very internet smart so I’m not
    100% positive. Any tips or advice would be greatly appreciated.
    Thank you

  34. Good info. Lucky me I discovered your site by chance (stumbleupon).
    I’ve saved it for later!

  35. venkat said

    nice information.thank’s

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: