MYSQL Update performance

[sree iyer]

having performance issues with this update query. This query runs for more than 9 hrs.

any help in optimizing this query, will be highly helpful.

query -

UPDATE claims_providers_associations cp,
stage_claims_provider_attributes stg
SET cp.provider_id = stg.provider_id
WHERE cp.claim_provider_npi = stg.provider_npi
AND cp.claim_provider_name_sha1 = stg.claim_provider_name_sha1
AND cp.claim_provider_address_sha1 = stg.claim_provider_address_sha1

table -

CREATE TABLE `claims_providers_associations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`imported_claim_id` int(11) NOT NULL,
`imported_claim_file_id` int(11) NOT NULL,
`claim_provider_npi` varchar(40) DEFAULT NULL,
`claim_provider_name_sha1` binary(20) NOT NULL,
`claim_provider_address_sha1` binary(20) NOT NULL,
`provider_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `claim_id` (`imported_claim_id`,`imported_claim_file_id`),
KEY `claims_providers_associations_idx` (`claim_provider_npi`,`claim_provider_name_sha1`,`claim_provider_address_sha1`)
) ENGINE=InnoDB AUTO_INCREMENT=1079689128 DEFAULT CHARSET=utf8;



CREATE TABLE `stage_claims_provider_attributes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`provider_npi` varchar(40) DEFAULT NULL,
`provider_id` int(11) NOT NULL,
`provider_name` varchar(200) DEFAULT NULL,
`provider_address_1` varchar(500) DEFAULT NULL,
`provider_address_2` varchar(500) DEFAULT NULL,
`provider_city` varchar(100) DEFAULT NULL,
`provider_state` varchar(50) DEFAULT NULL,
`provider_zip` varchar(50) DEFAULT NULL,
`provider_spec` varchar(100) DEFAULT NULL,
`name_distance` smallint(6) NOT NULL,
`claim_provider_name_sha1` binary(20) DEFAULT NULL,
`claim_provider_address_sha1` binary(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_npi_name_address` (`provider_npi`,`claim_provider_name_sha1`,`claim_provider_address_sha1`)
) ENGINE=InnoDB AUTO_INCREMENT=11075416 DEFAULT CHARSET=utf8;


the claims_providers_associations has 1,078,086,600 rows.
The stage_claims_provider_attributes has 11,033,025 rows


Explain gives me -

explain extended
UPDATE claims_providers_associations cp,
stage_claims_provider_attributes stg
SET cp.provider_id = stg.provider_id
WHERE cp.claim_provider_npi = stg.provider_npi
AND cp.claim_provider_name_sha1 = stg.claim_provider_name_sha1
AND cp.claim_provider_address_sha1 = stg.claim_provider_address_sha1;



id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE stg ALL idx_npi_name_address NULL NULL NULL 10666246 100.00 Using where
1 SIMPLE cp ref claims_providers_associations_idx claims_providers_associations_idx 163 claims_master_prod.stg.provider_npi,claims_master_prod.stg.claim_provider_name_sha1,claims_master_prod.stg.claim_provider_address_sha1 36 100.00 NULL