132

MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.

InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance

above differences is correct between MyISAM and InnobDB? please guide if any other limitations are there for MYISAM and InnobDB. when should i use MyiSAM or when Innodb? Thank you!

Licson
  • 2,173
  • 16
  • 25
ajay
  • 1,490
  • 2
  • 12
  • 16

2 Answers2

234

Read about Storage Engines.

MyISAM:

The MyISAM storage engine in MySQL.

  • Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  • Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources. -- Mostly no longer true.
  • Full-text indexing. -- InnoDB has it now
  • Especially good for read-intensive (select) tables. -- Mostly no longer true.
  • Disk footprint is 2x-3x less than InnoDB's. -- As of Version 5.7, this is perhaps the only real advantage of MyISAM.

InnoDB:

The InnoDB storage engine in MySQL.

  • Support for transactions (giving you support for the ACID property).
  • Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
  • Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
  • InnoDB is more resistant to table corruption than MyISAM.
  • Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
  • MyISAM is stagnant; all future enhancements will be in InnoDB. This was made abundantly clear with the roll out of Version 8.0.

MyISAM Limitations:

  • No foreign keys and cascading deletes/updates
  • No transactional integrity (ACID compliance)
  • No rollback abilities
  • 4,284,867,296 row limit (2^32) -- This is old default. The configurable limit (for many versions) has been 2**56 bytes.
  • Maximum of 64 indexes per table

InnoDB Limitations:

  • No full text indexing (Below-5.6 mysql version)
  • Cannot be compressed for fast, read-only (5.5.14 introduced ROW_FORMAT=COMPRESSED)
  • You cannot repair an InnoDB table

For brief understanding read below links:

  1. MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons
  2. MySQL Engines: MyISAM vs. InnoDB
  3. What are the main differences between InnoDB and MyISAM?
  4. MyISAM versus InnoDB
  5. What's the difference between MyISAM and InnoDB?
  6. MySql: MyISAM vs. Inno DB!
Mr.Web
  • 5,891
  • 8
  • 37
  • 75
Tony Stark
  • 7,818
  • 8
  • 41
  • 63
  • 13
    Full-Text indexing is available in InnoDB in MySQL 5.6 – Argeman Mar 28 '13 at 09:51
  • You didn't list the benefits for MyISAM. – Perception Mar 28 '13 at 10:02
  • 1
    @Perception add MyISAM dscription & all in below links also. – Tony Stark Mar 28 '13 at 10:14
  • For converting from MyISAM to InnoDB, see [myisam2innodb blog](http://mysql.rjweb.org/doc.php/myisam2innodb). – Rick James Jun 05 '15 at 04:45
  • InnoDB also have a limitation of having maximum of 64 indexes per table in MySQL 5.7. – VCD Jul 06 '17 at 06:17
  • InnoDB rocks nowadays. https://www.mysql.com/why-mysql/presentations/myisam-2-innodb-why-and-how/ – Ajmal Praveen Jan 14 '18 at 11:47
  • it's actually very true. Even with optimized InnoDB, the insert into InnoDB takes 30-60 seconds and in MyISAM, it takes 1 second. I'm talking about foreach insert from a lot of XML data.... InnoDB is great for redundancy but for speed, MyISAM is the winner. – Luka May 05 '18 at 14:32
19

Use MyISAM for very unimportant data or if you really need those minimal performance advantages. The read performance is not better in every case for MyISAM.

I would personally never use MyISAM at all anymore. Choose InnoDB and throw a bit more hardware if you need more performance. Another idea is to look at database systems with more features like PostgreSQL if applicable.

EDIT: For the read-performance, this link shows that innoDB often is actually not slower than MyISAM: https://www.percona.com/blog/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

MikeB
  • 1,214
  • 10
  • 23
Argeman
  • 1,334
  • 8
  • 21
  • 7
    "Use MyISAM for very unimportant data" is overstating the reliability difference, especially when using a modern journaling filesystem. And the more common opinion is generally that MyISAM is faster for reads. I can't figure out why the Percona blog's results were an exception. We did experiments on our tables with both engines and found MyISAM reads were typically almost twice as fast as InnoDB. It's always best to test with your own real data and environment when possible. – orrd Jul 27 '16 at 19:56
  • @orrd You are correct, only tests with the own data can show how the performance impact will be. My experiments showed near to no difference at all in most read-only cases, with myisam tending to suffer real problems when writes occur regularly, so in all usecases that I had so far innodb was much better – Argeman Aug 02 '16 at 13:28
  • 6
    The link to to blog entry is now about 10 years old. – robsch Sep 04 '17 at 12:26
  • not just twice as fast, in our test we reached 80x speed up in lookups, sad that its not being improved for write intensive applications. – Jones G Feb 21 '18 at 21:28