Skip to main content
2 of 2
fixed the query
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

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 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.

Rick James
  • 79.4k
  • 5
  • 51
  • 117