General Question

iTony's avatar

What MySQL engine would you recommend?

Asked by iTony (202points) 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

adrianscott's avatar

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.

andrew's avatar

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.

adrianscott's avatar

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.

richardhenry's avatar

Sounds like you need MyISAM (for the reasons already given).

iTony's avatar

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




to answer.

This question is in the General Section. Responses must be helpful and on-topic.

Your answer will be saved while you login or join.

Have a question? Ask Fluther!

What do you know more about?
Knowledge Networking @ Fluther