Best method to perform bulk updates

MySQL & MariaDB Other MySQL® Questions
1

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,
Ken

2

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 (…);

3

Hi

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.