title: Is there a way to force MySQL execution order?
body: <p>I know I can change the way MySQL executes a query by using the <code>FORCE INDEX (abc)</code> keyword. But is there a way to change the execution order?</p>
  
  <p>My query looks like this:</p>
  
  <pre><code>SELECT c.*
  FROM table1 a
  INNER JOIN table2 b ON a.id = b.table1_id
  INNER JOIN table3 c ON b.itemid = c.itemid
  WHERE a.itemtype = 1
    AND a.busy = 1
    AND b.something = 0
    AND b.acolumn = 2
    AND c.itemid = 123456
  </code></pre>
  
  <p>I have a key for every relation/constraint that I use. If I run explain on this statement I see that mysql starts querying c first.</p>
  
  <pre><code>id    select_type    table    type
  1     SIMPLE         c        ref
  2     SIMPLE         b        ref
  3     SIMPLE         a        eq_ref
  </code></pre>
  
  <p>However, I know that querying in the order  <code>a -&gt; b -&gt; c</code> would be faster (I have proven that)
  Is there a way to tell mysql to use a specific order?</p>
  
  <p>Update: That's how I know that <code>a -&gt; b -&gt; c</code> is faster.</p>
  
  <p>The above query takes 1.9 seconds to complete and returns 7 rows. If I change the query to</p>
  
  <pre><code>SELECT c.*
  FROM table1 a
  INNER JOIN table2 b ON a.id = b.table1_id
  INNER JOIN table3 c ON b.itemid = c.itemid
  WHERE a.itemtype = 1
    AND a.busy = 1
    AND b.something = 0
    AND b.acolumn = 2
  HAVING c.itemid = 123456
  </code></pre>
  
  <p>the query completes in 0.01 seconds (Without using having I get 10.000 rows).
  However that is not a elegant solution because this query is a simplified example. In the real world I have joins from c to other tables. Since <code>HAVING</code> is a filter that is executed on the entire result it would mean that I would pull some magnitues more records from the db than nescessary.</p>
  
  <p>Edit2: Just some information:</p>
  
  <ul>
  <li>The variable part in this query is c.itemid. Everything else are fixed values that don't change.</li>
  <li>Indexes are setup fine and mysql chooses the right ones for me
  
  <ul>
  <li>between a and b there is a 1:n relation (index PRIMARY is used)</li>
  <li>between b and c there is a many to many relation (index IDX_ITEMID is used)</li>
  </ul></li>
  </ul>
  
  <p>the point is that mysql should start querying table a and work it's way down to c and not the other way round. Any change to achive that.</p>
  
  <p>Solution: Not exactly what I wanted but this seems to work:</p>
  
  <pre><code>SELECT c.*
  FROM table1 a
  INNER JOIN table2 b ON a.id = b.table1_id
  INNER JOIN table3 c ON b.itemid = c.itemid
  WHERE a.itemtype = 1
    AND a.busy = 1
    AND b.something = 0
    AND b.acolumn = 2
    AND c.itemid = 123456
    AND f.id IN (
           SELECT DISTINCT table2.id FROM table1
           INNER JOIN table2 ON table1.id = table2.table1_id
           WHERE table1.itemtype = 1 AND table1.busy = 1)
  </code></pre>