Hi Kevin
Today we got error on slow query as below.
SET timestamp=1685024758;
SELECT A1.number
AS number
, A1.ticket_id
AS ticket_id
, A1.source
AS source
, A1.lastupdate
AS lastupdate
, B2.subject
AS cdata__subject
, A1.isoverdue
AS isoverdue
, B3.name
AS user__name
, B2.priority
AS cdata__priority
, A1.dept_id
AS dept_id
, B4.name
AS dept__name
, A2.id
AS status__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, B5.expire
> NOW() AND NOT B5.staff_id
= 161 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 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_department
B4 ON (A1.dept_id
= B4.id
) LEFT JOIN ost_lock
B5 ON (A1.lock_id
= B5.lock_id
) WHERE A1.staff_id
IN (161) AND A2.state
= 'open' AND A1.staff_id
IN (161) AND A2.state
= 'open' AND A1.staff_id
IN (161) AND ((A2.state
= 'open' AND (A1.staff_id
= 161 OR A5.staff_id
= 161 OR A6.object_type
= 'C' AND A8.staff_id
= 161)) OR A1.dept_id
IN (1, 2, 4, 6, 7, 9, 10, 11, 12, 13, 14, 33, 34, 41, 48) OR B0.id
IN (1, 2, 4, 6, 7, 9, 10, 11, 12, 13, 14, 33, 34, 41, 48) OR A6.object_type
= 'C' AND B1.id
IN (1, 2, 4, 6, 7, 9, 10, 11, 12, 13, 14, 33, 34, 41, 48)) AND (A1.ticket_pid
IS NULL OR A1.flags
& 8 != 0) GROUP BY A1.ticket_id
ORDER BY A1.created
DESC LIMIT 50;
User@Host: iobadmin[iobadmin] @ localhost [::1]
Thread_id: 16 Schema: iqubecms QC_hit: No
Query_time: 3.473918 Lock_time: 0.105671 Rows_sent: 40 Rows_examined: 3126
Rows_affected: 0 Bytes_sent: 6453
SET timestamp=1685025744;
SELECT COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q1
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.isanswered
= '0' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q2
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.isanswered
= '1' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q3
, COUNT(DISTINCT CASE WHEN A2.state
= 'open' AND A2.state
= 'open' AND A1.isoverdue
= '1' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q4
, COUNT(DISTINCT CASE WHEN A1.staff_id
IN (40) AND A2.state
= 'open' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q5
, COUNT(DISTINCT CASE WHEN A1.staff_id
IN (40) AND A2.state
= 'open' AND A1.staff_id
IN (40) AND A2.state
= 'open' AND A1.staff_id
IN (40) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q6
, COUNT(DISTINCT CASE WHEN A1.staff_id
IN (40) AND A2.state
= 'open' AND A1.staff_id
IN (40) AND A2.state
= 'open' AND NOT A1.staff_id
IN (40) AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q7
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q8
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2023-05-25 00:00:00' AND '2023-05-25 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q9
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2023-05-24 00:00:00' AND '2023-05-24 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q10
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2023-05-22 00:00:00' AND '2023-05-28 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q11
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2023-05-01 00:00:00' AND '2023-05-31 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q12
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2023-04-01 00:00:00' AND '2023-06-30 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q13
, COUNT(DISTINCT CASE WHEN A2.state
= 'closed' AND A2.state
= 'closed' AND A1.closed
BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND A3.object_type
= 'T' THEN A1.ticket_id
END) AS q14
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
) WHERE ((A2.state
= 'open' AND (A1.staff_id
= 40 OR A5.staff_id
= 40 OR A6.object_type
= 'C' AND A8.staff_id
= 40)) OR (A2.state
IN ('open', 'closed') AND (A1.dept_id
IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 27, 33, 34, 41, 48, 51) OR B0.id
IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 27, 33, 34, 41, 48, 51))) OR A6.object_type
= 'C' AND B1.id
IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 27, 33, 34, 41, 48, 51));
Time: 230525 16:42:26
User@Host: iobadmin[iobadmin] @ localhost [::1]
Thread_id: 171 Schema: iqubecms QC_hit: No
Query_time: 2.897504 Lock_time: 0.000391 Rows_sent: 1 Rows_examined: 514062
Rows_affected: 0 Bytes_sent: 479