CODEWITHSUNDEEP
mysql
zima10101
zima10101

Reputation: 307

slow GROUP_CONCAT in sub query

have a sub query which uses GROUP_CONCAT to group results in one field. but the query takes 3 secs to run with this and 0.05secs without. I have indexed accessory.actionid and action.actionid is PK. Is there a solution to improve performance?

SELECT
  action.actiondate, acc.acc, acc.acccount
FROM
  `action`
LEFT JOIN 
  (SELECT 
    actionid,
    GROUP_CONCAT(accessory) AS acc,
    COUNT(actionid) AS acccount
  FROM
  accessorieslink 
  GROUP BY actionid) AS acc 
  ON acc.actionid = action.actionid

Upvotes: 0

Views: 157

Answers (2)

echo_Me
echo_Me

Reputation: 37233

because you are making alias of group_concat same as the table acc which cause mysql to think.

change one of the aliace names. 

   GROUP_CONCAT(accessory) AS acc,
                               ^^^--//------ this

and

  GROUP BY actionid) AS acc 
                        ^^--// and this

Upvotes: 1

Stephan
Stephan

Reputation: 8090

You might be better off using this:

SELECT
    a.actiondate, 
    GROUP_CONCAT(IFNULL(al.accessory,'') ) as acc, 
    SUM(IF(al.actionid IS NULL,0,1)) as acccount
FROM
  `action` a
LEFT JOIN accessorieslink al
    ON al.actionid = a.actionid
GROUP BY
    a.actionid
ORDER BY 
    NULL

Upvotes: 1

Related Questions