MySQL Forums
Forum List  »  Performance

MYSQL Update performance
Posted by: sree iyer
Date: April 24, 2017 02:11PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MYSQL Update performance
130
April 24, 2017 02:11PM
55
April 24, 2017 04:05PM
45
May 01, 2017 08:50AM
44
May 01, 2017 10:45AM
87
May 01, 2017 01:11PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.