Mysql what is the difference between myisam and innodb

JamesENL 4 4 bronze badges. Another major difference not as yet mentioned is how caching for each storage engine is done. Possible scenarios are endless!!! Community Bot 1. Those are bad formulas! I'll add an "answer". Rolando's formulas for cache sizes are not practical. Here's my rundown on what to set them to: mysql. Rick : The powers of 2 were meant to display the answers in different units. Rick : You know what? I'll give credit where credit is due. Show 5 more comments.

Morgan Tocker 3, 17 17 silver badges 24 24 bronze badges. I read your answer and compared it with the others already here. Yours is the only one to mention BLOBs. They are usually taken for granted. Yours is also the only one mentioning myisampack, one of the unsung heroes of fast-readable MyISAM tables. Example would be a compressed read-only table where you have infrequent updates to it by fully replacing the table.

Referential Integrity Referential integrity ensures that relationships between tables remains consistent. Conclusion In conclusion, InnoDB should be your default storage engine of choice. Thanks, really informative and clear summary. Gaius Gaius 11k 3 3 gold badges 28 28 silver badges 63 63 bronze badges.

Other important differences include: InnoDB support transactions and foreign keys. MyISAM does not. MyISAM uses full text indexing. MyISAM does a poor job of enforcing data integrity.

InnoDB is good for both read- and write-heavy loads. Patrick Patrick 4, 7 7 gold badges 25 25 silver badges 26 26 bronze badges. And I would disagree with the generalization that InnoDB in general just gets a 'performance hit'.

Anto Anto 3 3 gold badges 7 7 silver badges 14 14 bronze badges. Includes MySQL 5. Multi-versioning is used to isolate transactions from one another. InnoDB supports foreign keys and referential integrity, including cascaded deletes and updates. Pruning the InnoDB table cache: To ease the memory load on systems with huge numbers of tables, InnoDB now frees up the memory associated with an opened table.

An LRU algorithm selects tables that have gone the longest without being accessed. These indexes are physically represented as entire InnoDB tables. Mahesh Patil Mahesh Patil 2, 13 13 silver badges 22 22 bronze badges.

The Overflow Blog. Does ES6 make JavaScript frameworks obsolete? Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta. Now live: A fully responsive profile. You must be logged in to post a comment. September 11, Ateeq Rafeeq Webful Creations. How useful was this post? Click on a star to rate it!

We are sorry that this post was not useful for you! Let us improve this post! HoldOffHunger 13k 8 8 gold badges 63 63 silver badges bronze badges. InnoDB has a big disadvantage: it doesn't remember auto increment values it resets them to the highest ids in the tables when MySQL restarts. So if you use InnoDB and you need unique ids, you need to implement a workaround e.

This difference could be considered a disadvantage; perhaps some would consider it a "big disadvantage". As far as a consistency of resultsets within a transaction, that depends on the "isolation level".

Add a comment. FRM, tablename. MYD, tablename. You can commit and rollback with InnoDB. InnoDB support of Fulltext indexes was introduced in v5. InnoDB designed for maximum performance when processing high volume of data? What is diskspace vs table space? You can use InnoDB for that as well. Could you please provide arguments for your statements?

Show 1 more comment. The Overflow Blog. Does ES6 make JavaScript frameworks obsolete? Podcast Do polyglots have an edge when it comes to mastering programming Featured on Meta.