sql UPDATE, a calculation is used multiple times, can it just be calculated once?
Using:
UPDATE `play`
SET `counter1` = `counter1` + LEAST(`maxchange`, FLOOR(`x` / `y`) ),
`counter2` = `counter2` - LEAST(`maxchange`, FLOOR(`x` / `y`) ),
`x` = MOD(`x`, `y`)
WHERE `x` > `y`
AND `maxchange` > 0
As you can see, LEAST(maxchange, FLOOR(x / y) )
is used mul开发者_如何学Gotiple times, but it should always have the same value. Is there a way to optimize this, to only calculate once?
I'm coding this in PHP, for the record.
The database engine query optimizer should optimize that away.
If you want to be sure check the explain plan. I do not think MySQL supports explaining updates, but it uses the same query optimizer as SELECT so you might have to translate it to a select.
精彩评论