I'm building a large collection of items from which I want to query items that a specific user has not seen before. I will need to track, per user, which items s/he has seen.

Currently the items are stored in mongodb and the 'seen items' I am planning to store in a mysql database.

I have two questions:

  1. How should I design the database of items in order to effectively query for items a certain user has not seen?
  2. As each user can have 'seen' several thousand items, should each user have its own mysql table or should I just have one large table?

EDIT: The items the user get are from the collection of items the user has not seen are selected randomly


The data says that you need

    user_id ...,
    item_id ...,
    PRIMARY KEY(user_id, item_id),
    INDEX      (item_id, user_id)

That is in addition to table Users, with PRIMARY KEY (user_id) and Items with PRIMARY KEY (item_id).

Items not seen by user 1234:

SELECT i.item_id
    FROM Items AS i
    LEFT JOIN Seen AS s  ON  s.item_id = i.item_id
                         AND s.user_id = 1234;
    WHERE s.item_id IS NULL ;

You could then JOIN to Users and/or Items to get more info.

Separate tables for separate users -- NO. This is a common question with the same answer every time.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.