-2

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?

Community
  • 1
  • 1
Alex
  • 1
  • 1
  • 1
    There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue which can be answered in a few paragraphs. I would suggest you find a development forum (perhaps [Quora](http://www.quora.com/Computer-Programming)?) to work out generalities. Then, when/if you have specific coding issues, come back to Stack Overflow and we'll be glad to help. – Jay Blanchard May 15 '17 at 14:48
  • As I said I need an architectural approach how can I manipulate the data in my case and not the code snippet or some low level details... – Alex May 15 '17 at 16:33

1 Answers1

0

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.

tereško
  • 58,060
  • 25
  • 98
  • 150