MySQL Storage Engine Choices
Moderator: General Moderators
MySQL Storage Engine Choices
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
Not many. Innodb is in almost every way superior to MyISAM. A few minor caveats below..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?
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.onion2k wrote:Are InnoDB tables quicker/slower than MyISAM?
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...
Yes, and no.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?
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.