product suggestion based on user data

Question

I'm trying to add suggestions to product pages along the lines of : "Customers who purchased this item also purchased x and y".

The way the data is compiled right now is a mysql table with 3 rows.

 PRODUCT | CUSTOMER |QUANTITY 

Product is the product ID. Customer is the customer ID. Quantity is the number of time that product was bought by that customer.

For each product description page, the system needs to figure out which users bought that product, what other products those users bought, in what quantity and then return the 2 products that were bought the most by people who bought the current product.

Hopefully this makes sense and someone can point me in the right directions as to what the mysql query should look like.

Thanks.

Answer 1

I would create a table called product_relations with three columns:

product_a_id | product_b_id | weight:int

When somebody buys more than one product at a time, insert a record for each permutation of two products into the table with weight=1 (or, increment the weight of the existing record).

Then, for a given product, you can find the 2 most related products with

SELECT * FROM product_relations
  WHERE product_a_id = {product_id}
     OR product_b_id = {product_id}
  ORDER BY weight DESC
  LIMIT 2;

There is more overhead up front in creating/maintaining these pairs of related products, but the select should be much faster than joining a bunch of tables together every single time somebody looks at one of your products.