1

I am trying to update 3 column values in a row in mysql only if any of the 3 values is different.

Say I have a table of

x,y,z,id columns

I have currently,

Method A

update foo set x = 'x_value', y = 'y_value', z = 'z_value' where id = 'unique_id'
and ((x <> 'x_value') or (y <> 'y_value') or (z <> 'z_value'))

I don't know much about the theoretical benchmarking/architecture of mysql, and I was wondering if the statements

Method B

update foo set x ='x_value' where id = 'unique_id' and ((x <> 'x_value')); 
update foo set y ='y_value' where id = 'unique_id' and ((y <> 'y_value')); 
update foo set z ='z_value' where id = 'unique_id' and ((z <> 'z_value'));

is better or superior.

I realize that Method B will only do one write and 3 reads if only one column has changed, vs 3 writes and 3 reads for the Method A. I just don't know if it is more time intensive because method B requires looking up the index row 3 times.

Thanks in advance!

Jason Bond
  • 47
  • 5
  • Why not just run the update without a conditional? If x, y, and z are already equal to the values you want to set them to, then there's no harm done. Is this a case where 1) you have reason to believe that reads are meaningfully less costly than writes, or 2) there's a timestamp field that auto-updates when you do an update, and you don't want that to happen unless there's an actual change? Or something else? – octern Jul 06 '12 at 04:53
  • Hi, sorry for the confusion, I have a website where the user puts things in, and I make a unique_id, column rows etc. and once the user comes back, he might a) just go over it and not do anything or b)change one or more things. In that event, I have the conditionals <> just in case the user doesn't change anything. But if he does change one or more things, I will have to update the table row. – Jason Bond Jul 06 '12 at 04:56
  • Are you talking about cases where you might be inserting a row for that user for the first time? Or just about running updates when one of the values might have changed? – octern Jul 06 '12 at 04:58
  • Hello, I have a seperate insert statement for if it's the first time, but I do see the value in condensing both insert and update, but primarily my concern is running the updates when one of the values have changed. – Jason Bond Jul 06 '12 at 05:03
  • No, I think handling them separately is reasonable. I was just confused about what you were saying. My point earlier was that there's no need to check whether a value has changed before running an update. Updating a row with the values that are already in it won't normally cause any problems. – octern Jul 06 '12 at 05:13
  • octern, thank you for your help. I agree with you that I should just do the update without the conditionals.. I read elsewhere that mysql automatically checks before doing the update trigger if it's the same, but someother people say it's different, and that's why I have the whole conditional string. I think keeping it simple is the best way to go. – Jason Bond Jul 06 '12 at 05:25
  • MySQL will only perform the writes if the data to be written is different than what is already there. FYI. So your read/write count is incorrect. – Buttle Butkus Jul 06 '12 at 06:36
  • Thank you Buttle Butkus, it's good to know that mysql update does the check before the write, I know protogreSQL has option of doing it.. but I read the thread http://stackoverflow.com/questions/6296313/mysql-trigger-after-update-only-if-row-has-changed and I'm a little confused.. but simple is always best :) – Jason Bond Jul 06 '12 at 06:51
  • @JasonBond that post is about triggers. But the fact remains that no write is performed if the data is the same. If you checked for affected rows, the count would only include lines that actually changed. – Buttle Butkus Jul 06 '12 at 20:21
  • @Buttle Butkus ahhh! Thank you for the insight. – Jason Bond Jul 07 '12 at 03:30

2 Answers2

1

Based on what I've read in the comments, I agree with octern that you should simply run an update. It will use significantly less resources and based on your table engine, it will free up your table/ row lock for less time, making your table perform a lot better.

However, if you insist on doing a check before doing a write, do so through PHP. Simply do a select statement, compare the code in PHP and then update the appropriate table(s). For example:

$res = mysql_query("SELECT * FROM table1 WHERE PK_ID = '0'");
$arr = mysq_fetch_assoc($res);
$update = false;
if ($arr["field_1"] != $_POST["field_1"])
{
    $update = true;
}

if ($arr["field_2"] != $_POST["field_2"])
{
    $update = true;
}

if ($update)
{
    mysql_query(sprintf("UPDATE table1 SET field_1 = '%s', field_2 = '%s'", $_POST["field_1"], $_POST["field_2"]));
}
if (
Battle_707
  • 708
  • 5
  • 15
  • I don't know why this is best answer. This looks way wrong to me. My impression is that as much as possible should be done by mysql. Why do the checks in PHP when MySQL can do them? – Buttle Butkus Jul 06 '12 at 06:37
  • I guess I should just use the simple update statement; I accepted the answer because of the effort;; – Jason Bond Jul 06 '12 at 06:55
  • @ButtleButkus: Like I said, in a typical environment you do NOT want to do waste MySQL resources if you have another language, like PHP, available, most importantly because it either locks the row (InnoDB) or worse, the entire table (MyISAM) to run this write query. Other queries will be delayed because of it either way, causing a performance drop in the system. Doing it through PHP will use more memory typically, but it is still the better route. – Battle_707 Jul 06 '12 at 17:06
  • @JasonBond: Yes, like I said, unless you have very specific reasons to check data before insertion, a simple update will be MUCH more efficient than one with a complicated conditional. – Battle_707 Jul 06 '12 at 17:07
  • @Battle_707 I think the OP's method A is the best method. It requires only a single MySQL interaction. Yours requires at least 2, plus a bunch of PHP logic that could have been accomplished instantly in the single MySQL statement. Not only that, but if he is using InnoDB tables, then he has row-level locking, so the rest of the table would be available. – Buttle Butkus Jul 06 '12 at 20:18
  • @ButtleButkus: I know. However, he has never specified that he does, and MyISAM it typically the default engine, so I have to keep that in mind. In my experience, IF a conditional has to be done (so a simple unconditional update won't work), then checking it through PHP is typically the best way to do it. In an extremely dumbed down query as used in this post I agree that my PHP approach is overkill, but when the code becomes more elaborate, it will make more sense. – Battle_707 Jul 06 '12 at 20:23
0

Method B will of course be more costly, because you do 3 different selects vs Method A's single select / update on condition.

Its pretty much a comparison of 1 statement to 3 statements. 1 will be faster as they are both update statements.

Jakub
  • 20,418
  • 8
  • 65
  • 92