This is kind of an expansion on a previously asked question
Some background info:
Querying Over 5,000 items from SQL database that eventually get sorted into a list depending on who the user is. One problem is that users are allowed to have customized lists which means that filtering these 5,000+ items in a uniform way is probably impossible.
Also, important that the amount of users (and users wanting to have custom item lists) is constantly growing, so there will be an unprecedented amount of ways to sort. Basically, we won't be paying attention to filtering this growing list.
Two methods we've come up with so far: -Option 1: Cache item list PER USER (and filter the items needed after) OR -Option 2: Cache the list once for the whole site (every user accesses this one cached list)
The issue with Option 1 is that there will be duplicated lists in cache. Although we do not have an amount of users that will strain our server's memory, we obviously want our system to have scalability.
The issue with Option 2 is that there will be duplicated items in the single, cached list. With the amount of users we have, this can quickly turn into a disorganized list full of duplicated items (i.e. 5,000 items can quickly turn into 10,000 based on a few user's custom item preferences)
Is there a "middle ground" for dealing with this situation? One that combines some benefits of querying and caching.
Even if a response is a link to a potentially helpful source, I would appreciate it as I am kind of at a fork in the road.
Looking to open up new possible paths in my brainstorming.