MySQL Forums
Forum List  »  Performance

Re: MYSQL Update performance
Posted by: Peter Brawley
Date: April 24, 2017 04:05PM

To improve readability of posted code and Explain output, surround it with BBCode code tags.

Explain says the engine can't find a stg index that will improve performance, that is, it doesn't think the existing index on (`provider_npi`, `claim_provider_name_sha1`, `claim_provider_address_sha1`) would be much faster than reading the table, even though those are the three columns joined on.

Sometimes the engine's estimates are mistaken. Did you try Force Index?

OTOH the engine may be right: those indexes are on substantially long string columns. Have you looked at normalising away those long strings in favour of integer ids for them? A billion comparisions of such length will be slow.

What are RAM, innodb_buffer_pool_size? Is the disk fast? No swapping?



Edited 1 time(s). Last edit at 04/27/2017 01:21PM by Peter Brawley.

Options: ReplyQuote


Subject
Views
Written By
Posted
1433
April 24, 2017 02:11PM
Re: MYSQL Update performance
724
April 24, 2017 04:05PM
865
May 01, 2017 08:50AM
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.