title: Mysql 5.1 Does Not Use Index On Joining With the Results of a Select
body: <p>I have a query like:</p>
  
  <pre><code>select * from x join (
      select * from y join z on y.id=z.id
  ) as t on x.name = t.name
  </code></pre>
  
  <p>This query takes 5 minutes in mysql <code>5.1</code>, while with the same dataset it takes only 100 milli seconds on mysql <code>5.7</code>. Mysql <code>5.1</code> is is not honoring the fact that name column is a key in all the tables. Basically all the tables have the column <code>name</code> and they are indexed:</p>
  
  <pre><code>KEY `name_index` (`name`),
  </code></pre>
  
  <p>I have tried using force index to persuade the server to use the index, no avail. Meaning that both versions complain about the syntax. </p>
  
  <pre><code>select * from x join (
      select * from y join z on y.id=z.id
  ) as t force index(`name_index`) on x.name = t.name
  </code></pre>
  
  <blockquote>
    <p>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'force index(<code>name_index</code>) on x.name = t.name' at line 3</p>
  </blockquote>
  
  <p>Does anyone know any workaround to force mysql to use an index on the results of a join?</p>