Speed ​​up Mysql queries during batch updates

Question

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.

Answer 1

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).