Advice on Optimizing Joins Query

4
votes

I have 3 tables : videos, categories, video_categories.

In videos, I have id, title, and other fields. In categories, I have id and name. In video_categories, I have id, video_id, and category_id.

One video can have multiple categories. So the video_categories table will be something like this.

id   video_id  category_id
1    1         1
2    1         2
3    1         3

If I want to have a list of videos and display their categories, which would be preferred?

  1. Via PHP, call 1 query to get all videos, then loop on that to query to get each video's categories, and another query to get the category name. This will be really slow if the table is huge, right?

  2. Via MySQL joins (need help on this). If I left join videos to video_categories, there will be 3 results of the same video_id. I can use GROUP BY or SELECT DISTINCT to get unique result, but how can I now get the categories' names?

My expected result will be something like this:

id title    categories
1  Video1   pop, rock, jazz
2
phpmysqlcodeigniter
I don't think your question has to do with denormalization, per se. Your schema, as you describe it, is completely normalized, meaning it has no redundant data. You could denormalize it by adding the categories column to the videos table, in which you would store the comma spearated string of the category names (e.g. 'pop, rock, jazz') and then keep it in sync with the redundant data in the categories and video_categories tables. As it stands, your question is really just asking about optimizing your join technique, which can be done with the group_concat method, as others note. - Patrick M
yeah, I got confused. I must have thought denormalization as the reverse process of normalization, which is reading and displaying the normalized schema. I've edited the title to avoid confusion - Henson

2 Answers

3
votes

Guessing the names of your other columns..

SELECT v.video_id, v.title, GROUP_CONCAT(c.category_name SEPARATOR ', ')
FROM videos v
LEFT JOIN video_categories vc ON vc.video_id = v.video_id
LEFT JOIN categories c ON c.category_id = vc.category_id
GROUP BY v.video_id, v.title
3
votes

For option 2, use GROUP_CONCAT. It will be ok 

SELECT v.id, v.title, GROUP_CONCAT(c.name)
FROM videos v
INNER JOIN video_categories vc ON vc.video_id = v.id
INNER JOIN categories c ON vc.category_id = c.id
GROUP BY v.id, v.title

For Group_Concat() function , is the default separator. That's why I don't use it here.