Re: MYSQL Update performance

[sree iyer]

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.