cftopper.com

For those who don't know, InnoDB and MyISAM are the 2 main data storage engines available in MySQL. In our literally hundreds of database-driven websites and projects at Digital Crew, we use both the InnoDB and MyISAM storage engines, often together on the individual tables within a project.

So which engine should we use for a given table? Is it OK to mix and match the 2 different table types within a project?

I researched this issue a year ago and found a myriad of different opinions with no conclusive advice but I did so again this morning and here is the outcome as sent in an email to the rest of the Digital Crew team.

Guys, I was concerned that we shouldn't mix InnoDB and MyISAM table types within a database but after searching nobody seems to say that is an issue.

It boils down to this, we can mix and match MyISAM and InnoDB tables as we please but should consider which the storage engine as follows:

Use MyISAM when:

1. Speed is important - ( "MyISAM is slightly faster" )
2. The data isn't too critical ( "unreliable and slow, related to table size, table repair process" ) 
3. You need Full-Text search support - (InnoDB doesn't support full text search, that said MyISAM is meant to be none-too-great at it with large data sets either - use Lucene when data set gets massive)

Use InnoDB for tables when:

1. Referential integrity must be enforced ("if you need foreign key constraints or transactions, you can only use InnoDB" )
2. The table gets a lot of writes ( "If you have a lot of concurrent inserts/updates I would recommend InnoDB since it supports row-locking." )
3. The table will be big (100Mb+ - "For reliability and performance, we use InnoDB for almost everything at Wikipedia - we just can't afford the downtime implied by MyISAM use and check table for 400GB of data when we get a crash." )
4. The table is critical ( "InnoDB is safer" )

So if we are going to mix and match storage engines, use the advice above to help choose the correct type. On the other hand, James Day, a MySQL Support Engineer and Wikipedia engineer recommends that people use InnoDB all the time unless for some reason if becomes apparent that you need MyISAM:

I'd go with InnoDB until it's been proved that it's unsuitable. The first reason is reliability. Get a crash with MyISAM and you have the unreliable and slow, related to table size, table repair process. Same thing with InnoDB and you instead get the fixed time, fast and reliable log apply/rollback process. As the data set gets bigger, this matters more and more, as it does if you want to do things like sleep instead of being woken up in the middle of the night to fix a crashed table.

For reliability and performance, we use InnoDB for almost everything at Wikipedia - we just can't afford the downtime implied by MyISAM use and check table for 400GB of data when we get a crash.

The speed advantage of MyISAM is overstated IMO. LiveJournal with a mostly write environment saw a big increase in speed when it switched from MyISAM to InnoDB and they are very happy with InnoDB.

At Wikipedia we achieved some major performance gains by exploiting the way InnoDB clusters records by their primary key. Took some quite common queries from 50 seeks per result page to 1 or 2 seeks per page. Worse, the code at that time allowed people to go back 50,000 rows using LIMIT, causing 50,000 seeks and a DOS vulnerability. Big improvement to working set size from the change as well, so the results were more often in cache. It's so great an advantage that like the write caching it could be a dealbreaker for anything MySQL might do about alternatives to InnoDB.

With InnoDB, if you don't need repeatability, you might also switch to the least consistent transaction isolation level for a particular query, since that can reduce the locking work InnoDB needs to do. That is, assuming that you really do need only the MyISAM lack of guarantees.

On the other hand, a crazy bit of code did once use SELECT ... FOR UPDATE to scan every row of a table. Switching that table to MyISAM was a quick hack until it could be fixed. The table locking of MyISAM was way faster than watching InnoDB lock every row individually.

We used to use MyISAM for fulltext, duplicating the data in the InnoDB master table. Once the query rate grew sufficiently high and the data size grew past a gigabyte or so it became completely unacceptable on performance grounds, taking more than half of our database server capacity and still not working well. We abandoned it and switched to Lucene. By that point we were in the top thousand sites on the net, so it had survived pretty well.

I'm also as MySQL Support Engineer but these views are from my Wikipedia role, not the MySQL one.

James Day

http://mysqldatabaseadministration.blogspot.com/2006/02/innodb-or-myisam-whats-your-preference.html

I'm going to take James Advice and use InnoDB for almost everything and MyISAM only when its suits.