We have a table with over 200k rows. Every row belongs to a category, there are almost 50k rows per category. What is the best way to update all rows of that category? We are using a script like
insert into foo (catgeory_id, family, text) values ('1', 'test', 'my text'), (50k more rows) on duplicate key update family = values(family), text = values(text)
Running that solely is relatively fast. Our production server has many databases and when we run the script on the different databases at the same time the whole machine starts swapping and on some point the kernel is killing the mysql process.
Where should we start to optimize? We are running MySQL 5.5.