title: Why does Mysql use a full table scan for table A when joining another table B?
body: <p>I have a table A and a table B. I am selecting information from table A where I need only the information from table A where tabel B joins table A. I have indexes on columns that are joint and on the WHERE clause.</p>
  
  <p>This is the select code:</p>
  
  <pre><code>SELECT *
  FROM tableA
  
  INNER JOIN tableB AS tableB
  ON tableB.id = tableA.id
  AND tableB.type = 'car'
  </code></pre>
  
  <p>When I use explain I can see that mysql is using a full table scan for all the rows in tableA. Further it properly uses the index to join tableB. </p>
  
  <p>So what Mysql seems to be doing is scan all the rows in talbeA to see if there is a match with tableB. However as I have indexes on id of both tables I don't expect a tablescan. As my table has 50.000 rows this query is taking a few seconds (which is really too long for my application). </p>
  
  <p>This is the explain:</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  tableA      ALL     tableA.id       NULL    NULL    49898   
  1   SIMPLE  tableB      eq_ref  tableB.type     tableB.type 4   1       Use WHere
  </code></pre>
  
  <p><strong>My question:</strong> How can I optimize this query and indexes such that Mysql directly selects those rows from tableA that have a match with tableB without scanning all rows in talbeA?</p>