Thanks for your support.
As per the link you provided, we have made the recommended changes in our application. However, we are still facing the same slowness issue.
Before making the changes, I noticed a large SELECT query in the output of the SHOW PROCESSLIST command (which I had already shared with you earlier).
After implementing the changes from the GitHub link you shared, I now observe a smaller SELECT query in the SHOW PROCESSLIST output (details below). Still, we are experiencing application hanging issues, and the application is going down. Could you please help us resolve this issue? It would be very helpful for us.
After Changes Query:
(SELECT B3.address AS userdefault_emailaddress, B4.subject AS cdatasubject, B2.name AS username, A1.ticket_id AS ticket_id, A3.id AS threadid, A1.flags AS flags, A1.number AS number, 1 AS tickets, COUNT(DISTINCT B5.id) AS tasks, COUNT(DISTINCT B6.id) AS collaborators, COUNT(DISTINCT B7.id) AS entries 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_user B2 ON (A1.user_id = B2.id) Left JOIN ost_user_email B3 ON (B2.default_email_id = B3.id) INNER JOIN ost_ticketcdata B4 ON (A1.ticket_id = B4.ticket_id) Left JOIN ost_task B5 ON (A1.ticket_id = B5.object_id) Left JOIN ost_thread_collaborator B6 ON (A3.id = B6.thread_id) Left JOIN ost_thread_entry B7 ON (A3.id = B7.thread_id) WHERE ((A2.state IN ('open', 'closed') AND (A1.staff_id = 124 OR A5.staff_id = 124 OR A6.object_type = 'C' AND A8.staff_id = 124)) OR A1.dept_id IN (1, 2, 4, 5, 6, 7, 8) OR B0.id IN (1, 2, 4, 5, 6, 7, 8) OR A6.object_type = 'C' AND B1.id IN (1, 2, 4, 5, 6, 7, 8)) AND A1.number LIKE 'o4132@os.hughes.in%' GROUP BY A1.ticket_id ORDER BY A1.number ASC) UNION ALL (SELECT B3.address AS userdefault_emailaddress, B4.subject AS cdatasubject, B2.name AS username, A1.ticket_id AS ticket_id, A3.id AS threadid, A1.flags AS flags, null AS number, COUNT(DISTINCT A1.ticket_id) AS tickets, COUNT(DISTINCT B5.id) AS tasks, COUNT(DISTINCT B6.id) AS collaborators, COUNT(DISTINCT B7.id) AS entries 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_user B2 ON (A1.user_id = B2.id) Left JOIN ost_user_email B3 ON (B2.default_email_id = B3.id) INNER JOIN ost_ticketcdata B4 ON (A1.ticket_id = B4.ticket_id) Left JOIN ost_task B5 ON (A1.ticket_id = B5.object_id) Left JOIN ost_thread_collaborator B6 ON (A3.id = B6.thread_id) Left JOIN ost_thread_entry B7 ON (A3.id = B7.thread_id) JOIN (SELECT COALESCE(Z3.object_id, Z5.ticket_id, Z8.ticket_id) as ticket_id, Z1.relevance FROM (SELECT Z1.object_id, Z1.object_type, MATCH (Z1.title, Z1.content) AGAINST ('o4132@os.hughes.in' IN NATURAL LANGUAGE MODE) AS relevance FROM ost__search Z1 WHERE MATCH (Z1.title, Z1.content) AGAINST ('o4132@os.hughes.in' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC) Z1 LEFT JOIN ost_thread_entry Z2 ON (Z1.object_type = 'H' AND Z1.object_id = Z2.id) LEFT JOIN ost_thread Z3 ON (Z2.thread_id = Z3.id AND (Z3.object_type = 'T' OR Z3.object_type = 'C')) LEFT JOIN ost_ticket Z5 ON (Z1.object_type = 'T' AND Z1.object_id = Z5.ticket_id) LEFT JOIN ost_user Z6 ON (Z6.id = Z1.object_id and Z1.object_type = 'U') LEFT JOIN ost_organization Z7 ON (Z7.id = Z1.object_id AND Z7.id = Z6.org_id AND Z1.object_type = 'O') LEFT JOIN ost_ticket Z8 ON (Z8.user_id = Z6.id)) Z1 WHERE ((A2.state IN ('open', 'closed') AND (A1.staff_id = 124 OR A5.staff_id = 124 OR A6.object_type = 'C' AND A8.staff_id = 124)) OR A1.dept_id IN (1, 2, 4, 5, 6, 7, 8) OR B0.id IN (1, 2, 4, 5, 6, 7, 8) OR A6.object_type = 'C' AND B1.id IN (1, 2, 4, 5, 6, 7, 8)) AND A1.ticket_id = Z1.ticket_id GROUP BY B3.address ORDER BY Z1.relevance LIMIT 25) LIMIT 25