Page 1 of 1

MySQL Storage Engine Choices

Posted: Sat Oct 22, 2005 12:57 pm
by onion2k
I'm currently setting up a new site .. and I have the opportunity to switch from the default MyISAM table type to InnoDB. The reason I'm considering doing this is so I can use transactions (and possibly foreign key relationship checking). Are there any good reasons against such a switch? Are InnoDB tables quicker/slower than MyISAM? Will my database backups work ok (they use mysqldump)? There will be MyISAM based databases running on the same server.. does that make a different? What do you use?

Re: MySQL Storage Engine Choices

Posted: Sat Oct 22, 2005 7:11 pm
by Roja
onion2k wrote:I'm currently setting up a new site .. and I have the opportunity to switch from the default MyISAM table type to InnoDB. The reason I'm considering doing this is so I can use transactions (and possibly foreign key relationship checking). Are there any good reasons against such a switch?
Not many. Innodb is in almost every way superior to MyISAM. A few minor caveats below..
onion2k wrote:Are InnoDB tables quicker/slower than MyISAM?
It depends on what you are doing. For transaction processing, they are faster, because you can row-lock instead of table lock. Sadly, row-locking still locks the table to reads on versions below 4.1. Or something like that.. details in the manual. They fixed it in 4.1, was my understanding.

For everything else, MyISAM tends to be somewhat faster. In most things I've tested it on, its been a trivial difference, but if you are building the next yahoo...
onion2k wrote:Will my database backups work ok (they use mysqldump)? There will be MyISAM based databases running on the same server.. does that make a different? What do you use?
Yes, and no.

I now use innodb exclusively on mysql unless I simply cannot do so. Of course, I've been migrating to postgresql for most things now, so take it with a grain of salt. Your mileage may vary.