title: Why does STRAIGHT_JOIN so drastically improve this query, and what does it mean when it is written after the SELECT keyword?
body: <p>I have the following MySql query:</p>
  
  <pre><code>select t1.*
  from Table1 t1
  inner join Table2 t2
  on t1.CommonID = t2.CommonID
  where t1.FilterID = 1
  </code></pre>
  
  <p>It takes about 30 seconds to run, which was strange, because if I comment out the join or the where clause it takes less than a second:
  i.e.</p>
  
  <pre><code>select t1.*
  from Table1 t1
  where t1.FilterID = 1
  </code></pre>
  
  <p>or</p>
  
  <pre><code>select t1.*
  from Table1 t1
  inner join Table2 t2
  on t1.CommonID = t2.CommonID
  </code></pre>
  
  <p>each take less than a second.</p>
  
  <p>Then there is the STRAIGHT_JOIN keyword, which I can find one reference of, here:
  <a href="http://dev.mysql.com/doc/refman/5.0/en/join.html">http://dev.mysql.com/doc/refman/5.0/en/join.html</a></p>
  
  <blockquote>
    <p>STRAIGHT_JOIN is similar to JOIN,
    except that the left table is always
    read before the right table. This can
    be used for those (few) cases for
    which the join optimizer puts the
    tables in the wrong order.</p>
  </blockquote>
  
  <p>What?
  I can write:</p>
  
  <pre><code>select t1.*
  from Table1 t1
  STRAIGHT_JOIN  Table2 t2
  on t1.CommonID = t2.CommonID
  where t1.FilterID = 1
  </code></pre>
  
  <p>and the query executes in less than a second.</p>
  
  <p>Even stranger, I can write:</p>
  
  <pre><code>select STRAIGHT_JOIN  t1.*
  from Table1 t1
  inner join Table2 t2
  on t1.CommonID = t2.CommonID
  where t1.FilterID = 1
  </code></pre>
  
  <p>and it takes less than a second, and this syntax does not appear to even be legal.</p>
  
  <p>I would guess the second example means that a STRAIGHT_JOIN will be used whenever an INNER JOIN is written, but I can’t find any documentation about it.</p>
  
  <p>What is going on here, and how can the “join optimizer” result in such relatively poor performance?  Should I always use STRAIGHT_JOIN?  How can I tell when to use it or not?</p>
  
  <p>Table1 and Table2 both have integer primary keys; FilterID is a foreign key to another table; the CommonID columns are both foreign keys to a third table.  They both have indexes on them.  The database engine is InnoDB.</p>
  
  <p>Thanks</p>