MySQL: поиск строк, которые не принимают участия в отношениях

У меня есть две таблицы: "фильмы" и "пользователи". Там существует связь n: m между ними, описывая, какие фильмы пользователь видел. Это описывается таблицей "видели" Теперь я хочу узнать для данного пользователя все фильмы, которые он не видел. Мое текущее решение выглядит так:

SELECT *
FROM movies 
WHERE movies.id NOT IN (
     SELECT seen.movie_id 
     FROM seen 
     WHERE seen.user_id=123
)

Это прекрасно работает, но, похоже, не очень хорошо масштабируется. Есть ли лучший подход к этому?

Ответ 1

Здесь типичный способ сделать этот запрос без использования метода подзапроса, который вы показали. Это может удовлетворить запрос @Godeke, чтобы увидеть решение на основе соединения.

SELECT * 
FROM movies m
 LEFT OUTER JOIN seen s
 ON (m.id = s.movie_id AND s.user_id = 123)
WHERE s.movie_id IS NULL;

Однако в большинстве брэндов базы данных это решение может работать хуже, чем решение подзапроса. Лучше всего использовать EXPLAIN для анализа обоих запросов, чтобы узнать, какой из них лучше будет работать с вашей схемой и данными.

Здесь другая вариация решения подзапроса:

SELECT * 
FROM movies m
WHERE NOT EXISTS (SELECT * FROM seen s 
                  WHERE s.movie_id = m.id 
                    AND s.user_id=123);

Это коррелированный подзапрос, который должен быть оценен для каждой строки внешнего запроса. Обычно это дорого, и ваш исходный пример лучше. С другой стороны, в MySQL "NOT EXISTS" часто бывает лучше, чем "column NOT IN (...)"

Опять же, вы должны проверить каждое решение и сравнить результаты, чтобы быть уверенным. Это пустая трата времени, чтобы выбрать любое решение без измерения производительности.

Ответ 2

Работает не только ваш запрос, но и правильный подход к проблеме, как указано. Возможно, вы можете найти другой способ подойти к проблеме? Простой LIMIT для вашего внешнего выбора должен быть очень быстрым даже для больших таблиц, например.

Ответ 3

Видите, это ваша таблица соединений, так что да, это похоже на правильное решение. Вы эффективно "вычитаете" набор идентификаторов фильмов в SEEN (для пользователя) из совокупности в MOVIES, что приводит к невидимым фильмам для этого пользователя.

Это называется "отрицательным соединением", и, к сожалению, NOT IN или NOT EXISTS - лучшие варианты. (Мне бы хотелось увидеть синтаксис отрицательного соединения, который был похож на соединения INNER/OUTER/LEFT/RIGHT, но где предложение ON может быть выражением вычитания).

Решение @Bill без подзапроса должно работать, хотя, как он отметил, неплохо проверить ваше решение для производительности в обоих направлениях. Я подозреваю, что подзапрос или нет, весь индекс SEEN.ID(и, конечно, весь индекс MOVIE.ID) будет оцениваться в обоих направлениях: он будет зависеть от того, как оптимизатор обрабатывает его оттуда.

Ответ 4

Если ваша СУБД поддерживает индексы растровых изображений, вы можете попробовать их.