Hi Team,
I have found some mySql select query which are cause of system Hang. Please Help how to fixed this issue.
(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
