Should fields that aren't needed often be kept in separate tables?

Question

I have a tagging system on my site. I also want to add descriptions to the tags just like on the S.E. sites.

The tags are used for a lot of stuff. Its how categories know what products to display, how users can further drill down into categories, how product attributes are set, how coupons know which products they can be applied too, etc.

Because if this I want the tag table to be as efficient as possible. The tag descriptions won't be needed for any of those tasks, I just want to add them so the people entering data can keep track of what each tag is for (if the tag name is not self explanatory)

I would like to just add a description column to the tag table, but I am worried about hurting the performance on all the other logic that doesn't need the description. I am wondering if it would be better for performance to keep them in a separate tag_descriptions table?

By the way I am using the CakePHP framework which uses ORM (and I think it selects * for model associations)

Answer 1

I sympathize with this situation because of the SELECT * FROM statements from ORMs. General MySQL (and probably other RDBMs) index theory is to only select the columns that you need, preferably entirely from an index.

That being said, the answer depends on how much traffic you are expecting to get to see a noticeable degradation of performance. It also depends on how much usage you'll get out of the category descriptions.

I wouldn't think storing the descriptions in the same table would reduce performance enough to warrant 2x calls to the database when you DO need to grab the descriptions.

To sum it up, I think you've got a bigger performance problem in using an ORM than in storing the descriptions in the same tag table.

Answer 2

If you don't SELECT the description column from the tag table, then it shouldn't really effect performance. You can also add indexes accordingly to how you now use the table.

With those 2 things, I do not think that you should experience a lot of performance problems.