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
235
April 24, 2017 02:11PM
90
April 24, 2017 04:05PM
92
May 01, 2017 08:50AM
102
May 01, 2017 10:18AM
92
May 01, 2017 10:45AM
187
May 01, 2017 01:11PM
127
May 01, 2017 01:55PM


Sorry, you can't reply to this topic. It has been closed.

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.