What is the difference between InnoDB and MyISAM

Question

what is the difference between InnoDB and MyISAM. What can I do? Any areas where others are provided do not? Are there any technological limitations in either of the two types? Please help me choose the right type for my project?

Answer 1

There are a few differences, but the most important thing I can think of:

• InnoDB is a transactional engine - i.e. it supports transactions
• InnoDB supports foreign keys
• MyISAM is only locked at the table level - InnoDB is locked at the row level

the Wikipedia entry on InnoDB contains a couple, if the differences are - some of which I didn't say -)

And you might be interested in the section in Chapter 13. Storage Engines in the MySQL manual, which is probably not a bad reference when it comes to InnoDB and MyISAM. Especially, I would say:

• 13.5. Myisam storage engine
• 13.6. InnoDB Storage Engine
• and 13.1. Comparison of Transactional and Non-Transition Engines

Answer 2

"MySQL docs" provide a nice crash and usage. The storage engine you choose will depend on what you use it for.

The biggest difference is that innodb supports ACID compatible transactions and row level locking.

Answer 3

Another important difference: MyISAM still supports full-text search, but InnoDB does not. Therefore, if you want the search index to work on text columns, you must use MyISAM or hack.