I have to update multiple rows (> 100k) satisfying a particular criterion in production environment. Since this query takes much time, it comes in slow query. someone told me that instead of making a bulk update, we should send update query row wise.
Example- old query:
update table_1 set column_20 = 'abc' where updatedOn > date_sub(now(), interval 1day) and column_19 = 'xyz'
select auto_increment_id_column from table_1 where updatedOn > date_sub(now(), interval 1day) and column_19 = 'xyz'
Now use a
"for loop" for all id's extracted from above, then send below query for each id:
update table_1 set column_20 = 'abc' where auto_increment_id_column = id_from_for_loop;
This method seems to be counter intutive, so, please confirm, if it should be done. Also how it will affect master slave replication.
dbinfo - mysql and using statement based replication
Major Update: Our DBA recently updated the replication to row based