GROUP_CONCAT is extremely slow

MySQL & MariaDB Other MySQL® Questions
1

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 | |
±—±------------±------±-------±---------------------- -----------±---------±--------±-------------------------- ---------------±--------±---------±---------------------- ------+

2

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?