title: MySQL using indexes in RAM; why are the disks running?
body: <p>I have indices of 800M and told MySQL to use 1500M of RAM. After starting MySQL it uses 1000M on Windows 7 x64. </p>
  
  <p>I want to execute this query:</p>
  
  <pre><code>SELECT oo.* FROM table o 
  LEFT JOIN table oo ON (oo.order = o.order  AND oo.type="SHIPPED") 
  WHERE o.type="ORDERED" and oo.type IS NULL
  </code></pre>
  
  <p>This finds all items not yet shipped . The execution plan tells me this:</p>
  
  <p><a href="https://i.stack.imgur.com/UIXeA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/UIXeA.png" alt="photo"></a>
  </p>
  
  <p>My indices are: </p>
  
  <ul>
  <li><code>type_order</code>: Multiple index with <code>type</code> and <code>order</code></li>
  <li><code>order_type</code> with <code>order</code> as first index value, followed by <code>type</code></li>
  </ul>
  
  <p>So MySQL should use the index <code>type_order</code> from RAM and then pick out the few entries with the <code>order_type</code> index. I'm expecting only about 1000 non shipped items, so this query should be really fast, but it isn't. Disks are going crazy....</p>
  
  <p>What am I doing wrong?</p>