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/

Some advanced stuff there, but I think I managed to get my head around it all. Thanks for the post, really informative and something I should consider more when designing database structures.

__________________
Any fool can write code that a computer can understand. Good programmers write code that humans can understand.

I think the key thing should be noted is that large website with a ton of writes, will need an INNODB table for writing, no doubt in that. However, a website that has 100% reads, but writing done in the background, it is best to have an INNODB table for the backend (writing) that is copied to the front end which is a MYISAM table.

Copying to the front end can be done like:
INSERT INTO table SELECT * FROM write_table

__________________
Halo 3 Cheats

Additionally, MyISAM only supports an index length of 500 characters per record.

This means that (assuming) if you have three cols as varchar(255) and you index all three fields, then the result will be an index of 255*3 = 765 (works well with InnoDB), but with MyISAM this would give you an error stating that the key length per record cannot exceed 500.

The solution to which is that you will have to create a partial composite index like alter table add index <indexname>(col1(100), col2(100), col3(100))

The 100 above is the length of each col that should be indexed.

Let me know if someone needs more info on this.

__________________
Sunil Bhatia www.twitter.com/sunilbhatia79 - Follow me on Twitter
PHP5 Tutorials
Career Articles

When should the written data be moved into the active reading table? Should written data be removed from the write table after a certain period of time?

I think what Bluesaga is getting is that when you lock a table, you prevent the opposite to what you're doing from happening. So you cannot INSERT and SELECT. So if you use 2 tables for the 2 different purposes, and then move the data from the slave to the active database periodically, then at least the data is safe for if the power fails on one of the databases servers. Although I'm guessing there are other reasons, too.

__________________
The man who comes back through the Door in the Wall will never be quite the same as the man who went out.

Its for redundancy as you guessed but it is also for speed reasons, a database that is being heavily written to will be much slower than a database with heavy reads. And thus if you mix and match the two then the database will be slower, heavy locks are incurred with some select functions (count(*)) on innodb for instance....

Also, innodb is awesome for writing, but it doesn't allow the benefits that myisam does for reading (fulltext search etc). So it is more beneficial to use innodb as a slave as Adam suggests, and doing an INSERT INTO table SELECT * FROM write_table in order to get an up to date read table!

__________________
Halo 3 Cheats

I agree, in my experience with large databases (100+ read/write tables) we'd have double that size in just cached data on MyISAM tables which we ran all of our searches on. yes, the site still gets lots of traffic (i no longer work there), and thousands of searches a day, and worked great off of the caches. (There was load balancing.)

So, my suggestion, is use InnoDB if you have a lot of writes, or want the FK checks, but for the most part, MyISAM is the default recommended table type for a reason. It works as a solution for most of the common problems you'll run into.

fantastic contribution webosb.

That sounds rather complex to me! But I think I understand. You're saying reduce each index to 100 so that you can effectively have 5 indexes of 100 in length?

__________________
The man who comes back through the Door in the Wall will never be quite the same as the man who went out.