7

Possible Duplicate:
Should I always prefer MySQL InnoDB over MyISAM?

Under what conditions should InnoDB be used over MyISAM ? I've seen some MySQL optimization tests where MyISAM proves to be faster. All of my tables are MyISAM, so why should I switch to InnoDB?

Community
  • 1
  • 1
Ben
  • 60,438
  • 111
  • 314
  • 488
  • 2
    possible duplicate of [Should I always prefer MySQL InnoDB over MyISAM?](http://stackoverflow.com/questions/1970160/should-i-always-prefer-mysql-innodb-over-myisam) and [MyISAM versus InnoDB](http://stackoverflow.com/questions/20148/myisam-versus-innodb) and [MySQL: MyISAM vs Inno DB](http://stackoverflow.com/questions/277440/mysql-myisam-vs-inno-db) and... _zzzzzzzzzzzzzz_ (**See also:** [what is the difference between INNODB and MYISAM](http://serverfault.com/questions/54897/what-is-the-difference-between-innodb-and-myisam)... – Matt Ball Nov 22 '10 at 15:19
  • ... and [How do you choose a MySQL database engine](http://serverfault.com/questions/219/how-do-you-choose-a-mysql-database-engine)) – Matt Ball Nov 22 '10 at 15:24
  • 8
    MySIAM is deprecated. You should use MyTHAILAND instead. – JeremyP Nov 22 '10 at 15:59
  • @Matt - some of those posts are a year old, things have changed, maybe the answers have too. – Ben Nov 22 '10 at 16:35
  • InnoDB and MyISAM are not young/in-flux technologies. The revision history for the [InnoDB Wikipedia article](http://en.wikipedia.org/wiki/InnoDB) (which the answer you accepted appears to summarize) confirms this; things really haven't changed much within the last year. – Matt Ball Nov 22 '10 at 17:12

4 Answers4

14

Pros & Cons of InnoDB & MyISAM -

  1. InnoDB recovers from a crash or other unexpected shutdown by replaying its logs.
  2. InnoDB can be run in a mode where it has lower reliability but in some cases higher performance.
  3. InnoDB automatically groups together multiple concurrent inserts and flushes them to disk at the same time.
  4. InnoDB flushes the transaction log after each transaction, greatly improving reliability.
  5. Unlike InnoDB, MyISAM has built-in full-text search
  6. MyISAM is still widely used in web applications as it has traditionally been perceived as faster than InnoDB in situations where most DB access is reads.
  7. While writing/updating data into a InnoDB table, only that particular row is locked, whereas in MyISAM the entire table is locked.
  8. InnoDB provides Full Transaction support.
Srikar Appalaraju
  • 71,928
  • 54
  • 216
  • 264
6

How about transactions and concurrent read/writes? Crash recovery is better as well. There are some features that are only available using MyISAM, like full text search, but unless you need these, I would go with InnoDB.

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
4

The creator of InnoDB - Heikki Tuuri, use th following email signature.

InnoDB - transactions, row level locking, and foreign keys for MySQL

tszming
  • 2,084
  • 12
  • 15
3

Every update operation exclusivly locks table. And You can't select from this table at the same time with update.

CyberDem0n
  • 14,545
  • 1
  • 34
  • 24