MySQL Forums :: InnoDB :: Fast Update

Advanced Search

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
Fast Update 463 Ferdie Palero 11/30/2016 06:10PM
Re: Fast Update 236 Peter Brawley 11/30/2016 08:17PM
Re: Fast Update 313 Ferdie Palero 11/30/2016 08:27PM
Re: Fast Update 307 Ferdie Palero 12/01/2016 07:36PM
Re: Fast Update 210 Ferdie Palero 12/04/2016 07:11PM
Re: Fast Update 317 Peter Brawley 12/04/2016 09:15PM
Re: Fast Update 227 Ferdie Palero 12/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.