Loading tickets is slow. The issue seems to be when joining to the ticket__cdata table based on running EXPLAIN on the below query (B4 is listed as type=ALL, no Key used, Using where; Using join buffer (flat, BNL join)).
SELECT A1.`number` AS `number`, A1.`ticket_id` AS `ticket_id`, A1.`source` AS `source`, A1.`lastupdate` AS `lastupdate`, B4.`subject` AS `cdata__subject`, A1.`isoverdue` AS `isoverdue`, B5.`name` AS `user__name`, B4.`priority` AS `cdata__priority`, A1.`dept_id` AS `dept_id`, B6.`name` AS `dept__name`, A2.`id` AS `status__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, B7.`expire` > NOW() AND NOT B7.`staff_id` = 3 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 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_department` B6 ON (A1.`dept_id` = B6.`id`) LEFT JOIN `ost_lock` B7 ON (A1.`lock_id` = B7.`lock_id`) WHERE A2.`state` = 'open' AND A2.`state` = 'open' AND NOT A1.`topic_id` = 0 AND ((A2.`state` = 'open' AND (A1.`staff_id` = 3 OR A5.`staff_id` = 3 OR A6.`object_type` = 'C' AND A8.`staff_id` = 3 OR A1.`team_id` IN (1, 3, 4) OR B0.`team_id` IN (1, 3, 4) OR A6.`object_type` = 'C' AND B1.`team_id` IN (1, 3, 4))) OR A1.`dept_id` IN (1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20) OR B2.`id` IN (1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20) OR A6.`object_type` = 'C' AND B3.`id` IN (1, 2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20)) AND (A1.`ticket_pid` IS NULL OR A1.`flags` & 8 != 0) GROUP BY A1.`ticket_id` ORDER BY A1.`lastupdate` DESC LIMIT 50;
