CODEWITHSUNDEEP
phpmysqlquery-optimization
Zachery Delafosse
Zachery Delafosse

Reputation: 312

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 multiple 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.

Upvotes: 0

Views: 232

Answers (1)

Peter Tillemans
Peter Tillemans

Reputation: 35341

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.

Upvotes: 4

Related Questions