I have a table with ~10,000 records.
There is a "viewed" column which contains either 0 or 1.
At some point in my code I want all rows = 1 for the "viewed" column.
Does it matter if I set all rows to 1 or set all rows to 1 that = 0?
For example, is there a performance issue or something else to consider between the 2:
UPDATE table SET viewed = 1;
and
UPDATE table SET viewed = 1 WHERE viewed = 0;
Since Mysql keeps track of the number of changed rows (which it displays after every update) I assume that the system retrieves the current field values before updating them in both cases, so the unconditional update (for all rows) should be slower than the filtered one. This would also depend on index usage, I assume. Did you time your updates? With which results?
Since Mysql keeps track of the number of changed rows (which it displays after every update) I assume that the system retrieves the current field values before updating them in both cases, so the unconditional update (for all rows) should be slower than the filtered one. This would also depend on index usage, I assume. Did you time your updates? With which results?
Hmmmm...
I'm doing all this through a PHP webpage, so I don't think number of changed rows are being displayed, though they may be tracked somewhere by MySQL.
I don't think the system is retrieving current field values before updating them...unless that is just what MySQL does with those UPDATE statements.
I did not time the updates because:
1) I'm not sure how.
2) seems like seeing how long it takes for the webpage to finish loading has to many other variables and will not be consistent from try to try.
I think putting an index on the viewed column, and then using the second query (with the where) should be fastest. I don't have any hard data, but as smantscheff said, to be sure, run some tests. You can create a copy of your table, and then run the query from mysql a couple of times. It should show you how long the query runs.
If you want any efficiency drop the PHP interface. Use mysql from the command line to perform your tests. If you don't know it, learn how to use it.
For timing your test use the linux "time" command as in time (echo 'update myTable set myField=myValue' | mysql myDatabase -umyUsername -pmyPassword)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.
