PHP用户个性化

Question

There is a news site - about 50 000 news in mysql db for now. I need to create a list of most interesting and relevant news for each news page and remove the already viewed items for the current user (the actual personalization). I have made a list of news viewed in cookies already. So all I need is an architectural best approach for the way to filter viewed news.

I see only tow options:

1. Keep in memory already calculated full list of most popular news (20-30k items) and for each customer request remove viewed ones.

2. Each time user opens the page create a list of popular items for him again.

   In option 1 we can use caching with APC, REDIS etc., but always have a big arrays of data copied to each request which is eating a lot of memory. But in the option 2 we would have to request db each time so it would be not fast and CPU and DB resource consuming.

So is there any way I can avoid using so many resources and make it fast?

Answer 1

You can make something like

SELECT ... article data .. FROM Articles 
LEFT JOIN ViewedArticles USING (articleId)
LEFT JOIN Users USING (userId)
WHERE ViewedArticles.articleId IS NULL AND Users.userId = :id

That should select select only the articles, that don't have matching articleId in the ViewedArticles table with matching userId.