What MySQL engine would you recommend?
Asked by
iTony (
202)
April 17th, 2008
I am working in a school website that it would showcase students work like videos, pictures, articles, news. Normal users would be able to comment, fave, and rate this content.
We are on the database design stage and we are using MySQL for our database management system, but I am not sure which engine to use on this work since I’ve never work on a website this big. The database would be accessed any time the authors (not everybody can upload content) would upload their media content(article, video, music, picture, or game) to the database. Also when the normal user (registered user) would comment, rate, or fave a media content.
Mainly I am wondering about the pluggable engines. I am not sure if I should worry about database transactions and use the InnoDB Engine or not worry about them MyISAM.
Or if you think I should use other engine
Observing members:
0
Composing members:
0
5 Answers
I’m a big fan of the whole ‘foreign key’ concept, so I really only use InnoDB (since MyISAM doesn’t support it). If I remember correctly, general statements run faster on InnoDB than MyISAM, and for a system with a high volume of traffic faster is better!
MyISAM is likely to be smaller on disk, but disk space is cheap, so in most cases that’s not really a determining factor. Though if you plan on implementing a full-text search, go with MyISAM for sure.
Actually, I’m pretty sure that MyISAM is more optimized for speed. And it supports (like any mysql engine) foreign keys. MySQL’s full-text search only works with MyISAM.
What it doesn’t give you are transactions and row-level locking—so if you’re doing very sensitive transactions or doing lots and lots of writes, you should consider it. Otherwise, I’d stick with MySQL’s default: MyISAM.
Edit: If memory serves, MyISAM doesn’t support database-level enforcement of foreign key relationships, but any backend interface with it would handle that for you. We’ve never had a problem with Fluther.
Yeah, it was the enforcement I was referring to, I’m sorry. I would assume a lot of it is situational, so you may get higher speeds via MyISAM in some cases, and InnoDB in others…
If memory serves (it’s been a while since I was in db class) MyISAM doesn’t have the same overhead for select statements that InnoDB has, so that may be where some speed increase may exist.
I’m certain there has to be sites out there that test all that stuff out though.
Sounds like you need MyISAM (for the reasons already given).
Thanks for your opinion. I’ve made some experiments with the 2 engines with our database plus the points you guys raised here. It all points out to MyISAM. thanks.
Answer this question
This question is in the General Section. Responses must be helpful and on-topic.