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

performance

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)

Best Answer

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.

Related Solutions

Mysql – How should I save apache logs into a thesql table

Because this is really logging data that you are capturing, I would first store it in its raw form, then ETL/normalize it as needed.

For searching, you can index the front part of the URLs first few characters. Let's say the options are http:// and https:// - then limit the index size to three characters passed the protocol part.

CREATE TABLE log ( 
datetime_created DATETIME, 
url VARCHAR(1024), 
domainname VARCHAR(255),
someotherdata VARCHAR(255), 
...
INDEX `idx_domain` (domainname(3))) 
ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

InnoDB will allow you to search the table w/o locking it.
Compression will help with disk space.
INDEX idx_domain (domainname(3)) will index the first 3 characters of the domain name column and speed up searches. However, the strategy for REGEXP will be to try to match on the front of the domain name.

Scale will eventually be an issue if the site becomes popular, so buyer beware.

SQL Server – Book Suggestions for Performance Tuning

I like Kalen Delaney's internals book:

Microsoft SQL Server 2008 Internals

I also like the Brent Ozar / Christian Bolton book:

Professional SQL Server 2008 Internals and Troubleshooting

For T-SQL tuning, you can't get much a better T-SQL wizard than Itzik Ben-Gan:

And query tuning overall, Grant Fritchey and Sajal Dam:

SQL Server 2008 Query Performance Tuning Distilled

There are also some interesting solutions and opinions presented in various chapters in the two MVP Deep Dives titles (from which all proceeds go to two charities, War Child and Operation Smile):

SQL Server MVP Deep Dives Volume 1 (War Child)
SQL Server MVP Deep Dives Volume 2 (Operation Smile)

And a couple of the Red Gate titles @Kev left out, but which are both excellent:

Inside the SQL Server Query Optimizer (Ben Nevarez)
Troubleshooting SQL Server: A Guide for the Accidental DBA (Jonathan Kehayias / Ted Kreuger)

Best Answer

Related Solutions

Mysql – How should I save apache logs into a thesql table

SQL Server – Book Suggestions for Performance Tuning

Related Question