Extremely slow performance/high load on mysql when loading agent panel/queues

simonct · Oct 27, 2020

Hi. I am having enormous performance issues with OSTicket (I have updated to 1.14.3 but this existed in the previous 2 versions as well). I have various queues, some nested 2 deep (inheriting parent properties) and around 700-800 tickets in the system, which seems to be within scope for normal operations.

Observing the processor on the server it often causes all 4 cores to max out at 100% for as long as a minute sometimes, often timing out with a "mysql server has gone away" type error. I have run a slow queries log on mysql ,below is one example which took: # Query_time: 40.598409 Lock_time: 0.007286 Rows_sent: 1 Rows_examined: 158744

I noticed there are a whopping 219 RegExp functions in there - RegExp is known for causing slow performance so this seems the likely culprit. I'd really appreciate some help as this is making our system almost unusable at times. Thanks

SELECT COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq68, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq69, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq70, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND B6.valueREGEXP '\"?73(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq71, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND B6.valueREGEXP '\"?74(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq72, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND B6.valueREGEXP '\"?75(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq73, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND B6.valueREGEXP '\"?76(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq74, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND B6.valueREGEXP '\"?77(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq75, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND B6.valueREGEXP '\"?78(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq76, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND B6.valueREGEXP '\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq77, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND B6.valueREGEXP '\"?73(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq78, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND B6.valueREGEXP '\"?74(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq79, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND B6.valueREGEXP '\"?75(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq80, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND B6.valueREGEXP '\"?76(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq81, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND B6.valueREGEXP '\"?77(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq82, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND B6.valueREGEXP '\"?78(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq83, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND B6.valueREGEXP '\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq84, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?55(\"|,|$)|\"?53(\"|,|$)|\"?54(\"|,|$)' AND NOT B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq85, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?57(\"|,|$)|\"?56(\"|,|$)|\"?58(\"|,|$)' AND NOT B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq86, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq87, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq88, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq89, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND B5.valueREGEXP '\"?61(\"|,|$)|\"?59(\"|,|$)|\"?60(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq90, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND B6.valueREGEXP '\"?73(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq91, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND B6.valueREGEXP '\"?74(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq92, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND B6.valueREGEXP '\"?75(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq93, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND B6.valueREGEXP '\"?76(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq94, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND B6.valueREGEXP '\"?77(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq95, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND B6.valueREGEXP '\"?78(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq96, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND B6.valueREGEXP '\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq97, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?65(\"|,|$)' AND NOT B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq98, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND B6.valueREGEXP '\"?73(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq99, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND B6.valueREGEXP '\"?74(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq100, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND B6.valueREGEXP '\"?75(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq101, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND B6.valueREGEXP '\"?76(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq102, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND B6.valueREGEXP '\"?77(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq103, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND B6.valueREGEXP '\"?78(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq104, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?64(\"|,|$)' AND B6.valueREGEXP '\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq105, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND B6.valueREGEXP '\"?73(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq106, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND B6.valueREGEXP '\"?74(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq107, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND B6.valueREGEXP '\"?75(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq108, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND B6.valueREGEXP '\"?76(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq109, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND B6.valueREGEXP '\"?77(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq110, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND B6.valueREGEXP '\"?78(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq111, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND B6.valueREGEXP '\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq112, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND A1.dept_id= 21 AND B5.valueREGEXP '\"?66(\"|,|$)' AND NOT B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq113, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq114, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND B6.valueREGEXP '\"?73(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq115, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND B6.valueREGEXP '\"?74(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq116, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND B6.valueREGEXP '\"?75(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq117, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND B6.valueREGEXP '\"?76(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq118, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND B6.valueREGEXP '\"?77(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq119, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND B6.valueREGEXP '\"?78(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq120, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A1.dept_id= 21 AND B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND B6.valueREGEXP '\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq121, COUNT(DISTINCT CASE WHEN A1.dept_id= 21 AND A1.dept_id= 21 AND NOT B6.valueREGEXP '\"?73(\"|,|$)|\"?74(\"|,|$)|\"?75(\"|,|$)|\"?76(\"|,|$)|\"?78(\"|,|$)|\"?77(\"|,|$)|\"?79(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq122, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq5, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND A1.isanswered= '0' AND A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq21, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND A1.staff_idIN (1) AND A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq6, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND NOT A1.staff_idIN (1) AND A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq7, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND A2.idIN (6) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq18, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND A2.idIN (8) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq61, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND A2.idIN (2) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq19, COUNT(DISTINCT CASE WHEN (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND (A1.team_idIN (1, 2, 3, 4) OR A1.staff_idIN (1)) AND A2.idIN (3, 7) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq20, COUNT(DISTINCT CASE WHEN NOT A2.idIN (2, 3) AND A1.team_id= 0 AND A1.staff_id= 0 AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq23, COUNT(DISTINCT CASE WHEN A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq1, COUNT(DISTINCT CASE WHEN A2.state= 'open' AND A2.state= 'open' AND NOT (A1.team_id= 0 AND A1.staff_id= 0) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq2, COUNT(DISTINCT CASE WHEN A2.state= 'open' AND A2.state= 'open' AND A1.isanswered= '1' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq3, COUNT(DISTINCT CASE WHEN A2.state= 'open' AND A2.state= 'open' AND A1.isoverdue= '1' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq4, COUNT(DISTINCT CASE WHEN A2.state= 'closed' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq8, COUNT(DISTINCT CASE WHEN A2.state= 'closed' AND A2.state= 'closed' AND A1.closedBETWEEN '2020-10-27 00:00:00' AND '2020-10-27 23:59:59' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq9, COUNT(DISTINCT CASE WHEN A2.state= 'closed' AND A2.state= 'closed' AND A1.closedBETWEEN '2020-10-26 00:00:00' AND '2020-10-26 23:59:59' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq10, COUNT(DISTINCT CASE WHEN A2.state= 'closed' AND A2.state= 'closed' AND A1.closedBETWEEN '2020-10-26 00:00:00' AND '2020-11-01 23:59:59' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq11, COUNT(DISTINCT CASE WHEN A2.state= 'closed' AND A2.state= 'closed' AND A1.closedBETWEEN '2020-09-30 23:00:00' AND '2020-10-31 23:59:59' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq12, COUNT(DISTINCT CASE WHEN A2.state= 'closed' AND A2.state= 'closed' AND A1.closedBETWEEN '2020-09-30 23:00:00' AND '2020-12-31 23:59:59' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq13, COUNT(DISTINCT CASE WHEN A2.state= 'closed' AND A2.state= 'closed' AND A1.closedBETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq14, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq24, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq25, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.team_id= 0 AND A1.staff_id= 0 AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq29, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.isoverdue= '1' AND A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq57, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A2.idIN (1) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq30, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A2.idIN (6) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq31, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A2.idIN (2) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq32, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A2.idIN (3, 7) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq33, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A2.state= 'deleted' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq50, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (4, 16, 17, 15, 18) AND A2.state= 'archived' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq49, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq26, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.team_id= 0 AND A1.staff_id= 0 AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq34, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.isoverdue= '1' AND A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq58, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A2.idIN (1) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq35, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A2.idIN (6) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq36, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A2.idIN (2) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq37, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A2.idIN (3, 7) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq38, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A2.state= 'archived' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq51, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (8, 9, 7, 10) AND A2.state= 'deleted' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq52, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq27, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.team_id= 0 AND A1.staff_id= 0 AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq39, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.isoverdue= '1' AND A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq59, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A2.idIN (1) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq40, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A2.idIN (6) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq41, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A2.idIN (2) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq42, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A2.idIN (3, 7) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq43, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A2.state= 'archived' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq53, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_idIN (3, 12, 13, 11, 14) AND A2.state= 'deleted' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq54, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq28, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.team_id= 0 AND A1.staff_id= 0 AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq44, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.isoverdue= '1' AND A2.state= 'open' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq60, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A2.idIN (1) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq45, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A2.idIN (6) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq46, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A2.idIN (2) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq47, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A2.idIN (3, 7) AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq48, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A2.state= 'archived' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq55, COUNT(DISTINCT CASE WHEN A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A1.sourceREGEXP '\"?Phone(\"|,|$)|\"?Email(\"|,|$)|\"?Web(\"|,|$)|\"?API(\"|,|$)|\"?Other(\"|,|$)' AND A1.dept_id= 19 AND A2.state= 'deleted' AND A3.object_type= 'T' THEN A1.ticket_idEND) ASq56FROMost_ticketA1 JOINost_ticket_statusA2 ON (A1.status_id= A2.id) LEFT JOINost_threadA3 ON (A3.object_type= 'T' AND A1.ticket_id= A3.object_id) LEFT JOINost_thread_referralA4 ON (A3.id= A4.thread_id) LEFT JOINost_staffA5 ON (A4.object_type= 'S' AND A4.object_id= A5.staff_id) LEFT JOINost_threadA6 ON (A1.ticket_id= A6.object_idAND A6.object_type= 'C') LEFT JOINost_thread_referralA7 ON (A6.id= A7.thread_id) LEFT JOINost_staffA8 ON (A7.object_type= 'S' AND A7.object_id= A8.staff_id) LEFT JOINost_teamB0 ON (A4.object_type= 'E' AND A4.object_id= B0.team_id) LEFT JOINost_teamB1 ON (A7.object_type= 'E' AND A7.object_id= B1.team_id) LEFT JOINost_departmentB2 ON (A4.object_type= 'D' AND A4.object_id= B2.id) LEFT JOINost_departmentB3 ON (A7.object_type= 'D' AND A7.object_id= B3.id) JOINost_form_entryB4 ON (B4.object_type= 'T' AND A1.ticket_id= B4.object_id) LEFT JOINost_form_entry_valuesB5 ON (B4.id= B5.entry_id) LEFT JOINost_form_entry_valuesB6 ON (B4.id= B6.entry_id) WHERE ((A2.state= 'open' AND (A1.staff_id= 1 OR A5.staff_id= 1 OR A6.object_type= 'C' AND A8.staff_id= 1 OR A1.team_idIN (1, 2, 3, 4) OR B0.team_idIN (1, 2, 3, 4) OR A6.object_type= 'C' AND B1.team_idIN (1, 2, 3, 4))) OR (A2.stateIN ('open', 'closed') AND (A1.dept_idIN (1, 3, 4, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21) OR B2.idIN (1, 3, 4, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21))) OR A6.object_type= 'C' AND B3.idIN (1, 3, 4, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21));

