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:
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?
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.