MySQL headache, should I or should I not?

Question

I have a classifieds website. I am using SOLR for indexing and storing data. Then I also have a MySQL db with some more information about the classified which I dont store or index.

Now, I have a pretty normalized db with 4 tables.

Whenever ads are searched on the website, SOLR does the searching and returns an array of ID_numbers which will then be used to query mysql. So solr returns id:s, which are then used to get all ads from the mysql db with THOSE id:s.

Now, all the JOIN and relations between my tables gives me a headache.

What except for maintanance-ease do I get for having a normalized db?

I could you know, store all info into one table with some 50 columns.

So instead of this for finding one ad and displaying it:

SELECT 
  category_option.option_name,
  option_values.value 
FROM classified, category_option, option_values 
WHERE classified.classified_id=?id 
AND classified.cat_id=category_options.cat_id 
AND option_values.option_id=category_options.option_id

I could use this:

SELECT * FROM table_name WHERE classified_id = $classified_id

Isn't the last one actually faster? Or does a normalized db permform faster?

Thanks

Answer 1

Whenever you do denormalization you usually gain reading speed and lose write speed, because you have to write the same value many times. Additionally, extra care should be taken to maintain data integrity.

• How many times the query will be executed?
• Is this a high traffic application?
• Can you add a cache?

Answer 2

I would advise against denormalizing in your situation. You'll get better with joins as you use them more and they start to become clearer in your head, and maintenance ease is a good benefit for the future.

Here's a pretty good link about normalization (and denormalization). Here's a question about denormalization. One answer suggests creating a view using joins to get the data you need, and using that like your SELECT * FROM table_name WHERE classified_id = $classified_id query. A normalized DB will likely be slower, but it's unlikely you'll want to denormalize for that reason. I hope this provides some help.

Answer 3

The query using a JOIN is trivial as far as MySQL joins are concerned. I see no need to denormalize this.

I would however suggest rewriting it to not be such a PITA to read:

SELECT 
  category_option.option_name,
  option_values.value 
FROM classified
JOIN category_option USING (cat_id)
JOIN option_values USING (option_id)
WHERE classified.classified_id = ?