1

I need to handle read/ unread status of a post per user. Write now i am using a denormalized column to store comma separated user_ids. Since it is a text column the performance of the select query is degraded.

Now i would like to assign a constant for all the users say User A - 1, User B-2, User C-4 and User D-8 (Bitwise), and store its combination in a integer column.So when the user A,C read the value for the integer column will be (1+4) 5. And use the bitwise operator in criteria to query the read/unread post. The problem is the no. of users that i can store as combination is restricted say 62 user's status in a column. If i have to extend further i may have add another BIGINT column to store other users.

What could be the best way to store that information. Following are the action i need to do.

  1. when a new reply is posted then i have to update the value as 0 (to make unread for every one).
  2. Update the status in the column when a user reads the post.
  3. Select all the unread post for an user.

EDIT: I trying to address 3rd action indirectly. i add that integer column in select columns list and find the read status for the user in application code instead of adding as mysql criteria. Any way this is not a solution. I still need of a good one.

John
  • 2,682
  • 5
  • 23
  • 24

1 Answers1

0

I would just do it this way, create a table like this:

CREATE TABLE views(
    user_id bigint not null,
    post_id bigint not null,
    primary key(user_id,post_id
)

and then just check if a record with the user's id and the post id exists. You can also use this to count the number of views.

bigblind
  • 12,539
  • 14
  • 68
  • 123
  • I tried this already, it is not scaling well in my case. 100s of users and half a million posts. Retrieving the set of posts based on some criteria. – John Jul 05 '11 at 13:24
  • There's also one that's not as nice, bud doesn't require any storage, you can show them what's new since their last login, just by comparing dates. – bigblind Jul 05 '11 at 14:03
  • Thanks Frederik, The read/unread status plays a vital role here, so it can't be volatile – John Jul 05 '11 at 14:34
  • I wish I had enough rep to add a bounty to this question, because I'm really wondering what'll come u here, if some mysql ninjqs find their way here. – bigblind Jul 05 '11 at 14:59