MYSQL Performance Ninja
09/22/2014 1682 views
I am looking for a mysql performance ninja.
Situation: we have a service desk queue with a dropdown for quick close, which runs hours. When you select quick close a custom service desk ticket rule will run and close out a ticket for you and basically market it is irrelevant; we use this for server status tickets.
Problem: the query takes a long time to run. For example, ran on 36 hours and it took 10 minutes.
Can someone make suggestions to improve the cost of these queries:
owner.EMAIL as OWNER_EMAIL,
if ((LENGTH(owner.FULL_NAME) = 0), owner.USER_NAME,
owner.FULL_NAME) as OWNER_NAME,
submitter.EMAIL as SUBMITTER_EMAIL
left outer join USER owner on owner.ID = HD_TICKET.OWNER_ID
left outer join USER submitter on submitter.ID =
LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE9) > 0
update HD_TICKET, HD_CATEGORY as T5, HD_STATUS as T6, USER as T8
set HD_TICKET.HD_CATEGORY_ID = T5.ID,
HD_TICKET.HD_STATUS_ID = '55',
HD_TICKET.HD_CATEGORY_ID = '116',
HD_TICKET.TIME_OPENED = IF(T6.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED = IF(T6.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T6.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.CUSTOM_FIELD_VALUE3 = 'Information Only',
HD_TICKET.CUSTOM_FIELD_VALUE0 = 'IT DEPT',
HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Main Building',
HD_TICKET.OWNER_ID = 'TC.USER_ID',
HD_TICKET.CUSTOM_FIELD_VALUE9 = ''
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in (<TICKET_IDS>))
Be the first to answer this question