The data says that you need
CREATE TABLE Seen (
user_id ...,
item_id ...,
PRIMARY KEY(user_id, item_id),
INDEX (item_id, user_id)
) ENGINE=InnoDB;
That is in addition to table Users
, with PRIMARY KEY (user_id)
and Items
with pk 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
WHERE s.item_id IS NULL
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.