MySQL Forums
Forum List  »  InnoDB

Re: Fast Update
Posted by: Peter Brawley
Date: November 30, 2016 08:17PM

What MySQL version are you running?

In( select... ) semi-joins are slower than joins, increasingly slower the earlier the version.

And, whether it's a semijoin or a join, it needs index(lname,fname,mname,address,city,state,zip) to perform.

Once that index is in place, I'd want to compare the performance of your query with that of a conventional exclusion join ...

update exclusive a
left join exclusive_temp b using(lname,fname,mname,address,city,state,zip)
set a.deleted_date=now()
where a.deleted_date is null and b.lname is null;

5.7 may run that query faster than pre-5.7 versions.

Options: ReplyQuote


Subject
Views
Written By
Posted
1840
November 30, 2016 06:10PM
Re: Fast Update
746
November 30, 2016 08:17PM
774
November 30, 2016 08:27PM
706
December 01, 2016 07:36PM
784
December 04, 2016 07:11PM
709
December 04, 2016 09:15PM
680
December 05, 2016 12:06AM


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.