SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    May 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:

    Code:
    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.

    Code MySQL:
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    looks pretty good to me

    it gets the right answer, doesn't it?

    as for performance, did you declare any indexes on those tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    May 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by silverquick View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •