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
131
April 24, 2017 02:11PM
Re: MYSQL Update performance
55
April 24, 2017 04:05PM
47
May 01, 2017 08:50AM
44
May 01, 2017 10:45AM
89
May 01, 2017 01:11PM


Sorry, only registered users may post in this forum.

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.