CODEWITHSUNDEEP
mysqldatabase-design
Attila Kun
Attila Kun

Reputation: 2415

Dilemma regarding database design for a discussion board using MySQL

In a discussion board there are generally many more posts, than topic headlines (titles). IMO it would be a cleaner practice to treat headlines as posts, but wouldn't it put serious penalty on performance if MySQL searches through every post to catch the headlines?

Making a seperate table for the headlines would certainly increase the performance, but I feel that it adds an unnecessary complexity.

What side should I choose of this performance-cleanliness trade-off?

Upvotes: 0

Views: 487

Answers (4)

Pascal MARTIN
Pascal MARTIN

Reputation: 401182

I would go with the simpler solution (considering headlines as posts -- which you should only do if those two share most of their properties and behaviors), at least until you really have performance problems.

And, if you do have performances problems, there are probably easier solutions to optimize, like :

  • using the right indexes in your Database -- you should do that right now, actually ^^
  • using cache (either caching HTML, or data) -- using memcached, for instance, even if it's only for a short period of time.


I might add, like Donald Knuth said :

We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.

If, one day, you really are experiencing performances problems, maybe that day it'll be time to try that kind of ideas... But as long as you don't need it... Why go through all that kind of trouble ?

And, even then, it might be cheaper to add more cache, or throw a second server into the game, than re-think that kind of thing -- and the performance benefits might be far better, actually !

Upvotes: 2

Walter Mitty
Walter Mitty

Reputation: 18940

Don't design tables based on performance considerations. Performance is important, but you need to get the logical model right before you begin worrying about performance. A wrong logical model results in endlessly contorted code, lots of bugs, long development times, and, eventually, slow performance.

...what Knuth said.

A table design that is both right and relevant can be worked with to get you the performance you need. You've got indexes, you've got table partitions, you've got oodles of tools that can fine tune the database without impacting the application.

Upvotes: 2

Godeke
Godeke

Reputation: 16281

It depends on what features your headlines share with the posts. It may be that headlines are distinct enough that it is better to keep them independent. Additionally, consider that you can use your headline rows as a denormalized performance improvement (update "most recent post" time, user and preview to the headline and you don't need to dig for that on the front page queries).

Upvotes: 1

DVK
DVK

Reputation: 129529

You can simply have an "is_headline" column with an index that starts with that column aimed at "headlines only" queries.

That would be OK performance while avoiding complexities of 2 table design.

Upvotes: 0

Related Questions