search

Home  >  Q&A  >  body text

Speed ​​up Mysql queries during batch updates

I need to update the record, for example

update table abc set marks='15' 
where rollno='432423' and pcode='ABC234';

Nearly 10,000 queries.

My table has 1,00,000 records. I need to update 10,000 records. It takes several hours How can I speed it up. I'm using INNODB

Any way to speed things up.

P粉470645222P粉470645222271 days ago446

reply all(1)I'll reply

  • P粉865900994

    P粉8659009942024-03-28 10:54:25

    The most efficient way is to insert the record into another table and use it to update, for example:

    create table def like abc;
    # optionally drop unneeded columns: alter table def drop foo, drop bar;
    insert into def (marks, rollno, pcode) values
        ('15','432423','ABC234'),
        ('16','432424','DEF567'),
        ...
        ;
    update def join abc using (rollno,pcode)
        set abc.marks=def.marks;
    drop table def;

    If the update itself is still slow, make sure abc has a composite index on (rollno, pcode).

    reply
    0
  • Cancelreply