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)

  • So if you add the description as a separate column on the table and add an index just on the tag column itself, the ORM will still read the entire row even if you only ask for the tag due to the SELECT *. Is that what your last line is hinting at? – Nick Chammas Sep 15 '11 at 20:43

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.

| improve this answer | |
  • 4
    +1 for: "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." – MicSim Sep 15 '11 at 20:34

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.