MySQL Forums
Forum List  »  Performance

Re: MYSQL Update performance
Posted by: sree iyer
Date: May 01, 2017 08:50AM

I made few changes to this query. Removed binary column comparison and added integer column to the join condition.

Query -


update claim_provider_association cp 
inner join 
     provider_attributes_lookup pa     
on cp.provider_attributes_lookup_id = pa.id
and pa.old_provider_id is not null
SET cp.provider_id = pa.old_provider_id  
where cp.provider_id is null;


Explain on this query -
id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	pa	range	PRIMARY,old_provider_id	old_provider_id	5	NULL	6970784	100.00	Using where; Using index
1	SIMPLE	cp	ref	claim_provider_association_idx	claim_provider_association_idx	4	claims_master_prod.pa.id	30	100.00	Using where


Table -
CREATE TABLE `provider_attributes_lookup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `claim_provider_npi` varchar(40) DEFAULT NULL,
  `claim_provider_name_sha1` binary(20) NOT NULL,
  `claim_provider_address_sha1` binary(20) NOT NULL,
  `old_provider_id` int(11) DEFAULT NULL,
  `new_provider_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `npi_name_address` (`claim_provider_npi`,`claim_provider_name_sha1`,`claim_provider_address_sha1`),
  KEY `old_provider_id` (`old_provider_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14913307 DEFAULT CHARSET=utf8


CREATE TABLE `claim_provider_association` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider_attributes_lookup_id` int(11) NOT NULL,
  `imported_claim_id` int(11) NOT NULL,
  `imported_claim_file_id` int(11) NOT NULL,
  `provider_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `claim_id` (`imported_claim_id`,`imported_claim_file_id`),
  KEY `claim_provider_association_idx` (`provider_attributes_lookup_id`),
  CONSTRAINT `claim_provider_association_ibfk_1` FOREIGN KEY (`provider_attributes_lookup_id`) REFERENCES `provider_attributes_lookup` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1108155809 DEFAULT CHARSET=utf8


Now this update query is struck on state = updating reference tables for the past 15 hours.

Options: ReplyQuote


Subject
Views
Written By
Posted
1433
April 24, 2017 02:11PM
726
April 24, 2017 04:05PM
Re: MYSQL Update performance
865
May 01, 2017 08:50AM
769
May 01, 2017 10:18AM
814
May 01, 2017 10:45AM
753
May 01, 2017 12:58PM
1527
May 01, 2017 01:11PM
675
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.