# GROUP\_CONCAT is extremely slow

**URL:** <https://forums.percona.com/t/group-concat-is-extremely-slow/1444>\
**Category:** Other MySQL® Questions\
**Created:** [June 30, 2010, 8:08am UTC](https://forums.percona.com/t/group-concat-is-extremely-slow/1444 "2010-06-30T08:08:33Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![dahuuhad](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dahuuhad](https://forums.percona.com/u/dahuuhad)\
**Post date:** [June 30, 2010, 8:08am UTC](https://forums.percona.com/t/group-concat-is-extremely-slow/1444/1 "2010-06-30T08:08:33Z")

</div>

Hi,

I have a query that is extremely slow, it takes minutes to execute.

DESC EXTENDED  
SELECT r.id, r.start, r.end, GROUP\_CONCAT(oc.value) FROM te\_reservation r  
JOIN te\_reservation\_object ro ON r.id = ro.te\_reservation  
JOIN te\_object\_char oc ON oc.te\_object = ro.te\_object AND oc.te\_field = 8  
JOIN ac\_user\_permission aup ON aup.ac\_list = r.ac\_list AND aup.te\_user = 11230 AND aup.context = 1 AND aup.permission = 0  
WHERE r.properties & 1  
GROUP BY r.id  
ORDER BY r.start, r.end  
LIMIT 0, 200;

±—±------------±------±-------±---------------------- -----------±---------±--------±-------------------------- ---------------±-----±-----------±----------------------- ----------------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |  
±—±------------±------±-------±---------------------- -----------±---------±--------±-------------------------- ---------------±-----±-----------±----------------------- ----------------------+  
| 1 | SIMPLE | r | index | PRIMARY,ac\_list | PRIMARY | 4 | NULL | 100 | 2154606.00 | Using where; Using temporary; Using filesort |  
| 1 | SIMPLE | aup | eq\_ref | PRIMARY,ac\_list | PRIMARY | 8 | const,const,const,te\_multi\_big.r.ac\_list | 1 | 100.00 | Using where; Using index |  
| 1 | SIMPLE | ro | ref | PRIMARY,te\_object,te\_reservation | PRIMARY | 4 | te\_multi\_big.r.id | 2 | 100.00 | Using index |  
| 1 | SIMPLE | oc | ref | te\_field,te\_object | te\_field | 6 | const,te\_multi\_big.ro.te\_object | 1 | 100.00 | |  
±—±------------±------±-------±---------------------- -----------±---------±--------±-------------------------- ---------------±-----±-----------±----------------------- ----------------------+

But if I avoid doing GROUP BY it just takes a few seconds to execute. But MySQL is doing a full table scan instead.

Any suggestions, what am I doing wrong?

DESC EXTENDED  
SELECT r.id, r.start, r.end, oc.value FROM te\_reservation r  
JOIN te\_reservation\_object ro ON r.id = ro.te\_reservation  
JOIN te\_object\_char oc ON oc.te\_object = ro.te\_object AND oc.te\_field = 8  
JOIN ac\_user\_permission aup ON aup.ac\_list = r.ac\_list AND aup.te\_user = 11230 AND aup.context = 1 AND aup.permission = 0  
WHERE r.properties & 1  
ORDER BY r.start, r.end  
LIMIT 0, 200;

±—±------------±------±-------±---------------------- -----------±---------±--------±-------------------------- ---------------±--------±---------±---------------------- ------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | filtered | Extra |  
±—±------------±------±-------±---------------------- -----------±---------±--------±-------------------------- ---------------±--------±---------±---------------------- ------+  
| 1 | SIMPLE | r | ALL | PRIMARY,ac\_list | NULL | NULL | NULL | 2154606 | 100.00 | Using where; Using filesort |  
| 1 | SIMPLE | aup | eq\_ref | PRIMARY,ac\_list | PRIMARY | 8 | const,const,const,te\_multi\_big.r.ac\_list | 1 | 100.00 | Using where; Using index |  
| 1 | SIMPLE | ro | ref | PRIMARY,te\_object,te\_reservation | PRIMARY | 4 | te\_multi\_big.r.id | 2 | 100.00 | Using index |  
| 1 | SIMPLE | oc | ref | te\_field,te\_object | te\_field | 6 | const,te\_multi\_big.ro.te\_object | 1 | 100.00 | |  
±—±------------±------±-------±---------------------- -----------±---------±--------±-------------------------- ---------------±--------±---------±---------------------- ------+

---

<div class="post-metadata">

**Author:** ![xaprb](https://avatars.discourse-cdn.com/v4/letter/x/49beb7/32.png) [@xaprb](https://forums.percona.com/u/xaprb)\
**Post date:** [July 5, 2010, 8:13pm UTC](https://forums.percona.com/t/group-concat-is-extremely-slow/1444/2 "2010-07-05T20:13:19Z")

</div>

You need to study the join plans and figure out what’s happening at the physical level. Remember that LIMIT is applied _after_ the group-by finishes, in the first query. How many rows would it return without the LIMIT? How long would the second query take to finish without the LIMIT?