I was doing some research last night and ran into this, thought it might be useful for some of you folks:

I’ve had a request on a detailed article about when one should opt for MyISAM or InnoDB when using MySQL. First off, there’s a few deal-breakers that need to be discussed:

MyISAM Limitations

* No foreign keys and cascading deletes/updates
* No transactional integrity (ACID compliance)
* No rollback abilities
* Row limit of 4,284,867,296 rows (232)
* Maximum of 64 indexes per row

InnoDB Limitations

* No full text indexing
* Cannot be compressed for fast, read-only

If none of those deal-breakers apply, then you can keep reviewing the pros and cons. Here’s some things to keep in mind:

Read to write ratio
My rule of thumb is to calculate the ratio of queries that read data to those that write data. If you find that the amount of writes climbs over 10-15%, you should consider InnoDB, but if it climbs over 20%, you will get a big performance gain by moving to InnoDB.

This all stems from how the storage engines handle locking. MyISAM uses table-level locking. That means that if you run a query against the table, the whole table will be locked. The only exception is that you can insert a row if there is an active read lock on the table, and the table is not fragmented (called concurrent insertion). So if a user is running a really slow select on a table, your updates or deletes will have to wait until the select is completely finished. This delay can reduce the overall efficiency of your application drastically if your MySQL server is busy and over 20% of your queries are writes.

InnoDB uses a row-level locking scheme, which means that waiting on locks is greatly reduced. When MySQL wants to lock something for a query in an InnoDB table, only the row that needs to be accessed is actually locked. For example, if you run a select that examines rows 5-20 in a table, but another user wants to write to row 24 at the same time, both queries will be executed simultaneously. Of course, transactions will change this behavior a bit, but I’ll leave that out for simplicity’s sake.

Available hardware
InnoDB requires additional hardware in order to function as efficiently as MyISAM. This means that you will need additional CPU power and RAM to get good performance out of InnoDB tables. MyISAM tables can also be compressed with myisampack so that they can handle queries quickly (at the expense of being read-only).

Table growth
MyISAM (by default) is limited to 232 rows, and this can be a pain when you reach the limit. You can increase it manually when you reach the limit, but this causes the row pointer to become gigantic, and this reduces performance. InnoDB tables have no limits, and they can grow over 2GB even on systems that don’t allow files to grow over 2GB. InnoDB tables can also use raw partitions to store data, and this ends up being quite fast relative to simple ibdata files.

Isolation and integrity
When it comes to transactional integrity, InnoDB holds all the cards. You can adjust your isolation levels so that you see the exact data you want, even when data is changing from other transactions when your transaction runs. Transactions can have save points to which you can roll back if something does not work out. Also, if you have the need to run several queries at once that need to run uninterrupted, transactions are required for this functionality.


Source: http://rackerhacker.com/2007/11/06/w...sam-or-innodb/