UPDATE all or UPDATE WHERE?

Hi,

I have a table with a column that contains only 0 or 1. e.g.
username .... drank coffee
me ...............0
Tom .............1
John .............0

I set a cron job to update set all 1s into 0.

I don't add index on column drankcoffee, which query should i use:

UPDATE userboard SET drankcoffee=0;

Code (markup):

or

UPDATE userboard SET drankcoffee=0 WHERE drankcoffee=1;

Code (markup):

 

I think that the first query will be OK.

 

Why?

I think the first query will make mysql crawl all tables and update all records, one by one.
The second query: search all tables, but update only some records.

 

In my opinion, definitely the second one. Depending on the size of your database you should be trying to lower any stress on it so I'd go for the second as it should update less rows, as you said

 

I would use the second one for efficiency. Not to mention, if you decide you want other numbers (2,4,5 what have you) to be values as well, the first one will always update every entry.

Regards,
Dennis M.

 

Thank you very much Rep +

 

The second one is better in regard to the amount of work that will have to be done. Its always better to reduce the work load and get the work done in the most efficient and the best way.

 

The first query will work.

 

Both WILL work.

But use the 2nd one Querying as little rows as possible makes the query faster!

 

Both of them worked. But the 2nd one is better, faster because it just change some query. If you have little data, you can not figure out the difference, but when you have thousand data, it will show you the difference

 

wow, suggestions all over the board. Guess some of you missed that he doesn't have an index on the column so specifying won't matter, it's still going to do a table scan. Also, it takes more effort to check and replace than to simply replace.

Point being unless the optimizer is doing a trick with it the first query is just as good, if not slightly better, than the second.

 

I think second will work.

 

You can use both, Unless you have other requirements like minimize disk access / minimize speed / minimize CPU load / best for concurrent usage.
If you have those requirements, then you can run a benchmark which of the two is better in terms of your requirements.
Then, if you can't explain them, you could ask the intellect on this board to explain the difference.

At the moment this thread doesn't contribute to anything, since you don't specify the requirements for which it should be better, and you did not even perform some internal benchmarks.