group_concat performance issue in MySQL

Question

I added a group_concat to a query and killed the performance. The explain plans are identical before and after I added it, so I'm confused as to how to optimize this.

Here is a simplified version of the query:

SELECT @curRow := @curRow + 1 AS row_number,
docID,
docTypeID,
CASE WHEN COUNT(1) > 1
     THEN group_concat( makeID )
     -- THEN 'multiple makes found'
     ELSE MIN(makeID)
END AS makeID,
MIN(desc) AS desc
FROM simplified_mysql_table,
(SELECT @curRow := 0) r
GROUP BY docID, docTypeID,
CASE WHEN docTypeID = 1
     THEN 0
     ELSE row_number
END;

Note the CASE statement in the SELECT. The group_concat kills performance. If I comment that line and just output 'multiple makes found' it executes very quickly. Any idea what is causing this?

Answer 1

In the original non-simplified version of this query we had a DISTINCT, which was completely unnecessary and causing the performance issue with group_concat. I'm not sure why it caused such a problem, but removing it fixed the performance issue.

Answer 2

In MySQL, group_concat performance should not kill query performance. It is additional work involving strings, so some slow down is expected. But more like 10% rather than 10X. Can you quantify the difference in the query times?

Question: is MakeID a character string or integer? I wonder if a conversion from integer to string might affect the performance.

Second, what would the performance be for concat(min(MakeId), '-', max(MakedId)) isntead of the group_concat?

Third, does the real group_concat use DISTINCT or ORDER BY? These could slow things down, especially in a memory limited environment.