Take the 2-minute tour ×

What are the differences between MyISAM and Inno DB types in MySql?

share|improve this question
possible duplicate of MyISAM versus InnoDB –  Michel Feb 25 at 14:51
add comment

11 Answers

up vote 44 down vote accepted

The main difference is that InnoDB supports transactions while MyISAM does not.

There are numerous other differences, however the common one's i am aware of are:

  • MyISAM has typically been considered faster at searching, but recent InnoDB improvements are removing this difference and improving high concurrency workload performance
  • InnoDB support transactions whilst MyISAM does not
  • InnoDB handles indexes a bit differently, storing the primary key as part of every index (making indexes take up more room on the disk, but also making a covering index more likely)
  • MyISAM does table level locking while InnoDB can do row level locking
  • Different memory/buffer/index settings are used in the MySQL configuration files
  • InnoDB is typically said to have better crash recovery
  • As mentioned in another answer, the data is store on disk differently. I believe InnoDB is configurable in this area and can have one file per table etc. if required

I'm sure a google search or the MySQL site will bring up numerous other differences in more detail.

share|improve this answer
Recent improvements in InnoDB performance have eliminated MyISAM's advantages, except in some edge cases. –  Bill Karwin Apr 28 '09 at 22:45
@Bill - agreed - i have been following these improvements as well. I've updated my answer. –  Jarod Elliott Apr 29 '09 at 0:52
This discussion caught my attention while I'm revisiting transactionalessness at eBay: infoq.com/interviews/dan-pritchett-ebay-architecture. Particularly interesting is your comment: "Recent improvements in InnoDB performance have eliminated MyISAM's advantages." I want to bleed more scalability out of my web site, mostly as an exercise. MySQL is the long pole in the tent right now, so I'd like to see a side-by-side performance test, Hibernate+InnoDB (which is what I have right now), vs. SQL+MyIsam. May have to write my own. OTOH, table redesign may be the first order of bus. –  Don Branson Aug 3 '09 at 20:34
@Don - sorry i can't really help with the exact performance tests you are after, but for scaling improvement try taking a look specifically at MySQL 5.4 (in beta still), the InnoDB plugin and XtraDB from Percona. Many of the improvements for scaling on multiple processors have been included in these versions as well as other patches which can be applied. OurDelta also does MySQL builds with many of the patches included. Correct memory tuning is more critical with InnoDB compared to MyISAM and i've found the table design, indexing correctly and then reworking queries is very beneficial as well. –  Jarod Elliott Aug 3 '09 at 21:58
add comment

MyISAM supports (non-standard-SQL) fulltext indexing which InnoDB still does not. This is the only reason we ever use MyISAM today.

share|improve this answer
add comment

InnoDB and MyISAM

Features and Performance comparison:

  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  5. InnoDB has transactions while MyISAM does not.


Advantages of InnoDB


Disadvantages of InnoDB

  3. No full-text indexing.

Advantages of MyISAM

  3. Full-text indexing.

Disadvantages of MyISAM



Reference: Comparison InnoDB and MyISAM

You can also check it out here for further details: MyISAM Or InnoDB MySQL engine?

Hope this helps.

share|improve this answer
+1 That is a great Ocotober 2011 write-up / update. –  Michael Durrant Nov 28 '11 at 2:17
@MichaelDurrant: But the content still goes back to sep 2009 –  bragboy Jan 25 '12 at 9:40
add comment

The most important difference between MyISAM and InnoDB is that InnoDB supports transactions and foreign keys. If you need foreign keys and related functionality (for example automatically cascading deletes), you will need to use InnoDB.

InnoDB is slower than MyISAM for most uses, but can perform faster in certain conditions due to a better locking mechanism; MyISAM locks the whole table for reading while inserts/updates are executing. InnoDB can do row-level locking, thus allowing multiple concurrent writes and read on the table.

share|improve this answer
Innodb for the win ! mysqlperformanceblog.com/2007/01/08/… –  f00 Mar 26 '10 at 8:27
add comment

You can have more information about MyISAM & InnoDB in MySQL Documentation:



share|improve this answer
add comment

MyISAM and InnoDB also store their data on disk differently. MyISAM uses a data file and an index file for each table, stored in a directory named after the database. InnoDB seems to lump everything together in a file called ibdata1.

share|improve this answer
It is possible to force InnoDB to store data separately, even though it's not the default behavior. –  pilsetnieks Nov 10 '08 at 11:05
innodb_file_per_table dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html –  f00 Mar 26 '10 at 8:28
add comment

NFS support

Unlike MyISAM, InnoDB may have problems on NFS.

From Configuring InnoDB (MySQL version 5.5)


If reliability is a consideration for your data, do not configure InnoDB to use data files or log files on NFS volumes. Potential problems vary according to OS and version of NFS, and include such issues as lack of protection from conflicting writes, and limitations on maximum file sizes.

share|improve this answer
add comment

The major difference is that InnoDB supports transactions, whereas MyISAM doesn't.

share|improve this answer
add comment

Here is a description of differences between InnoDB and MyIsam:

Differences between InnoDB and MyIsam

Few differences:

  • MYISAM doesnt support any database transactions,
  • INNODB will provide transactions
  • MYISAM provides a table level locking,
  • INNODB provides a row level locking
  • INNOBD supports foreign keys, MYISAM does not...
share|improve this answer
add comment

MyISAM is more convienient when it comes to backup, since it's rather simple to just lock all tables and copy the files directly in the filesystem. (mysqlhotcopy which is a perl-script is even part of mysql afaik)

InnoDB is a little more complex and just copying the files won't do since they cannot be restored on another machine out-of-the-box.

However, there are commercial software that offers InnoDB hotcopying.

share|improve this answer
Actually, no. InnoDB is more convenient for backups because it can provide a consistent snapshot of a database without locking every table. mysqldump --single-transaction does this. MyISAM requires you to shut down the database when you're copying the files. –  Seun Osewa Aug 2 '10 at 7:32
When I said "more convenient" I ment that it is faster, both when dumping and restoring, since you can just copy the datafiles. You don't need to shut down the database, only lock the tables. If you want a consistent copy, then you only need to lock all tables before copying, however it is less intrusive to just lock the table that you are copying. But yes, a innoDB dump would allow the database to be written to during the operation, but a large table can take forever to dump, (several hours) in opposite to a MyISAM file copy which would finish in a matter of minutes. –  jishi Aug 2 '10 at 8:33
add comment

While transaction support is the major difference, table-level locking can be an issue if you have long-running SELECT queries mixed with UPDATE statements.

share|improve this answer
add comment

Your Answer


