mysql bulk update performance when `WHERE` filter is not satisfied

Question

bulk update like this:

update table_name set price='x1' where sku='x1'; #updated because sku=x1 exist
update table_name set price='x2' where sku='x2'; #ignored because sku=x2 does not exist
update table_name set price='x3' where sku='x3'; #ignored because sku=x2 does not exist
  ...about 10000 lines...

some lines did not update anything because they do not exist, I want to know, would they make mysql slow OR just affect nothing?

score 1 · Accepted Answer · answered Jun 14 '17 at 02:45

1

If you have an index on sku, then the updates should have reasonable performance. If they are in a single transaction, then the performance should be okay.

However, you would be better off putting the new data into a table and using a join for the update.

answered Jun 14 '17 at 02:45

Gordon Linoff

1,242,037
58
646
786

`update table_name as t1 left join table_name as t2 on t1.id=t2.id set t1.price='x1' where t2.sku='x1'`, you mean that? – Mil0R3 Jun 14 '17 at 02:52
@Mil0r . . . Use `join` not `left join`. You only want the values that match. – Gordon Linoff Jun 14 '17 at 02:55

mysql bulk update performance when `WHERE` filter is not satisfied

1 Answers1