Query and processing questions.

[iceflyin]

It it possible to lower a value across 10mil+ rows simultaneously like this?

Psedocode: "UPDATE myTable SET myValue = myValue - 1;"

Also, what would be the approximate processing time? Quad core dedicated.


Might it be more efficient to use this instead?
"UPDATE myTable SET myValue = myValue - 1 WHERE myValue > 0;"

This is going to be a once a day midnight chron job.

[Andrew Johnson]

Realistically, if it must be done then does processing time even matter?

It depends on what you wanna do to decide which query to use. If you don't want `myValue` dropping into the negatives then use

UPDATE myTable SET myValue=myValue-1 WHERE myValue>0

If you don't care about negative values then use

UPDATE myTable SET myValue=myValue-1

[iceflyin]

If your using UNSIGNED, it shouldn't drop into negatives anyway, right?

Also, probably only 30% of the values are going to ever go above 0 during one day.

[StupidRalph]

If you're using UNSIGNED then they will stay at 0. I have 2.6 million rows and I'm able to return a few hundred rows performing a SELECT using a WHERE clause in < 800ms. But I'm sure that using an UPDATE query will be a bit longer since it has to WRITE to the table.