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
271
April 24, 2017 02:11PM
Re: MYSQL Update performance
106
April 24, 2017 04:05PM
110
May 01, 2017 08:50AM
124
May 01, 2017 10:18AM
118
May 01, 2017 10:45AM
116
May 01, 2017 12:58PM
218
May 01, 2017 01:11PM
156
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.