Best method to perform bulk updates

Hi all,

I hope you can help me, I had an application, Is a messaging system and I would like to be able to do about 50000 updates every two seconds on a table indexed by autoincrement id.

What is the faster way to do these updates? Send these on the same query? disable the indexes?

Thanks in advance,

Show us Create Table of that table. Have you tried batch update??, in case if your application need to set same values for many rows. i.e. UPDATE tb1 SET col=val WHERE id IN (…);


Thanks for your answer, below is the table, the engine is InnoDB.

I would like to update the status field from “PENDING” to “OK” on 50000 rows in one go, is that posible?

±------------±------------±-----±----±--------±------- --------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±------- --------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| reference | varchar(20) | YES | | NULL | |
| status | varchar(10) | NO | | NULL | |
±------------±------------±-----±----±--------±------- --------+

Thanks in advance.