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
