title: Mysql range check instead of index usage on inner join
body: <p>I'm having a serious problem with MySQL (innoDB) 5.0.</p>
  
  <p>A very simple SQL query is executed with a very unexpected query plan.</p>
  
  <p>The query:</p>
  
  <pre><code>SELECT 
  SQL_NO_CACHE
  mbCategory.*
  
  FROM 
  MBCategory mbCategory 
  
  INNER JOIN ResourcePermission as rp
      ON rp.primKey = mbCategory.categoryId 
  
  where mbCategory.groupId = 12345 AND mbCategory.parentCategoryId = 0 
  limit 20;
  </code></pre>
  
  <p>MBCategory - contains 216583 rows</p>
  
  <p>ResourcePermission - contains 3098354 rows.</p>
  
  <p>In MBCategory I've multiple indexes (columns order as in index):</p>
  
  <pre><code>Primary (categoryId)
  A (groupId,parentCategoryId,categoryId)
  B (groupId,parentCategoryId)
  </code></pre>
  
  <p>In ResourcePermission I've multiple indexes (columns order as in index):</p>
  
  <pre><code>Primary - on some column
  A (primKey).
  </code></pre>
  
  <p>When I look into query plan Mysql changes tables sequence and selects rows from ResourcePermission at first and then it joins the MBCategory table (crazy idea) and it takes ages. So I added <code>STRAIGHT_JOIN</code> to force the innodb engine to use correct table sequence:</p>
  
  <pre><code>SELECT
  
  STRAIGHT_JOIN SQL_NO_CACHE
  mbCategory.*
  
  FROM 
  MBCategory
   mbCategory 
  
  INNER JOIN ResourcePermission as rp
      ON rp.primKey = mbCategory.categoryId 
  
  where mbCategory.groupId = 12345 AND mbCategory.parentCategoryId = 0 
  limit 20;
  </code></pre>
  
  <p>But here the second problem materialzie:
  In my opinion mysql should use <code>index A (primKey)</code> on the join operation instead it performs Range checked for each record (index map: 0x400) and it again takes ages !
  Force index doesn't help, mysql still performing Range checked for each record .</p>
  
  <p>There are only 23 rows in the MBCategory which fulfill where criteria, and after join there are only 75 rows. 
  How can I make mysql to choose correct index on this operation ?</p>