Desiging a "people who liked this also liked.... " tool

[stubarny]

Hi,

 

I've designed a simple tool for identifying the quality of posts on my social network by forcing people to state whether they like a post or not before allowing them to see the next post. So for each post I have a running total of the number of people who have seen the post and the proportion or people who have liked/disliked the post.

 

Table of 'granular votes':

user_account, post_number, vote (like/dislike), timestamp, ip_address

 

I then have some running totals in the "posts" table:

post_likes (number of likes), post_dislikes (number of dislikes), post_views (number of post views)

 

What I'm trying to do now is personalise this functionality for each user by, identifying what posts people are likely to like based on their history of 'likes'. i.e. by finding which posts people liked who also like the same posts as the user. This should show posts that are not only high quality but also relevent to the user's interests.

 

However I'm really struggling with it - in my experience any solution that uses joined tables using lots of data is going to be really slow (hence me using running totals in the submissions table). Please could someone nudge me in the right direction for finding out an effective way of doing this?

 

Thanks,

 

Stu

[fenway]

I'd recommend you think about the solution without worrying about how mysql may or may not execute it.