/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Help to make query more efficient

12/14/2015 952 views
We upgraded our kbox over the weekend, 6.3 > 6.4. Still dealing with the fallout *sigh*.
One major issue we dealt with was query (update statement) in our ticketing that the newer version of MySQL didn't like. Unfortunately for me it's a ticket rule for routing and we have 50+ across all of our queues.  I did manage to fix it on the fly but the resolution is sloppy and inefficient, so I'd like to clean it up if at all possible.

The UPDATE that was failing began like this -

update HD_TICKET set HD_TICKET.OWNER_ID =
((select DISTINCT USER_ID from (select O.ID USER_ID, O.USER_NAME, (SELECT MAX(CREATED) FROM HD_TICKET WHERE OWNER_ID = O.ID AND CREATED > CURDATE() GROUP BY OWNER_ID) MAXC
 from HD_QUEUE Q . . .
 
To correct the issue I modified it to this -

update HD_TICKET set HD_TICKET.OWNER_ID =
((select DISTINCT USER_ID from (select O.ID USER_ID, O.USER_NAME, (SELECT MAX(CREATED) FROM (SELECT HD_TICKET.* FROM HD_TICKET) AS FIX WHERE OWNER_ID = O.ID AND CREATED > CURDATE() GROUP BY OWNER_ID) MAXC
from HD_QUEUE Q . . .

This change stopped the errors and tickets are routing appropriately but I do not like using SELECT * for, anything, really. Here is the page I found the solution on -
http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

Oddly enough, this is from 2006 but it helped me resolve the issue quickly (sort of) this morning.

Any suggestions would be appreciated, thank you.
3 Comments   [ + ] Show comments

Comments

  • What exactly are you trying to accomplish with this rule?
  • The update assigns an Owner to a ticket selected from a pool of available owners. I only posted the problem code from the update statement because it's the piece that is relevant to the problem. We've been using this specific code since 5.2.
    • And do you want the selected owner to be random or the person with the fewest tickets? You're selecting MAX(CREATED) from tickets maybe? I'm trying to understand your statement so I can see if there is another way to come at the problem.
  • It's a random selection from the pool of available agents. There are additional conditions in the update that provide a way to exclude agents in a particular label, as in a OUT OF OFFICE label or what have you. I'm going to play with this a bit more. I'll keep you posted.

All Answers

0
> (SELECT HD_TICKET.* FROM HD_TICKET)  
It seems there's no way around adding this sub-select, especially according to the link you posted. Maybe there's a more efficient fix but I don't know SQL well enough to speak to this exact problem. So AFAIK the only way to optimize the query would be to refine the sub-select you had to add. You can do this by (1) selecting only the specific columns the query needs and/or (2) including a WHERE clause. Sorry I don't have any other suggestions.

Answered 12/15/2015 by: JasonEgg
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