CODEWITHSUNDEEP
mysqlsqlgroup-concat
reddy
reddy

Reputation: 1821

group_concat with left join is way too slow

I have a song library and a working query that joins multiple tables and returns all of song_id, song_title etc into one big table like this:

song_id             song_title          song_vocal          song_composer
--------------------------------------------------------------------------
1                   Hello Boy           John                Mike
2                   Hello Girl          Jim                 Dave
2                   Hello Girl          Tom                 Dave

And I want to combine songs with multiple vocal/composer into one row like this:

song_id             song_title          song_vocal          song_composer
--------------------------------------------------------------------------
1                   Hello Boy           John                Mike
2                   Hello Girl          Jim,Tom             Dave

I did it with the group_concat() function, but it's way too slow. Without the group_concat, my query only takes 0.06 seconds. With the function, it takes 3 seconds. Here is my query:

SELECT
songs.song_id,
songs.title as Song_Title,
group_concat(DISTINCT a1.artist_name ORDER BY a1.artist_name DESC SEPARATOR ',' ) as Vocals,
group_concat(DISTINCT a2.artist_name ORDER BY a2.artist_name DESC SEPARATOR ',' ) as Composers    

FROM songs

left join song_vocals ON song_vocals.song_id = songs.song_id
left join artists a1 ON song_vocals.artist_id = a1.artist_id

left join song_composers on songs.song_id = song_composers.song_id
left join artists a2  on a2.artist_id = song_composers.artist_id

GROUP BY songs.song_id

What did I do wrong?

Upvotes: 0

Views: 274

Answers (2)

Uueerdo
Uueerdo

Reputation: 15951

When dealing with multiple many-to-many relationships, the relationships almost always have to be (sub)queried separately and then merged.

SELECT s.song_id, s.title as Song_Title, v.Vocals, c.Composers    
FROM songs AS s
LEFT JOIN (
   SELECT song_id
        , GROUP_CONCAT(DISTINCT a.artist_name 
                       ORDER BY a.artist_name DESC 
                       SEPARATOR ',' 
          ) as Vocals
   FROM song_vocals AS sv
   LEFT JOIN artists AS a ON sv.artist_id = a.artist_id
   GROUP BY sv.song_id
) AS v ON s.song_id = v.song_id
LEFT JOIN (
   SELECT sc.song_id
        , GROUP_CONCAT(DISTINCT a.artist_name 
                       ORDER BY a.artist_name DESC 
                       SEPARATOR ',' 
          ) as Composers
   FROM song_composers AS sc
   LEFT JOIN artists AS a ON sc.artist_id = a.artist_id
   GROUP BY sc.song_id
) AS c ON s.song_id = c.song_id
;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Yes, of course this is too slow. You are generating a Cartesian product and then removing the duplicates.

I would recommend replacing the logic with correlated subqueries:

select s.song_id, s.title as Song_Title,
       (select group_concat(a.artist_name order by a.artist_name DESC separator ',' ) 
        from song_vocals sv join 
             artists a 
             on sv.artist_id = a.artist_id
        where sv.song_id = s.song_id
       ) as Vocals,
       (select group_concat(a.artist_name order by a.artist_name desc separator ',' ) 
        from song_composers sc join 
             artists a 
             on sc.artist_id = a.artist_id
        where sc.song_id = s.song_id
       ) as Composers    
from songs s;

This can also take advantage of indexes on song_composers(song_id, artist_id) and song_vocals(song_id, artist_id). I also removed the distinct from group_concat(). That should no longer be necessary.

Upvotes: 1

Related Questions