what are the difference between InnoDB and MyISAM. Which one I can go for? any areas where one provides and other doesnot provide? IS there any technological limitations in any of the both types? Please help me to choose the right type for my project?
Asked
Active
Viewed 1,354 times
4
-
Duplicate: http://stackoverflow.com/questions/277440/mysql-myisam-vs-inno-db – Taylor Leese Mar 11 '10 at 17:46
-
Another Duplicate: http://stackoverflow.com/questions/20148/myisam-versus-innodb – Taylor Leese Mar 11 '10 at 17:47
-
@Taylor, can you please tell me how to make sure that post should not be a duplicate? – gmhk Mar 11 '10 at 17:51
-
Well, I typed in "innodb myisam" into the search and found those at the top. I think that should suffice. – Taylor Leese Mar 11 '10 at 17:52
3 Answers
5
There are several differences, but the most important I can think about are :
- InnoDB is a transactionnal engine -- i.e. it supports transactions
- InnoDB supports foreign keys
- MyISAM only does lock at the table level -- InnoDB does locks at the row level
The wikipedia entry on InnoDB lists a couple if differences -- some of which I didn't spoke about ;-)
And you could be interested by the Chapter 13. Storage Engines section of the MySQL manual, which is probably not a bad reference, when it comes to InnoDB and MyISAM.
Especially, I'd say :
Pascal MARTIN
- 395,085
- 80
- 655
- 663
-
@pascal do you mean to Say InnoDB provides the lock access on the table and row access, so that means when one user is performing an insert or update operation, I can prevent accessing that table. – gmhk Mar 11 '10 at 17:50
-
@hagrim : from what I remember, with MyISAM, when someone performs some write, it locks the whole table *(preventing any other write)* -- with InnoDB, in at least some cases, it should lock only the affected rows *(allowing other people to write to some other, non-locked, rows)* – Pascal MARTIN Mar 11 '10 at 17:52
1
The MySQL Docs provide a nice run-down and use cases. The storage engine you choose will depend on what you are using it for.
The largest difference is that innodb supports ACID compliant transactions and row level locking.
jasonbar
- 13,333
- 4
- 38
- 46
1
Another important difference: so far, MyISAM supports fulltext search, but InnoDB does not. So if you want to have a search index effective over text columns, you have to use MyISAM or hack around.
Adriano Varoli Piazza
- 7,297
- 5
- 39
- 50