title: Need basic advice regarding mysql indexes and query performance
body: <p>I'm a relative newbie when it comes to database design.  I know how to define the tables, but I'm starting to run into performance issues as my database grows.  Can someone give me some guidance on how to use mysql indexes to improve query performance?  My understanding of indexes is that they prevent the entire database table from being searched line by line, while still being able to return valid results.  </p>
  
  <p>I have typically just defined a primary key for each table that gives a unique id for each row.  Is the idea behind defining an "index" that you do it for each field that will be used in the WHERE clause of a mysql statement?  Not sure if that is too general of a statement.  </p>
  
  <p>As an example, lets say we have the following three tables: </p>
  
  <p><strong>products</strong></p>
  
  <p>products_id, products_name</p>
  
  <p><strong>categories</strong></p>
  
  <p>categories_id, categories_name</p>
  
  <p><strong>products_to_categories</strong></p>
  
  <p>products_id
  categories_id</p>
  
  <p>and we want to run the following query: </p>
  
  <pre><code>SELECT p.products_name, c.categories_name 
    FROM categories as c 
    JOIN products as p 
    JOIN products_to_categories as p2c 
   WHERE p.products_id=12345
  </code></pre>
  
  <p>Would we define both fields in the products_to_categories table as indexes and then the products_id and categories_id as primary keys within their parent table? </p>
  
  <p>Any advice or guidance on the general approach to indexes would be much appreciated!</p>