Join query optimization tips

Question

I have 3 tables: videos , categories , video_categories .

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

There can be several categories in one video. Thus, 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, what would be preferable?

• Through PHP, call 1 query to get all the videos, then run a loop to query to get all the video categories, and another request to get the category name. It will be very slow if the table is huge, right?

• Through a MySQL connection (need help on this). If I stay, attach the video to video_categories, there will be 3 results of the same video_id. I can use GROUP BY or SELECT DISTINCT to get a unique result, but how now can I get category names?

My expected result would be something like this:

 id title categories 1 Video1 pop, rock, jazz 

Answer 1

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 delimiter. That is why I do not use it here.

Answer 2

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