Optimize GROUP_CONCAT in SQL on MySQL

Is there a way to get the following SQL optimized so it would run faster?

SELECT bitValue as bitV, endName as endN, filDuration as filD, filSize as filS, genName as genN, sonAdditionalPrefix AS sonAP, sonDate as sonD, sonID, sonIsRip, sonName, typName, sonPlays as plays,
(SELECT GROUP_CONCAT(a.artName SEPARATOR ';') FROM tblArtists a, tblSoAr sa WHERE a.artID = sa.soarartIDRef AND sa.soarsonIDRef = s.sonID) as artists,
(SELECT GROUP_CONCAT(a.artName SEPARATOR ';') FROM tblArtists a, tblRemixer r WHERE a.artID = r.remartIDRef AND r.remsonIDRef = s.sonID) as remixer,
(SELECT GROUP_CONCAT(a.artName SEPARATOR ';') FROM tblArtists a, tblFeaturing f WHERE a.artID = f.feaartIDRef AND f.feasonIDRef = s.sonID) as featuring,
(SELECT GROUP_CONCAT(ta.tagName SEPARATOR ';') FROM tblTags ta, tblSoTa st WHERE ta.tagID = st.sotatagIDRef AND st.sotasonIDRef = s.sonID) as tags
FROM tblSongs s, tblFiles f, tblGenres g, tblEndings e, tblBitrates b, tblTypes t
WHERE s.sonfilIDRef = f.filID AND s.songenIDRef = g.genID AND f.filendIDRef = e.endID AND f.filbitIDRef = b.bitID AND s.sontypIDRef = t.typID AND sonshaIDRef = 1 AND sonWasEdited = 1 AND sonDeleted = 0 AND sonOnWishlist = 0 ;

I encountered the bottleneck are the GROUP_CONCAT’s but I couldn’t find a way by using an alternative or optimizing the query so it would run faster. At the moment the query needs about 3.2s on 2700 entries (mysql database 5.1.73, mysqli). All tables have indices set on the important columns.

So after implementing some LEFT JOINS my query looks the following:

SELECT bitValue as bitV, endName as endN, filDuration as filD, filSize as filS, genName as genN, sonAdditionalPrefix AS sonAP, sonDate as sonD, sonID, sonIsRip, sonName, typName, sonPlays as plays,    
conArtists.artists, conRemixer.remixer, conFeaturing.featuring, conTags.tags
FROM (tblSongs s, tblFiles f, tblGenres g, tblEndings e, tblBitrates b, tblTypes t)
LEFT JOIN (SELECT tblSoAr.soarsonIDRef as soarsonIDRef, GROUP_CONCAT(tblArtists.artName SEPARATOR ';') as artists FROM tblSoAr, tblArtists WHERE tblArtists.artID = tblSoAr.soarartIDRef GROUP BY soarsonIDRef) AS conArtists ON conArtists.soarsonIDRef = s.sonID
LEFT JOIN (SELECT tblRemixer.remsonIDRef as remsonIDRef, GROUP_CONCAT(tblArtists.artName SEPARATOR ';') as remixer FROM tblRemixer, tblArtists WHERE tblArtists.artID = tblRemixer.remsonIDRef GROUP BY remsonIDRef) AS conRemixer ON conRemixer.remsonIDRef = s.sonID
LEFT JOIN (SELECT tblFeaturing.feasonIDRef as feasonIDRef, GROUP_CONCAT(tblArtists.artName SEPARATOR ';') as featuring FROM tblFeaturing, tblArtists WHERE tblArtists.artID = tblFeaturing.feasonIDRef GROUP BY feasonIDRef) AS conFeaturing ON conFeaturing.feasonIDRef = s.sonID
LEFT JOIN (SELECT tblSoTa.sotasonIDRef as sotasonIDRef, GROUP_CONCAT(tblTags.tagName SEPARATOR ';') as tags FROM tblSoTa, tblTags WHERE tblTags.tagID = tblSoTa.sotasonIDRef GROUP BY sotasonIDRef) AS conTags ON conTags.sotasonIDRef = s.sonID
WHERE s.sonfilIDRef = f.filID AND s.songenIDRef = g.genID AND f.filendIDRef = e.endID AND f.filbitIDRef = b.bitID AND s.sontypIDRef = t.typID AND sonshaIDRef = 1 AND sonWasEdited = 1 AND sonDeleted = 0 AND sonOnWishlist = 0;

Answer

I think you can avoid the subqueries (that are done for every row) by grouping the artName values first and then joining them:

old query:

select
a.id,
(select group_concat(x) as x_concat from table_2 as _b where a.id = _b.id and ...) as b,
(select group_concat(x) as x_concat from table_3 as _c where a.id = _b.id and ...) as c
from table_1 as a
where ...

Should be something like this:

select
a.id,
b.x_concat,
c.x_concat
from
table_1 as a,
left join (select id, group_concat(x) as x_concat from table_2 where ... group by id) as b on a.id = b.id,
left join (select id, group_concat(x) as x_concat from table_3 where ... group by id) as c on a.id = c.id
where ...

Advertisement

Answer