MySql: MyISAM vs. Inno DB!

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.

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

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.

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

http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html

http://dev.mysql.com/doc/refman/5.1/en/innodb-overview.html

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

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.

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...

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.