title: MySQL Recommendation Engine Query
body: <p>I'm trying to write a very basic recommedation/upselling query, where I can return a list of products that other people have purchased that have been in the same "order" as a product the current user has purchased, but aren't the same product.</p>
  
  <p>I simple way to explain this is:</p>
  
  <ul>
  <li>User A buys a spanner and a drill</li>
  <li>User B buys a drill and some screws</li>
  <li>User C buys some nails and a spanner</li>
  </ul>
  
  <p>So I'd like to recommend screws and nails to User A.</p>
  
  <p>I have a products table, a Invoice_Header table, and an Invoice_Line table.</p>
  
  <p>What I have so far is this (which probably explains my requirements a little better).</p>
  
  <pre><code>SELECT
      Products.Product_Code,
      Products.Full_Description,
      count(Products.id) as hits
  FROM
      Products as Products,
      Invoice_Header as Invoice_Header,
      Invoice_Line as Invoice_Line,
      Invoice_Line as Invoice_Line_Common,
      Invoice_Line as Invoice_Line_Products
  WHERE
      Invoice_Header.account_number = 765
      AND
      Invoice_Line.invoice_num = Invoice_Header.invoice_number
      AND
      Invoice_Line_Common.product_code = Invoice_Line.product_code
      AND
      Invoice_Line_Products.invoice_num = Invoice_Line_Common.invoice_num
      AND
      Products.Product_Code = Invoice_Line_Products.product_code
      AND
      Products.Product_Code != Invoice_Line.product_code
  group by Products.Product_Code
  order by hits
  limit 0,10
  </code></pre>
  
  <p>Now, this runs like a dog, as I have 800,000 Invoice Lines.</p>
  
  <p>Has anyone any recommendations for doing this faster?</p>