Hi Team,
Occasionally, our OS Ticket application experiences hangs, preventing agents from logging in due to blocking. When this occurs, I execute the "SHOW FULL PROCESSLIST;" command in MySQL and find specific SELECT queries that appear to be the cause.
(
SELECT B5.address AS user__default_email__address
,B6.subject AS cdata__subject
,B4.name AS user__name
,A1.ticket_id AS ticket_id
,A3.id AS thread__id
,A1.flags AS flags
,A1.number AS number
,1 AS tickets
,COUNT(DISTINCT B7.id) AS tasks
,COUNT(DISTINCT B8.id) AS collaborators
,COUNT(DISTINCT C0.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_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_user B4 ON (A1.user_id = B4.id)
LEFT JOIN ost_user_email B5 ON (B4.default_email_id = B5.id)
LEFT JOIN ost_ticket__cdata B6 ON (A1.ticket_id = B6.ticket_id)
LEFT JOIN ost_task B7 ON (A1.ticket_id = B7.object_id)
LEFT JOIN ost_thread_collaborator B8 ON (A3.id = B8.thread_id)
LEFT JOIN ost_thread_entry C0 ON (A3.id = C0.thread_id)
WHERE (
(
A2.state IN (
'open'
,'closed'
)
AND (
A1.staff_id = 8
OR A5.staff_id = 8
OR A6.object_type = 'C'
AND A8.staff_id = 8
OR A1.team_id IN (
1
,2
,5
,4
)
OR B0.team_id IN (
1
,2
,5
,4
)
OR A6.object_type = 'C'
AND B1.team_id IN (
1
,2
,5
,4
)
)
)
OR A1.dept_id IN (
1
,2
,4
,5
,6
,7
,8
)
OR B2.id IN (
1
,2
,4
,5
,6
,7
,8
)
OR A6.object_type = 'C'
AND B3.id IN (
1
,2
,4
,5
,6
,7
,8
)
)
AND A1.number LIKE '20 @0Z@ Cancellation document number of contract partner changed CCC-154217%'
GROUP BY A1.ticket_id
ORDER BY A1.number ASC
)
UNION ALL
(
SELECT B5.address AS user__default_email__address
,B6.subject AS cdata__subject
,B4.name AS user__name
,A1.ticket_id AS ticket_id
,A3.id AS thread__id
,A1.flags AS flags
,NULL AS number
,COUNT(DISTINCT A1.ticket_id) AS tickets
,COUNT(DISTINCT B7.id) AS tasks
,COUNT(DISTINCT B8.id) AS collaborators
,COUNT(DISTINCT C0.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_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_user B4 ON (A1.user_id = B4.id)
LEFT JOIN ost_user_email B5 ON (B4.default_email_id = B5.id)
LEFT JOIN ost_ticket__cdata B6 ON (A1.ticket_id = B6.ticket_id)
LEFT JOIN ost_task B7 ON (A1.ticket_id = B7.object_id)
LEFT JOIN ost_thread_collaborator B8 ON (A3.id = B8.thread_id)
LEFT JOIN ost_thread_entry C0 ON (A3.id = C0.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('20 @0Z@ Cancellation document number of contract partner changed CCC-154217' IN NATURAL LANGUAGE MODE) AS relevance
FROM ost__search Z1
WHERE MATCH(Z1.title, Z1.content) AGAINST('20 @0Z@ Cancellation document number of contract partner changed CCC-154217' 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 = 8
OR A5.staff_id = 8
OR A6.object_type = 'C'
AND A8.staff_id = 8
OR A1.team_id IN (
1
,2
,5
,4
)
OR B0.team_id IN (
1
,2
,5
,4
)
OR A6.object_type = 'C'
AND B1.team_id IN (
1
,2
,5
,4
)
)
)
OR A1.dept_id IN (
1
,2
,4
,5
,6
,7
,8
)
OR B2.id IN (
1
,2
,4
,5
,6
,7
,8
)
OR A6.object_type = 'C'
AND B3.id IN (
1
,2
,4
,5
,6
,7
,8
)
)
AND A1.ticket_id = Z1.ticket_id
GROUP BY B5.address
ORDER BY Z1.relevance LIMIT 25
) LIMIT 25
