Altering queries?

[John Kramlich]

K J wrote:
> Does using Memcache lead to a change in database query design?
>  
> For instance, in my application each user has an ignore list, where 
> they can specify users they'd like to block.  Once blocked neither 
> person can see the existence of the other in the system anymore.  Now 
> suppose user A (who has blocked user B) is listing user C's friends, 
> which contains user B.  My SQL query would be something like 
> select from Friends where ID=C and where none of these members are on 
> A's ignore list.
>  
> Now, if I want to start storing each user's ignore list in Memcache, 
> it would mean I just grab C's friends list, then filter out the 
> ignored users in memcache using program logic.
>  
> Is this a good way of using Memcache? Or is there a better way?

I have a similar situation in one of my PHP applications.  Our users are 
able to flag a posting to our site as spam.  The post remains on the 
site until several other users flag the same item.  However, each user 
who flags a post no longer sees that post.

I've accomplished this by storing which posts a user flags into a MySQL 
table.  When a user logs in I retrieve their account information and an 
array of all flagged posts.  This information is then stored in their 
session data.

Anytime a listing of posts is displayed on the site I do a quick check 
with PHPs in_array() function and strip out any posts that the user has 
already flagged.  This allows me to store the entire list of current 
posts in Memcache and customize the display of that data for each user.  
We don't have a huge amount of users yet so I don't know how well this 
would scale to your application but it has served us well so far.

The lists of flagged posts per user are always pretty low since we don't 
have much of a spam problem yet.  Also, as items are removed from the 
site they are removed from the MySQL lookup table and thus don't clutter 
a users session data anymore since they will never have a chance to 
encounter that post again.

- John