title: Slow UPDATE, can't work out why
body: <p>I have a slow UPDATE statement and can't work out why</p>
  
  <pre><code>CREATE TEMPORARY TABLE ttShifts
  (ShiftId int NOT NULL, ttdtAdded datetime not null, ttdtBookingStart DATETIME NOT NULL, ttHoursNeeded int not null, szHidden varchar(255),
   PRIMARY KEY (ShiftID))
   AS
   (select shiftId, dtAdded as ttdtAdded, dtBookingStart as ttdtBookingStart, HoursNeeded as ttHoursNeeded from shifts where shifts.lStatus=0);
  
   update ttShifts set szHidden='x' where szHidden is NULL and ShiftId in (select shiftid from shifts,practices where shifts.PracticeId=practices.PracticeId and shifts.iBranch = practices.iBranch and practices.Healthboard not in (select Locname from userlocationprefs where iUser=82 and Level=0 and fAcceptWork=true))
  
  166 rows affected. (Query took 0.2899 seconds.)    
  </code></pre>
  
  <p>EXPLAIN: </p>
  
  <pre><code>  1 PRIMARY ttShifts    index       PRIMARY 4       297 Using where 
    2 DEPENDENT SUBQUERY  shifts  eq_ref  PRIMARY PRIMARY 4   func    1       
    2 DEPENDENT SUBQUERY  practices   ALL PRIMARY             636 Using where; Using join buffer (flat, BNL join) 
    3 MATERIALIZED    userlocationprefs   ref PRIMARY PRIMARY 8   const,const 3   Using where 
  </code></pre>
  
  <p>OK, so let's try switching this to use a join to eliminate the dependent subqueries</p>
  
  <pre><code>update ttShifts join shifts on (ttShifts.ShiftID=shifts.shiftId) join practices on (shifts.practiceId=practices.PracticeId and shifts.iBranch=practices.iBranch) set szHidden='x' where szHidden is NULL and practices.Healthboard not in (select Locname from userlocationprefs where iUser=82 and Level=0 and fAcceptWork=true);
  
  166 rows affected. (Query took 0.4009 seconds.)
  </code></pre>
  
  <p>Right, so that takes <em>longer</em></p>
  
  <p>EXPLAIN:</p>
  
  <pre><code>1   PRIMARY ttShifts    ALL PRIMARY             297 Using where 
  1   PRIMARY shifts  eq_ref  PRIMARY PRIMARY 4   ttShifts.shiftId    1       
  1   PRIMARY practices   ALL                 636 Using where 
  2   MATERIALIZED    userlocationprefs   ref PRIMARY PRIMARY 8   const,const 3   Using where 
  </code></pre>
  
  <p>OK, so it must be the MATERIALIZED bit it's not doing efficiently for some reason, let's try swapping that to a straight equality check just as a test.</p>
  
  <pre><code>update ttShifts join shifts on (ttShifts.ShiftID=shifts.shiftId) join practices on (shifts.practiceId=practices.PracticeId and shifts.iBranch=practices.iBranch) set szHidden='x' where szHidden is NULL and practices.Healthboard!='X'
  </code></pre>
  
  <p>0.3493 seconds.</p>
  
  <p>OK, not that then.</p>
  
  <p>If I eliminate the update and make it a select...</p>
  
  <pre><code>select * from ttShifts join shifts on (ttShifts.ShiftID=shifts.shiftId) join practices on (shifts.practiceId=practices.PracticeId and shifts.iBranch=practices.iBranch) where szHidden is NULL and practices.Healthboard not in (select Locname from userlocationprefs where iUser=82 and Level=0 and fAcceptWork=true)
  
  (166 rows, Query took 0.0159 seconds.)
  </code></pre>
  
  <p>So why is the UPDATE so bloody slow, and what can I do to speed it up?</p>