title: When to add index on joined tables
body: <p>I have a mysql table with 9 million records that doesn't have any indices set.  I need to join this to another table based on a common ID.  I'm going to add an index to this ID, but I also have other fields in the <strong>select</strong> and <strong>where</strong> clause.  </p>
  
  <p>Should I add an index to all of the fields in the <strong>where</strong> clause?  </p>
  
  <p>What about the fields in the <strong>select</strong> clause?  Should I create one index for all fields, or an index per field?</p>
  
  <p><strong>Update - Added tables and query</strong></p>
  
  <p>Here is the query - I need to get the number of sales, item name, and item ID by item based on the store name and store ID (the store name and ID by themselves are not unique)</p>
  
  <pre><code>SELECT COUNT(*) as salescount, items.itemName, CONCAT(items.ID, items.productcode) as itemId 
  FROM items JOIN sales ON items.itemId = sales.itemId WHERE items.StoreName = ? 
  AND sales.storeID = ? GROUP BY items.ItemId ORDER BY salescount DESC LIMIT 10;
  </code></pre>
  
  <p>Here is the sales table:</p>
  
  <pre><code>+----------------+------------------------------+------+-----+---------+-------+
  | Field          | Type                         | Null | Key | Default | Extra |
  +----------------+------------------------------+------+-----+---------+-------+
  | StoreId        | bigint(20) unsigned          | NO   |     | NULL    |       |
  | ItemId         | bigint(20) unsigned          | NO   |     | NULL    |       |
  +----------------+------------------------------+------+-----+---------+-------+
  </code></pre>
  
  <p>and the items table:</p>
  
  <pre><code>+--------------------+------------------------------+------+-----+---------+-------+
  | Field              | Type                         | Null | Key | Default | Extra |
  +--------------------+------------------------------+------+-----+---------+-------+
  | ItemId             | bigint(20) unsigned          | NO   | PRI | NULL    |       |
  | ProductCode        | bigint(20) unsigned          | NO   |     | NULL    |       |
  | ItemName           | varchar(100)                 | NO   |     | NULL    |       |
  | StoreName          | varchar(100)                 | NO   | PRI | NULL    |       |
  +--------------------+------------------------------+------+-----+---------+-------+
  </code></pre>