MySql: MyISAM vs. Inno DB!

mysql database innodb myisam
Arron S picture Arron S · Nov 10, 2008 · Viewed 17.4k times · Source

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

Answer

Jarod Elliott picture Jarod Elliott · Nov 10, 2008

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 supports referential integrity 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.

Related questions

MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. …

Read More
What's the difference between MyISAM and InnoDB?

I understand that this question has been asked before, but most of the time it is asked in relation to a specific database or table. I cannot find an answer on this site that describes the two engines and their …

Read More
Changing Table Engine in MySQL

I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. …

Read More