title: MySQL: Optimizing UPDATE of many rows in a large table
body: <p>The table is organized using a <a href="http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/" rel="nofollow">nested set model</a>. When I'm inserting something I need to move everything with left/right values > destination to the left.</p>
  
  <pre><code>UPDATE projects SET rgt = rgt + 2 WHERE rgt &gt;= @superRgt;
  </code></pre>
  
  <p>This query can take a couple of seconds to complete, which is not acceptable. My question is; how can this query be optimized?
  is it possible to ..</p>
  
  <ul>
  <li>Normalize/defragment/reorder the physical layout of the table?</li>
  <li>Create better indexes?</li>
  <li>Just be smarter and avoid the problem?</li>
  </ul>
  
  <p>We're using Innodb table, and already have indexes on left, right and left/right. The table has ~100k rows.</p>