Search code examples
mysqlquery-performance

why is MySQL update query is slow?

Update tbldupaudit_workingitems as w inner join
tbldupaudit_auditstatus_temp t on 
Resolved_Status = 0 and
concat(w.Claim_ID, w.DIN) = concat(t.Claim_ID, t.DIN)
SET 
w.Status = t.Status,
w.Audit_Remarks=t.Audit_Remarks,
w.Resolved_By=t.Resolved_By,
w.Resolved_Date=t.Resolved_Date,
w.Resolved_Status = 1;

tbldupaudit_workingitems: Rows - 0.4 million approximately and it gets increased on daily basis. tbldupaudit_auditstatus_temp: Rows - 25 thousand approximately and it may gets increased or decreased.

Process: loading csv file into tbldupaudit_auditstatus_temp table and update tbldupaudit_workingitems if any records that are matched based on Claim_ID &DIN.

This is taking quite a long time to execute but if query runs more than 1:45 hrs then the connection is getting lost.

Show Create Tables: enter image description here

is there any thing that we can improve this query performance please advice. Please do let me know if any more details required to help me but please do not make my question with negative marks as I am new to Stakeoverflow/matter of fact I am new to MySQL(DB coding).

Solution 
  • AND concat(w.Claim_ID, w.DIN) = concat(t.Claim_ID, t.DIN)

    That cannot be optimized.

    Probably this would work very well:

    AND w.Claim_ID = t.Claim_ID
AND w.DIN = t.DIN

    But it would need a composite index on at least one of the tables:

    INDEX(Clarim_ID, DIN)