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: Reply• Quote


Subject
Views
Written By
Posted
2413
November 30, 2016 06:10PM
Re: Fast Update
973
November 30, 2016 08:17PM
1038
November 30, 2016 08:27PM
962
December 01, 2016 07:36PM
1042
December 04, 2016 07:11PM
972
December 04, 2016 09:15PM
936
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.