Dear Team,
We have noticed too many delays and timeout issues happening with our osTicket when viewing the tickets which closed 'This Year'. Upon checking, we have seen that the MySQL query is taking too much time( 200 plus seconds) for this particular listing. The query is
SELECT A1.number AS number, A1.ticket_id AS ticket_id, A1.source AS source, A1.closed AS closed, B2.subject AS cdata__subject, A1.isoverdue AS isoverdue, B3.name AS user__name, B4.firstname AS staff__firstname, B4.lastname AS staff__lastname, B5.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, B6.expire > NOW() AND NOT B6.staff_id = 6 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(B4.firstname, B4.lastname, B5.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_department B0 ON (A4.object_type = 'D' AND A4.object_id = B0.id) LEFT JOIN ost_department B1 ON (A7.object_type = 'D' AND A7.object_id = B1.id) LEFT JOIN ost_ticket__cdata B2 ON (A1.ticket_id = B2.ticket_id) LEFT JOIN ost_user B3 ON (A1.user_id = B3.id) LEFT JOIN ost_staff B4 ON (A1.staff_id = B4.staff_id) LEFT JOIN ost_team B5 ON (A1.team_id = B5.team_id) LEFT JOIN ost_lock B6 ON (A1.lock_id = B6.lock_id) WHERE A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59' AND ((A2.state = 'open' AND (A1.staff_id = 6 OR A5.staff_id = 6 OR A6.object_type = 'C' AND A8.staff_id = 6)) OR A1.dept_id IN (4) OR B0.id IN (4) OR A6.object_type = 'C' AND B1.id IN (4)) AND (A1.ticket_pid IS NULL OR A1.flags & 8 != 0) GROUP BY A1.ticket_id ORDER BY A1.lastupdate DESC LIMIT 25;
Dear team members and developers, please check and advise a solution.
osTicket version: 1.17.3
Server version: 10.6.18-MariaDB - MariaDB Server
Database client version: libmysql - mysqlnd 8.1.28
PHP - 8.0
RAM - 64GB
Thank you
