Announcement Module
No announcement yet.

Best method to perform bulk updates

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #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

      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.


      • #4
        UPDATE table
        SET status = "OK"
        WHERE id IN (1, 3, 756, 98234, 2344323);

        I'd strongly recommend converting your status column to an TINYINT and using an integer to specify the status. You'll save a significant amount of disk space and that kind of query will go faster as you will be writing out less information to disk.

        Still, updating 50,000 rows in 2 seconds isn't likely possible. Even if they're 36 bytes, going by your table, that's asking MySQL to write out almost 1 MB a second. That's possible if the records you're updating are all located together, but you might only manage 100 records per second if the records are no sequential by primary key.