MYSQL Performance Ninja
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:
Select Query
_________
select
HD_TICKET.*,
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
from
HD_TICKET
left outer join USER owner on owner.ID = HD_TICKET.OWNER_ID
left outer join USER submitter on submitter.ID =
HD_TICKET.SUBMITTER_ID
where
LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE9) > 0
_________
Update Query:
_______________
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.SUBMITTER_ID='10',
HD_TICKET.OWNER_ID = 'TC.USER_ID',
HD_TICKET.CUSTOM_FIELD_VALUE9 = ''
where
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in (<TICKET_IDS>))
________________
3 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
Selecting * along with OWNER and SUBMITTER is redundant unless you're sending an email to each of those participants.
OUTER JOINS are slower than all others.
LENGTH, in relation to the line you have in the select query isn't needed.
The select query should look for tickets in the specific queue the tickets are in, as in WHERE HD_TICKET.HD_QUEUE_ID=5. This select/update is forcing the kbox to look at every ticket in the system.
I'm not sure I understand why you're setting the submitter and the owner of the ticket via the update statement.
Your UPDATE statement is locking four tables when it runs. If there are other queues in action it must wait for any other ticket rules to complete prior to running.
A better approach would be to find a way to populate the ticket fields prior to selecting the Quick Close so that the only field being updated is the STATUS. - jmarotto 10 years ago