Speedup Mysql Query on bulk update-CodePudding

I need to update records like

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

almost 10,000 queries.

My table has 1,00,000 records. and I need to update 10,000 records. it take hours how can i speed it up. i am using INNODB

any way to speed this up.

CodePudding user response：

The most efficient way to do this is to insert records to another table and use that to update, something like:

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

CodePudding user response：

For any solution, definitely have INDEX(rollno, pcode) (either order is ok)

Plan A. This might run 10 times as fast, simply because of the transactional overhead:

 START TRANSACTION;
 UPDATE ...
 UPDATE ...
 UPDATE ...
 ...
 COMMIT;

Plan B: Use a single statement. This assumes you have only 3 columns in the table and (rollno,pcode) are the composite PRIMARY KEY:

INSERT INTO ABC (rollno, pcode, marks)
    VALUES
    ('432423', 'ABC234', '15'),
    ('2342345', 'qwer', '99),
    ...
    ON DUPLICATE KEY UPDATE
        marks = VALUES(marks);

Plan C: See ysth's answer.

Please provide SHOW CREATE TABLE and discuss the data and the processing a bit more -- there may be other shortcuts.