title: How to query huge MySQL databases?
body: <p>I have 2 tables, a <code>purchases</code> table and a <code>users</code> table. Records in the purchases table looks like this:</p>
  
  <pre><code>purchase_id | product_ids | customer_id
  ---------------------------------------
  1           | (99)(34)(2) | 3 
  2           | (45)(3)(74) | 75
  </code></pre>
  
  <p>Users table looks like this:</p>
  
  <pre><code>user_id  | email              | password
  ----------------------------------------
  3        | joeShmoe@gmail.com | password 
  75       | nolaHue@aol.com    | password
  </code></pre>
  
  <p>To get the purchase history of a user I use a query like this:</p>
  
  <pre><code>mysql_query(" SELECT * FROM purchases WHERE customer_id = '$users_id' ");
  </code></pre>
  
  <p>The problem is, what will happen when tens of thousands of records are inserted into the purchases table. I feel like this will take a performance toll.</p>
  
  <p>So I was thinking about storing the purchases in an additional field directly in the user's row:</p>
  
  <pre><code>user_id | email              | password  | purchases
  ------------------------------------------------------
  1       | joeShmoe@gmail.com | password  | (99)(34)(2)
  2       | nolaHue@aol.com    | password  | (45)(3)(74)
  </code></pre>
  
  <p>And when I query the user's table for things like username, etc. I can just as easily grab their purchase history using that one query.</p>
  
  <p>Is this a good idea, will it help better performance or will the benefit be insignificant and not worth making the database look messier?</p>
  
  <p>I really want to know what the pros do in these situations, for example how does amazon query it's database for user's purchase history since they have millions of customers. How come there queries don't take hours?</p>
  
  <h2>EDIT</h2>
  
  <p>Ok, so I guess keeping them separate is the way to go. Now the question is a design one:</p>
  
  <p>Should I keep using the "purchases" table I illustrated earlier. In that design I am separating the product ids of each purchase using parenthesis and using this as the delimiter to tell the ids apart when extracting them via PHP. </p>
  
  <p>Instead should I be storing each product id separately in the "purchases" table so it looks like this?:</p>
  
  <pre><code>purchase_id | product_ids | customer_id
  ---------------------------------------
  1           | 99          | 3 
  1           | 34          | 3
  1           | 2           | 3
  2           | 45          | 75
  2           | 3           | 75
  2           | 74          | 75
  </code></pre>