What is the best solution for adding INDEX to speed up the query?

Now I have a Query that runs 50 minutes on Mysql database and I can’t accept that…

I want this process can running under 15 minutes….

insert into appianData.IC_DeletedRecords 
            (sourcetableid, 
             concatkey, 
             sourcetablecode) 
select mstid, 
        concatkey, 
        'icmstlocationheader' as sourcetablecode 
 from   appianData.IC_MST_LocationHeader
 where  concatkey not in(select concatkey 
                         from appianData.IC_PURGE_LocationHeader)

The “sourcetableid” and “mstid” are unique.

So what is the best way to add INDEX or optimize on this?

Thank you

Answer

I would write the select as:

select mstid, concatkey,  'icmstlocationheader' as sourcetablecode 
from  appianData.IC_MST_LocationHeader lh
where not exists (select 1 
                  from appianData.IC_PURGE_LocationHeader lhp
                  where lhp.concatkey = lh.concatkey
                 );

Then you want an index on IC_PURGE_LocationHeader(concatkey).