KevinTheJedi We're experiencing a similar issue with another use case - trying to view "closed" tickets. Just clicking this button issues another large query, which runs forever

Here's the query

SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`closed` AS `closed`, B4.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B5.`name` AS `user__name`, B6.`firstname` AS `staff__firstname`, B6.`lastname` AS `staff__lastname`, B7.`name` AS `team__name`, A1.`team_id` AS `team_id`, A1.`isanswered` = '0' AS `2GkBZxz`, (SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND NOT R0.`flags` & 4 != 0) AS _thread_count, (SELECT COUNT(R1.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) LEFT JOIN `ost_attachment` R1 ON (R1.`type` = 'H' AND R0.`id` = R1.`object_id`) WHERE Q8.`ticket_id` = A1.`ticket_id` AND R1.`inline` = 0) AS _att_count, B8.`expire` > NOW() AND NOT B8.`staff_id` = 4 AS `_locked`, (SELECT COUNT(R0.`id`) AS `count` FROM `ost_thread` Q7 JOIN `ost_ticket` Q8 ON (Q7.`object_type` = 'T' AND Q7.`object_id` = Q8.`ticket_id`) LEFT JOIN `ost_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) WHERE Q8.`ticket_id` = A1.`ticket_id`) AS _collabs, COALESCE(B6.`firstname`, B6.`lastname`, B7.`name`, 'zzz') AS `assignee` FROM `ost_ticket` A1 JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`) LEFT JOIN `ost_thread` A3 ON (A3.`object_type` = 'T' AND A1.`ticket_id` = A3.`object_id`) LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) LEFT JOIN `ost_staff` A5 ON (A4.`object_type` = 'S' AND A4.`object_id` = A5.`staff_id`) LEFT JOIN `ost_thread` A6 ON (A1.`ticket_id` = A6.`object_id` AND A6.`object_type` = 'C') LEFT JOIN `ost_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) LEFT JOIN `ost_staff` A8 ON (A7.`object_type` = 'S' AND A7.`object_id` = A8.`staff_id`) LEFT JOIN `ost_team` B0 ON (A4.`object_type` = 'E' AND A4.`object_id` = B0.`team_id`) LEFT JOIN `ost_team` B1 ON (A7.`object_type` = 'E' AND A7.`object_id` = B1.`team_id`) LEFT JOIN `ost_department` B2 ON (A4.`object_type` = 'D' AND A4.`object_id` = B2.`id`) LEFT JOIN `ost_department` B3 ON (A7.`object_type` = 'D' AND A7.`object_id` = B3.`id`) LEFT JOIN `ost_ticket__cdata` B4 ON (A1.`ticket_id` = B4.`ticket_id`) LEFT JOIN `ost_user` B5 ON (A1.`user_id` = B5.`id`) LEFT JOIN `ost_staff` B6 ON (A1.`staff_id` = B6.`staff_id`) LEFT JOIN `ost_team` B7 ON (A1.`team_id` = B7.`team_id`) LEFT JOIN `ost_lock` B8 ON (A1.`lock_id` = B8.`lock_id`) WHERE A2.`state` = 'closed' AND ((A2.`state` = 'open' AND (A1.`staff_id` = 4 OR A5.`staff_id` = 4 OR A6.`object_type` = 'C' AND A8.`staff_id` = 4 OR A1.`team_id` IN (2) OR B0.`team_id` IN (2) OR A6.`object_type` = 'C' AND B1.`team_id` IN (2))) OR A1.`dept_id` IN (1, 3) OR B2.`id` IN (1, 3) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 3)) AND (A1.`ticket_pid` IS NULL OR A1.`flags` & 8 != 0) GROUP BY A1.`ticket_id` ORDER BY A1.`closed` DESC LIMIT 50

Perhaps you have some suggestions what else we could tweak?

KevinTheJedi Thanks for your reply, I'd like to note that it was working fine before the 1.16 update so I was expecting there was something I could do code-wise to fix this? Just adding more resources to the server because there might be a code error doesn't seem like the best way to go. I'll check on the attachment suggestion, thanks.

@KevinTheJedi
This is the output of explain

What troubles me the most is the B4 with null key used and 22706 rows. Any idea how I could make this situation better?

I have modified the query a little bit

select q1.*, q2.subject as `cdata__subject` from (
    SELECT 
  A1.`number` AS `number`, 
  A1.`ticket_id` AS `ticket_id`, 
  A1.`source` AS `source`, 
  A1.`closed` AS `closed`, 
  A1.`isoverdue` AS `isoverdue`, 
  B5.`name` AS `user__name`, 
  B6.`firstname` AS `staff__firstname`, 
  B6.`lastname` AS `staff__lastname`, 
  B7.`name` AS `team__name`, 
  A1.`team_id` AS `team_id`, 
  A1.`isanswered` = '0' AS `2GkBZxz`, 
  (
    SELECT 
      COUNT(R0.`id`) AS `count` 
    FROM 
      `ost_thread` Q7 
      JOIN `ost_ticket` Q8 ON (
        Q7.`object_type` = 'T' 
        AND Q7.`object_id` = Q8.`ticket_id`
      ) 
      LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) 
    WHERE 
      Q8.`ticket_id` = A1.`ticket_id` 
      AND NOT R0.`flags` & 4 != 0
  ) AS _thread_count, 
  (
    SELECT 
      COUNT(R1.`id`) AS `count` 
    FROM 
      `ost_thread` Q7 
      JOIN `ost_ticket` Q8 ON (
        Q7.`object_type` = 'T' 
        AND Q7.`object_id` = Q8.`ticket_id`
      ) 
      LEFT JOIN `ost_thread_entry` R0 ON (Q7.`id` = R0.`thread_id`) 
      LEFT JOIN `ost_attachment` R1 ON (
        R1.`type` = 'H' 
        AND R0.`id` = R1.`object_id`
      ) 
    WHERE 
      Q8.`ticket_id` = A1.`ticket_id` 
      AND R1.`inline` = 0
  ) AS _att_count, 
  B8.`expire` > NOW() 
  AND NOT B8.`staff_id` = 4 AS `_locked`, 
  (
    SELECT 
      COUNT(R0.`id`) AS `count` 
    FROM 
      `ost_thread` Q7 
      JOIN `ost_ticket` Q8 ON (
        Q7.`object_type` = 'T' 
        AND Q7.`object_id` = Q8.`ticket_id`
      ) 
      LEFT JOIN `ost_thread_collaborator` R0 ON (Q7.`id` = R0.`thread_id`) 
    WHERE 
      Q8.`ticket_id` = A1.`ticket_id`
  ) AS _collabs, 
  COALESCE(
    B6.`firstname`, B6.`lastname`, B7.`name`, 
    'zzz'
  ) AS `assignee` 
FROM 
  `ost_ticket` A1 
  JOIN `ost_ticket_status` A2 ON (A1.`status_id` = A2.`id`) 
  LEFT JOIN `ost_thread` A3 ON (
    A3.`object_type` = 'T' 
    AND A1.`ticket_id` = A3.`object_id`
  ) 
  LEFT JOIN `ost_thread_referral` A4 ON (A3.`id` = A4.`thread_id`) 
  LEFT JOIN `ost_staff` A5 ON (
    A4.`object_type` = 'S' 
    AND A4.`object_id` = A5.`staff_id`
  ) 
  LEFT JOIN `ost_thread` A6 ON (
    A1.`ticket_id` = A6.`object_id` 
    AND A6.`object_type` = 'C'
  ) 
  LEFT JOIN `ost_thread_referral` A7 ON (A6.`id` = A7.`thread_id`) 
  LEFT JOIN `ost_staff` A8 ON (
    A7.`object_type` = 'S' 
    AND A7.`object_id` = A8.`staff_id`
  ) 
  LEFT JOIN `ost_team` B0 ON (
    A4.`object_type` = 'E' 
    AND A4.`object_id` = B0.`team_id`
  ) 
  LEFT JOIN `ost_team` B1 ON (
    A7.`object_type` = 'E' 
    AND A7.`object_id` = B1.`team_id`
  ) 
  LEFT JOIN `ost_department` B2 ON (
    A4.`object_type` = 'D' 
    AND A4.`object_id` = B2.`id`
  ) 
  LEFT JOIN `ost_department` B3 ON (
    A7.`object_type` = 'D' 
    AND A7.`object_id` = B3.`id`
  ) 
  LEFT JOIN `ost_user` B5 ON (A1.`user_id` = B5.`id`) 
  LEFT JOIN `ost_staff` B6 ON (A1.`staff_id` = B6.`staff_id`) 
  LEFT JOIN `ost_team` B7 ON (A1.`team_id` = B7.`team_id`) 
  LEFT JOIN `ost_lock` B8 ON (A1.`lock_id` = B8.`lock_id`) 
WHERE 
  A2.`state` = 'closed' 
  AND (
    (
      A2.`state` = 'open' 
      AND (
        A1.`staff_id` = 4 
        OR A5.`staff_id` = 4 
        OR A6.`object_type` = 'C' 
        AND A8.`staff_id` = 4 
        OR A1.`team_id` IN (2) 
        OR B0.`team_id` IN (2) 
        OR A6.`object_type` = 'C' 
        AND B1.`team_id` IN (2)
      )
    ) 
    OR A1.`dept_id` IN (1, 3) 
    OR B2.`id` IN (1, 3) 
    OR A6.`object_type` = 'C' 
    AND B3.`id` IN (1, 3)
  ) 
  AND (
    A1.`ticket_pid` IS NULL 
    OR A1.`flags` & 8 != 0
  ) 
GROUP BY 
  A1.`ticket_id` 
ORDER BY 
  A1.`closed` DESC 
LIMIT 
  50
) as q1
left join ost_ticket__cdata as q2 on q1.ticket_id = q2.ticket_id

This provides the following EXPLAIN output

Results in the same data set but runs much faster (although still takes around 20 seconds but at least not 5 minutes)

Perhaps you could guide me in which file I should look into to find the generation of this query? Which file is used when you click on "Closed" button?

Thanks