MySql: MyISAM vs. Inno DB! [closed]

Question

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

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

NFS support

Unlike MyISAM, InnoDB may have problems on NFS.

From Configuring InnoDB (MySQL version 5.5)

Caution

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.