Friday, August 19, 2011

InnoDB VS MyISAM

MyISAM offers speed whereas Innodb offers reliability. 
MyISAM is fast but with no transactions support, Innodb has certain, but very important limitations.

it's said that InnoDB works faster because it uses row locking instead of table locking, like MyISAM. However, if there are mainly SELECT statements, a MyISAM table might be faster.

If you need foreign key constraints or transactions, you can only use InnoDB, wheras if you need fulltext indexes, you can only use MyISAM tables at the moment.

With replication it's even possible to take advantage of both storage engines on one table. For example, the master could store a table as InnoDB which makes it fast for INSERTs, UPDATEs and DELETEs while the slave(s) could store the same table as MyISAM and offer the best performance for SELECTs.

If you have a lot of cncurrent inserts/updates I would recommend inndb since it supports row-locking.