开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