Как эффективно выполнять пересечение соединений в SQL?

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

Он использует синтаксис MySQL (не уверен, что вы используете), но он довольно прост, и вы сможете использовать его с другими базами данных.

Это будет выглядеть для вас так (используя синтаксис MySQL):

SELECT books.id, books.title, books.author
FROM books
INNER JOIN taggings ON ( taggings.book_id = books.book_id )
INNER JOIN tags ON ( tags.tag_id = taggings.tag_id )
WHERE tags.name IN ( @tag1, @tag2, @tag3 )
GROUP BY books.id, books.title, books.author
HAVING COUNT(*) = @number_of_tags

Из моего другого поста:

Если у вас есть 3 тега, как в вашем примере, тогда number_of_tags должно быть 3, и объединение приведет к 3 строкам для каждого совпадающего идентификатора.

Вы можете либо создать этот запрос динамически, либо определить его, скажем, с 10 тегами и инициализировать их значением, которое не будет встречаться в тегах.

Я бы порекомендовал ВСЕ вместо пересечения, поскольку mysql на самом деле знает, как присоединиться к этому намного лучше, хотя мне не хватает надлежащих тестов.

select books.* from books, taggings, tags
 where taggings.book_id = books.id
   and taggings.tag_id  = tag.id
   and tag.name ALL("interesting", "fiction");

Что касается его масштабирования, то с миллионами книг и низкой кардинальностью в таблице тегов вы в конечном итоге перенесете идентификатор тега в код/память, чтобы использовать taggeds.tag_id ALL(3, 7 , 105) или что-то в этом роде. Это последнее соединение для получения таблицы тегов не будет использовать индекс, если вы не превысите 1k тегов, поэтому вы будете каждый раз выполнять сканирование таблицы.

По моему опыту, соединения, пересечения и объединения — это огромное зло для производительности. В основном соединения - это проблема, с которой мы обычно сталкиваемся. Чем меньше у вас соединений, тем быстрее вы в конечном итоге получите.

Здесь немного больше «старой школы» диалекта SQL, но это более компактный синтаксис и все еще внутреннее соединение.

select * from books, taggings tg1, tags t1, taggings tg2, tags t2 
 where tg1.book_id = books.id
   and tg1.tag_id  = t1.id
   and t1.name = 'interesting'
   and tg2.book_id = books.id
   and tg2.tag_id  = t2.id
   and t2.name = 'fiction'

РЕДАКТИРОВАТЬ: Ничего себе, это много ненависти от стекеров за то, что они слишком много объединяются в одном запросе. Больше оптимизации можно получить, используя exists подзапросов:

select * from books
 where exists (select * from taggings, tags
                where tags.name = 'fiction'
                  and taggings.tag_id = tags.id
                  and taggings.book_id = books.id)
   and exists (select * from taggings, tags
                where tags.name = 'interesting'
                  and taggings.tag_id = tags.id
                  and taggings.book_id = books.id)

with
  tt as
  (
      select id
      from tags
      where name in ('interesting', 'fiction')
  ),
  mm as
  (
      select book_id
      from taggings join tt on taggings.tag_id = tt.id
      group by taggings.book_id having count(*) = 2
  )
select books.*
from books join mm on books.id = mm.book_id

Этот вариант, по-видимому, обеспечивает лучший план выполнения (по крайней мере, в Oracle), чем решение Питера Ланга, по следующим причинам (перефразировано из EXPLAIN PLAN):

• Соединение между tags и taggings выполняется таблица-индекс, а не таблица-таблица. Я не знаю, повлияет ли это на производительность запросов для больших наборов данных.

• План группирует и подсчитывает набор данных перед выполнением окончательного соединения с помощью books. Это, безусловно, повлияет на производительность для больших наборов данных.

Какая база данных? Это немного изменит ответ. Например, это работает с сервером sql и должно быть быстрее, потому что устраняет необходимость дважды обращаться к таблице тегов, но не будет работать на mysql, потому что mysql не выполняет CTE:

WITH taggingNames
AS
(
    SELECT tag.Name, tag.tag_id, tagging.book_id
    FROM tags
    INNER JOIN taggings ON tags.tag_id = taggings.tagid
) 
SELECT b.* 
FROM books b
INNER JOIN (
  SELECT t1.book_id
   FROM taggingNames 
   INNER JOIN taggingNames t2 ON t2.book_id = t1.book_id AND t2.Name='fiction'
   WHERE t1.Name='interesting' 
   GROUP BY t1.book_id
 ) ids ON b.book_id = ids.book_id

Подумал, что теперь, когда я это вижу, мне также нравится ответ Питера Ланга.