GROUP_CONCAT from multiple subqueries?

I can't seem to figure this out for the life of me :x

I have two tables..

• 1 = Tag Links
• 2 = Tag Data

When I query for a profile, each profile can have multiple entries in the tag links table. Once I retrieve the tag links, I want to get the tag text from the tag data table.

I can do this with: SELECT * FROM platform.tagWords WHERE tagId IN (SELECT tagId FROM platform.user sProfilesTags WHERE userId = 1001)

But it goes through each tag (around 50,000) and then checks if it is assigned to the user so that isn't exactly a solution as each query takes 5-8 seconds.

Is there a way to reverse this?

Any tips or advice will be much appreciated.

Thanks in advance!

*update

开发者_如何学PythonSo I'm trying to give this a stab with a join, but I'm stuck there too :P

SELECT                                                                          

  GROUP_CONCAT(tagWords.tagWord SEPARATOR ', ') AS tags,                        
  usersProfiles.*                                                                

FROM platform.users u                                                          

INNER JOIN platform.usersProfilesTags ON usersProfilesTags.userId = u.userId   
INNER JOIN platform.usersProfiles ON usersProfiles.userId = u.userId           
INNER JOIN platform.tagWords ON tagWords.tagId = usersProfilesTags.tagId       

WHERE u.userName = 'mattstest'

Your first query looks fine. If it's taking 5-8 seconds, then you are probably missing an index on usersProfilesTags(userId).

Trying to do this in one query is more difficult and probably slower than doing two separate queries: one query to get the profile, and another query to get the tags. Generally, MySQL works best when you run multiple simple queries rather than one big complicated query. Subqueries are a relatively recent feature and often perform poorly.

Also note that you are missing a GROUP BY for the GROUP_CONCAT() query. You would need to list every column in userProfiles in the GROUP BY list.