Many-to-many - restrict to one w/ GROUP BY, but fetch all

[silverquick]

Hi all. Hope that subject wasn't too confusing ... here's what I'm going for:

I have a table of videos, and a table of themes. Each video is associated with 3 themes through the videos_themes table (thus it's a many-to-many relationship).

There is a page where you can browse videos by selecting a theme. So on this page I want to show only those videos which have the selected theme as one of its 3 themes, but at the same time, I want to fetch the 3 themes for each video matched, so they can be displayed on the page. Rough example:

Select theme: <baseball>

* Video 1 - themes: baseball, rain, bloopers
* Video 2 - themes: friends, picnics, baseball
* Video 3 - etc.

I'm working with something like the following query, which does TWO inner joins to the videos_themes table: one that is used in the WHERE clause to restrict the videos to the selected theme, and another so that the GROUP_CONCAT function can get a list of all 3 themes for each video.

SELECT v.*, GROUP_CONCAT(t.name) AS themes_list
FROM videos v
  INNER JOIN videos_themes vt1 ON v.id = vt1.video_id
  INNER JOIN videos_themes vt2 ON v.id = vt2.video_id
  INNER JOIN themes t ON vt2.theme_id = t.id
WHERE vt1.theme_id = <selected theme id> # reduces vt1 to just one row per video
GROUP BY v.id # one row per video

I'm just feeling like this isn't very graceful and there must be a better/faster way to do it. The query (which is actually a good deal more complex than the above) is taking 3-4 ms, which doesn't seem too bad--although the table is still pretty small. But I'm not sure this is the best way--I'd be grateful for any criticism.

[r937]

looks pretty good to me

it gets the right answer, doesn't it?

as for performance, did you declare any indexes on those tables?

[silverquick]

Hey r937, thanks for the reply.

Yes it does get the right answer. I have used two joins to the same table before, but it just seems like a last-ditch kind of thing to do

As for indexes, yes, the 'id' columns are all primary keys (obviously), and the videos_themes table just consists of those two columns -- video_id and theme_id -- and the primary key is on both of them (compound). Come to think of it, I should probably index them individually too for the purposes of the WHERE and (JOIN) ON clauses in this query. Or maybe it doesn't matter since there are no other columns anyway?

[r937]

since your PK is (video_id,theme_id) -- which is correct, by the way, thanks for not tossing an auto_increment in there -- then all you need is an additional index on (theme_id,video_id)

[silverquick]

Yeah auto columns on xref tables irk me

So you're saying I should have one index with video_id first and another with theme_id first? That makes sense to me. What if the second index was just (theme_id)? What difference would this make?

[r937]

So you're saying I should have one index with video_id first and another with theme_id first?

you already have one of them -- PKs are automatically indexed

as for just theme_id by itself, do a search for covering index