KevinTheJedi · Oct 28, 2020

@simonct

There is a workaround for this that we might be implementing in core soon. Essentially, the below patch allows for individual Queue Counts instead of running one query for all Queue Counts:

diff --git a/include/class.search.php b/include/class.search.php
index 1238347..02e8878 100755
--- a/include/class.search.php
+++ b/include/class.search.php
@@ -990,12 +990,9 @@ class SavedQueue extends CustomQueue {
                 $counts['q'.$queue->getId()] = '-';
         }
 
-        try {
-            $counts = array_merge($counts, $query->values()->one());
-        }  catch (Exception $ex) {
-            foreach ($queues as $q)
-                $counts['q'.$q->getId()] = $q->getTotal();
-        }
+        // Opt for individual queue counts - it's much faster in large datasets
+        foreach ($queues as $q)
+            $counts['q'.$q->getId()] = $q->getTotal();
 
         // Always cache the results
         self::storeCounts($key, $counts, $ttl);

The idea behind the one query for all queue counts is so we don't make a ton of trips to the database. BUT this creates a problem for people like you with a ton of custom queues. We are debating on if we should implement the above patch to core so please run some tests before and after applying the changes and provide your feedback.

Cheers.

KevinTheJedi · Oct 28, 2020

@simonct

Also, the standard, built-in queue configurations don't use REGEXP so it must be custom configurations you have on the queues (eg. Ticket Status / Name => contains => Status1, Status2, Status3).

