title: Slow MySQL query for relatively small table with proper primary key and index
body: <p>I have a select statement on 3 tables; T1 and T2 can be joined and T2 and T3 can be joined.  T3 is a table with 42 rows only.  T2 is really huge, about 4 billions rows and T1 is  750,000 records.  What I want is that for all the records in T1, I want to get relevant data from T3</p>
  
  <p>If I do a join on all 3 tables like the following, the query takes a long time to run:</p>
  
  <pre><code>select T1.A, T2.B, T3.C, T3.D from 
  T1, T2, T3 where T1.A = T2.A 
  and T1.B = T2.B and T2.C = T3.C
  </code></pre>
  
  <p>But if I take out T3 from the query, the query ran much faster.  I also used <code>EXPLAIN</code> to find out the query path.  It looks like for T3, it's doing full table scan. i.e, the key column is NULL .</p>
  
  <p>So, my question is why is it doing so?  </p>
  
  <p>T3 has a primary key and it's relatively small table.  Is my overall query slow because after T1 and T2 join, for all the remaining record, it's doing full table scan with T3?  So if there's 700,000 records after T1 and T2 join, then for each of those 700,000 records, it's fully scanning T3 tables.  So, it's like doing 700,000 x 42 scanning?</p>
  
  <hr>
  
  <p><strong>UPDATE</strong>:</p>
  
  <p>I replaced my original table names with T1,T2,T3 for simpler understanding.  But here's my actual query:</p>
  
  <pre><code>select vc.vkey, vc.enst, vi.`effect_code`, te.effect, te.impact
  from Variants vc, var_RVS.variant_impact vi, var_RVS.`types_effects` te
  where vi.effect_code = te.eid
  and vc.vkey = vi.vkey 
  </code></pre>
  
  <p>And here's the output of the explain statement:</p>
  
  <pre><code>+----+-------------+-------+------+-------------------------------------+-------------+---------+---------------------------------+------+-------------+
  | id | select_type | table | type | possible_keys                       | key         | key_len | ref                             | rows | Extra       |
  +----+-------------+-------+------+-------------------------------------+-------------+---------+---------------------------------+------+-------------+
  |  1 | SIMPLE      | te    | ALL  | PRIMARY                             | NULL        | NULL    | NULL                            |   42 | NULL        |
  |  1 | SIMPLE      | vi    | ref  | canonical_enst,vkey_idx,effect_code | effect_code | 4       | var_RVS.te.eid                  |  981 | Using where |
  |  1 | SIMPLE      | vc    | ref  | allVsAllXref,vkey_enst              | vkey_enst   | 788     | var_RVS.vi.vkey,var_RVS.vi.enst |    1 | Using where |
  +----+-------------+-------+------+-------------------------------------+-------------+---------+---------------------------------+------+-------------+
  </code></pre>