title: SQL performance hit when NOT joining
body: <p>I'm trying to execute a select query over a fairly simple (but large) table and am getting over 10x slower performance when I <strong>don't</strong> join on a certain secondary table.</p>
  
  <p>TableA is keyed on two columns, 'ID1' &amp; 'ID2', and has a total of 10 numeric (int + dbl) columns.</p>
  
  <p>TableB is keyed on 'ID1' and has a total of 2 numeric (int) columns.</p>
  
  <pre><code>SELECT
      AVG(NULLIF(dollarValue, 0))
  FROM
      TableA
  INNER JOIN
      TableB
  ON
      TableA.ID1 = TableB.ID1
  WHERE
      TableA.ID2 = 5
  AND
      TableA.ID1 BETWEEN 15000 AND 20000
  </code></pre>
  
  <p>As soon as the join is removed, performance takes a major hit. The query above takes <strong>0.016 seconds</strong> to run while the query below takes <strong>0.216 seconds</strong> to run.</p>
  
  <p>The end goal is to replace TableA's 'ID1' with TableB's 2nd (non-key) column and deprecate TableB.</p>
  
  <pre><code>SELECT
      AVG(NULLIF(dollarValue, 0))
  FROM
      tableA
  WHERE
      ID2 = 5
  AND
      ID1 BETWEEN 15000 AND 20000
  </code></pre>
  
  <p>Both tables have indexes on their primary keys. The relationship between the two tables is One-to-Many. DB Engine is MyISAM.</p>
  
  <p>Scenario 1 (fast):</p>
  
  <pre><code>id  stype   table   type    possKey key     kln ref                     rws extra
  1   SIMPLE  TableB  range   PRIMARY PRIMARY 4                           498 Using where; Using index
  1   SIMPLE  TableA  eq_ref  PRIMARY PRIMARY 8   schm.TableA.ID1,const   1   
  </code></pre>
  
  <p>Scenario 2 (slow):</p>
  
  <pre><code>id  stype   table   type    possKey key     key_len ref         rows    extra
  1   SIMPLE  TableA  range   PRIMARY PRIMARY 8                   288282  Using where
  </code></pre>
  
  <p>Row count and lack of any mention of an index in scenario 2 definitely stand out, but why would that be the case?</p>
  
  <p>700 results from both queries -- same data.</p>