efficient update queries

Anon

Does UPDATE automatically checks to see if the data has changed before actually writing anything into the database (MySQL in question here)

For example, which is more efficient:

function update($a, $b, ..., $bar) {
mysql_query("UPDATE Foo SET A = '$a', B = '$b', ... WHERE Bar = $bar");
}

function update($a, $b, ...) {
mysql_query("SELECT A, B, ... WHERE Bar = $bar");

...

if(A different) {
mysql_query("UPDATE Foo SET A = '$a' WHERE Bar = $bar");
}
if(B different) {
mysql_query("UPDATE Foo SET B = '$b' WHERE Bar = $bar");
}
if(...) {
...
}
}

if most of the data in the row changes, then the first function should be more efficient, however, if only one field changes, is the second function more efficient?

[deleted]

The way I do it is:

$query="update table set a=$newa where id=$id and a!=$newa";
or, if you have it,
$query="update table set a=$newa where id=$id and a=$olda";

then, if you're using transactions, you can rollback if the number of rows affected is zero, or, if it should only be updating one row, if it's greater than one.

[deleted]

Uhm.. that doesn't make any sense 🙂
You are asking the database to update a record but only if the new data is different from the old data. That means the database must first read the old data and compare it to the new before it can update, and the end result is the same as with a straight update.

If you want an efficient update, just do the update and let the database worry about what it should do with the data.

Never try to do the database's work.

BTW. What's the point in starting a transaction, doing a bunch of conditional updates, and then decide to rollback if there were no changes? that just takes even longer. (on a good database, the rollback takes more time than the commit)

Anon

Hmn. Good point, Vincent.

So what if you're working with a database with no referential integrity, such as MySQL? That's the reason I've been handling the conditional rollbacks in the application rather than the database layer.

[deleted]

Referencial integrety is a completely different thing (a livesaver, required functionality in any database, that's probably why crappy MySQL doesn't have it)

An efficient update is just 'update' with nothing else, as simple as possible.

If you want to be sure that all the updates you run are either succesfull as a group or fail as a group, then you'd need transactions (now also available for MYSQL, about 5 years too late).

You can't really do transactions outside the DB no matter how hard you try, because you don't know if a query will work untill you execute it. You can safeguard against the most common mistakes (such as referencial integrety if you like to lock your tables a lot) but there will always be a few things that you cannot check for without 'being' the database

Anon

...And that's why I do that weird 'check first' update. I'm programming a groupware app right now where the possibilty exists in some updates that someone has already done the update while another user was sitting there staring at the page.

So I'll concede your point that it's not an efficient update. But efficiency is useless if your data gets goobered.