Help to make query more efficient
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.
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
-
What exactly are you trying to accomplish with this rule? - chucksteel 8 years ago
-
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. - jmarotto 8 years ago
-
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. - chucksteel 8 years ago
-
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. - jmarotto 8 years ago
Answers (1)
Please log in to answer
Posted by:
JasonEgg
8 years ago
> (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.
