Skip to main content
fixed the query
Source Link
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 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.

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 (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;

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.

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.

Source Link
Rick James
  • 79.4k
  • 5
  • 51
  • 117

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 (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;

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.