[Resolved] Which is faster?

[Dolemite50]

Hi,

If I need to set a field for an entire column in a DB to 0 is it faster to check to see which aren't 0 first and just update those?

Should I use:
$sql7 = "UPDATE songs SET monthly_plays = 0 WHERE monthly_plays != 0";
or
$sql7 = "UPDATE songs SET monthly_plays = 0";

Thanks for any advice.

[Dolemite50]

Oops, I have a little to add to that,..

What if I have two columns that I need to set to 0?

What would be the syntax here?

Should I use:

$sql7 = "UPDATE songs SET monthly_plays = 0, weekly_plays=0";

??

[kburger]

Originally posted by Dolemite50
Should I use:
$sql7 = "UPDATE songs SET monthly_plays = 0 WHERE monthly_plays != 0";
or
$sql7 = "UPDATE songs SET monthly_plays = 0";

I think the second one will be faster since MySQL doesn't have to do any comparisons.

[Dolemite50]

Thanks. 🙂

[kburger]

I don't know that to be true, I'm just guessing.

"One test is worth a thousand expert opinions."

You might try running those 2 queries at the command line or using something like PHPMyAdmin on identical tables and see how long they take.

[bradgrafelman]

Query: UPDATE speed_test1 SET monthly_plays = 0 WHERE monthly_plays != 0
Time: 0.0235832 seconds.

Query: UPDATE speed_test2 SET monthly_plays = 0
Time: 0.0013371 seconds.

speed_test1 and speed_test2 were identical tables filled with 100 rows of incremental numbers. Structure was as follows:

CREATE TABLE speed_test1 (
monthly_plays int(11) NOT NULL default '0',
fake_field varchar(4) NOT NULL default 'fake'
) TYPE=MyISAM;