MySQL Forums
Forum List  »  Performance

Re: MYSQL Update performance
Posted by: Peter Brawley
Date: May 01, 2017 10:18AM

Wow, weirder, "updating reference tables" suggests it's updating FKs, but you're not doing that.

How long does this take?

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

If that's a lot faster, you have a clue: InnoDB is checking a billion FKs. Then disabling FKs for the transaction might help.

Similar workaround: Explain thinks there are 30 rows to update, so run the above query to find IDs of those rows, then run an update query using just that subset.

Again: What is the MySQL version, what is RAM, what is innodb_buffer_pool_size, is the disk fast, is the hard disk swapping (15 hours, it must be), did you try Force Index?

Options: ReplyQuote


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