We experience the same issue since upgrading from 1.10.x to 1.16.x. Especially opening the closed tickets tab is terribly slow. I think this has to do with the way the new filtering system is implemented. The queries generated by this are NOT at all efficient. I assume if you have around 10/20k tickets you won't have any trouble, but we have around 180k tickets and 500k thread entries, which causes delays ranging from a few seconds to 40 seconds. (16GB mem, 4 cores AMD Ryzen, MySQL 8, PHP 8.1). Throwing more cores or memory at the problem doesn't solve the problem, it has to do with poorly composed queries.
We're a non profit organization and have been using osTicket happily for about 7 years. I really hope they can throw in some fixes in the next release that restores the performance/responsetimes back to what they were before the upgrade.
Here is an example of the query used to open te closed tabs (40 seconds), 1.4 million examined rows!
Time: 2023-02-22T08:05:57.619639Z
User@Host: osticket[osticket] @ localhost [] Id: 129
Query_time: 41.523699 Lock_time: 0.000009 Rows_sent: 50 Rows_examined: 1387529
SELECT
A1.numberASnumber,
A1.ticket_idASticket_id,
A1.sourceASsource,
A1.closedASclosed,
B2.subjectAScdatasubject,
A1.isoverdueASisoverdue,
B3.nameASusername,
B4.firstnameASstafffirstname,
B4.lastnameASstafflastname,
B5.nameASteamname,
A1.team_idASteam_id,
A1.isanswered= '0' AS2GkBZxz,
(
SELECT
COUNT(R0.id) AScountFROMost_threadQ7
JOINost_ticketQ8 ON (
Q7.object_type= 'T'
AND Q7.object_id= Q8.ticket_id)
LEFT JOINost_thread_entryR0 ON (Q7.id= R0.thread_id)
WHERE
Q8.ticket_id= A1.ticket_idAND NOT R0.flags& 4 != 0
) AS _thread_count,
(
SELECT
COUNT(R1.id) AScountFROMost_threadQ7
JOINost_ticketQ8 ON (
Q7.object_type= 'T'
AND Q7.object_id= Q8.ticket_id)
LEFT JOINost_thread_entryR0 ON (Q7.id= R0.thread_id)
LEFT JOINost_attachmentR1 ON (
R1.type= 'H'
AND R0.id= R1.object_id)
WHERE
Q8.ticket_id= A1.ticket_idAND R1.inline= 0
) AS _att_count,
B6.expire> NOW()
AND NOT B6.staff_id= 3 AS_locked,
(
SELECT
COUNT(R0.id) AScountFROMost_threadQ7
JOINost_ticketQ8 ON (
Q7.object_type= 'T'
AND Q7.object_id= Q8.ticket_id)
LEFT JOINost_thread_collaboratorR0 ON (Q7.id= R0.thread_id)
WHERE
Q8.ticket_id= A1.ticket_id) AS _collabs,
COALESCE(
B4.firstname, B4.lastname, B5.name,
'zzz'
) ASassigneeFROMost_ticketA1
JOINost_ticket_statusA2 ON (A1.status_id= A2.id)
LEFT JOINost_threadA3 ON (
A3.object_type= 'T'
AND A1.ticket_id= A3.object_id)
LEFT JOINost_thread_referralA4 ON (A3.id= A4.thread_id)
LEFT JOINost_staffA5 ON (
A4.object_type= 'S'
AND A4.object_id= A5.staff_id)
LEFT JOINost_threadA6 ON (
A1.ticket_id= A6.object_idAND A6.object_type= 'C'
)
LEFT JOINost_thread_referralA7 ON (A6.id= A7.thread_id)
LEFT JOINost_staffA8 ON (
A7.object_type= 'S'
AND A7.object_id= A8.staff_id)
LEFT JOINost_departmentB0 ON (
A4.object_type= 'D'
AND A4.object_id= B0.id)
LEFT JOINost_departmentB1 ON (
A7.object_type= 'D'
AND A7.object_id= B1.id)
LEFT JOINost_ticketcdataB2 ON (A1.ticket_id= B2.ticket_id)
LEFT JOINost_userB3 ON (A1.user_id= B3.id)
LEFT JOINost_staffB4 ON (A1.staff_id= B4.staff_id)
LEFT JOINost_teamB5 ON (A1.team_id= B5.team_id)
LEFT JOINost_lockB6 ON (A1.lock_id= B6.lock_id)
WHERE
A2.state= 'closed'
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.dept_idIN (4)
OR B0.idIN (4)
OR A6.object_type= 'C'
AND B1.idIN (4)
)
AND (
A1.ticket_pidIS NULL
OR A1.flags& 8 != 0
)
GROUP BY
A1.ticket_idORDER BY
A1.closedDESC
LIMIT
50;
