GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

GROUP_CONCAT is extremely slow

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • GROUP_CONCAT is extremely slow

    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?

    Comment

    Working...
    X