Recommendation algorithm

notes.txt

table "recommendation_action"
id_item (int)
item_type (int - 1 = "users browsed X", 2- "users bought X")
id_user

table "recommendation_pair"
id_item_from (int)
id_item_to (int)

Algorithm:
- Every time a user buys something, add it to the action table
- Every time an authenticated user browses, add it to the action table
- Periodically, examine the pairs with the highest count, and add them to the pair table. This will be very slow, hence using the table as a cache

I'd use this as an subselect (hard to play around with without a Fiddle, please do that):

    SELECT
        action1.id_item id_item1,
        action2.id_item id_item2
    FROM recommendation_action action1
    INNER JOIN recommendation_action action2 ON (action1.user_id = action2.user_id)

Then you'll want to put that in another select that counts them, and perhaps stores the top 10 for each pair. This will create a lot of data!