Cheers.

simonct · Oct 29, 2020

Wow, what a difference. Night and day in fact. This has gone from almost unusable on a 4 core (2.5ghz clock) server with 8GB RAM running Ubuntu 18.04 server and Apache/MySQL, to almost instantaneous loading of all pages/tickets. When entering a particularly large queue occasionally it will take up to a about 1/2 a second to load, but that's absolutely nothing and totally within acceptable performance. This is compared with the same operations yesterday which were taking upwards of 40 seconds.

I have asked my team to comment on performance during their use over the next days but from what I am seeing initially this is a gigantic improvement. Regarding implementing this in the core for future release, whilst in my case it has fixed the situation completely, in others (especially with the default queues only, as you say) it could of course hurt performance. I suppose the ideal solution would be to make this an option in the system configuration (either in the UI or the ost-config file, although I suspect many would miss this if it was only set there). Perhaps a solution in which OSTicket prompts an admin to set this mode once it detects the sort of queues which could be problematic (ie. all the RegExp tests) would be ideal, if this is feasible?

Thank you so much for your help and this fantastic software, I really appreciate it.

dpou · Feb 24, 2021

You should watch Engine in tables: https://forum.osticket.com/d/99041-myisam-to-innodb

riccardo_centropaghe · Apr 20, 2021

I confirm....the difference in speed is awesome! I have a lot of queues and a lot of tickets...now osticket is flying!

rorymaher · May 5, 2021

I need this in the next release, instant improvement for all users of my deployment. It was taking around 45 seconds for a user with lots of custom searches to even authenticate and halted the system for all other users.

After implementing this code change it's working beautifully, perhaps this could be an option we could toggle in the config so we can decide whether we want to be conservative with DB queries or not?

Extremely slow performance/high load on mysql when loading agent panel/queues

Ssimonct

KevinTheJedi

KevinTheJedi

Ssimonct

Ddpou

Rriccardo_centropaghe

rorymaher