group_concat performance issue in MySQL
mysql, sql
Solution
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.
Problem
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?