0

Coming from here sql group_concat and subquery

I managed to "solve" de problem by doing this a "subquery":

SELECT
GROUP_CONCAT(name,',',results separator '#')
as finalresult 
FROM
(

    select t.name as name, group_concat(distinct r.idResult separator '-') as results
    from threshold t
    left join threshold_results r on r.idThreshold = t.idThreshold 
    group by t.idThreshold, t.name

) final

But it is too slow when there are many records, withouth the subquery the initial solution performs very well. Any ideas?

Thank you!

Community
  • 1
  • 1
user3032175
  • 87
  • 1
  • 7
  • 1
    Isn't it better to run only the inner query and do the final concatenation in the client code? The outer query does not run any database-specific processing, it just concatenate some strings and this could be achieved with better results (regarding the speed) in the client code. – axiac Jan 30 '15 at 12:22
  • I thought it was faster to do it in the database rather than in the client code. – user3032175 Jan 30 '15 at 12:24
  • 2
    If you run the subquery on its own, how does it perform? What sort of performance differences (in terms of seconds) are you seeing? –  Jan 30 '15 at 12:27
  • subquery duration less than 1 second, final query about 14 seconds – user3032175 Jan 30 '15 at 12:31

1 Answers1

1

add index on r.idThreshold, t.name

andrei.mihail.nicolae
  • 54
  • 3
  • It already has index, I think the problem is the main GROUP_CONCAT if I add more fields it gets even slower, maybe I will have to try to do it, from the client code – user3032175 Jan 30 '15 at 12:40