Как (быстро) выбрать книги, у которых есть все нужные авторы?

Question

[asdzxx]

Есть таблица, которая содержит список авторов каждой из книг:

book_id | author_id 
1 | 1
2 | 1
2 | 2
3 | 3
...

Мне нужно понять принцип, по которому строится запрос вида "все книги, которые написали одновременно эти авторы" - для общего случая с количеством авторов от ноля до бесконечности.

Лучшее, что я смог придумать - это вариант с GROUP BY / HAVING:

SELECT * FROM table
WHERE author_id = x 
OR author_id = y
OR author_id = z /* каждый автор - один OR */
...
GROUP BY book_id
HAVING count(*) = 3 /* общее количество авторов */

Запрос работает, но уже на сотнях тысяч записей в таблице начинает выполнятся несколько секунд (естественно, с проставленным индексом - (author_id,book_id)).

Я могу придумать запрос с JOIN - но он работает ещё медленнее:

SELECT * FROM table as t1 
JOIN table as t2 ON t1.book_id = t2.book_id
JOIN table as t3 ON t1.book_id = t3.book_id
/* каждый автор - один джоин */
WHERE t1.author_id = x 
AND  t2.author_id = y
AND t3.author_id = z

Возможно, я сильно усложняю, и есть какой-то более простой вариант для такого запроса?
Если нет, то какие есть варианты улучшить его скорость?

Comments

[Ипатьев]

А индексы-то хоть какие-то в таблице есть, я стесняюсь спросить?

[Akina]

Единственный способ ускорить - это правильная индексация данных. Попробуйте создать индекс с обратным порядком полей (book_id, author_id) и смотрите в EXPLAIN, какой индекс используется. Попробуйте также и такой вариант запроса:

SELECT * 
FROM table
GROUP BY book_id
HAVING SUM(author_id IN ('x', 'y', 'z')) = 3

[Ипатьев]

Akina, мне всё-таки кажется что с джойнами самый простой план запроса будет
Не люблю я запросы с группировкой.

Чуть усложнится запрос от этого игрушечного примера - и весь план пойдёт раком.

Но динамически собирать запрос с группировкой конечно проще.

[Sergey В.]

Я правильно понимаю, что есть список id авторов и нужно вывести список id написанных ими книг (без повторов)?

[asdzxx]

Ипатьев, написано прямо в вопросе:

Запрос работает, но уже на сотнях тысяч записей в таблице начинает выполнятся несколько секунд (естественно, с проставленным индексом - (author_id,book_id)).

BasiC2k,
Нужно вывести список книг, которые написали оба (или все три, или все N) авторов. ("Книги, которые написали Керниган и Ритчи вместе").

Akina,
Действительно, с другим порядком полей стало работать значительно быстрее (до секунды при 5 млн строк в таблице и limit 10000 в запросе). Спасибо.

[Ипатьев]

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

Теперь ещё осталось понять, зачем нужен limit 10000 в запросе

[Sergey В.]

asdzxx, у меня получается такой запрос (если я правильно понял задание):

SELECT DISTINCT book_id
FROM test_table
WHERE author_id IN (1,3)

Выводит список книг, актором которых одновременно является author_id=1 и author_id=3

Обратите внимание - слово table зарезервировано и его нежелательно использовать в названии таблицы.

[Akina]

Ипатьев, да, план с джойнами будет попроще, но запрос получится совершенно нерасширяемый.

[mayton2019]

В реальности - текстовый поиск по полному названию книги был-бы быстрее всего. Разумеется при условии что построены текстовые индексы.

Еще ради разминки мозга я предлагаю рассмотреть другую задачу. Взять к примеру 3х авторов и посчитать пересечение их книг. Можно даже решать не в SQL а просто в языках программирования.

[Ипатьев]

BasiC2k, Ваш запрос выводит список книг, автором которых является ЛИБО author_id=1, ЛИБО author_id=3, ЛИБО одновременно author_id=1 и author_id=3

[Sergey В.]

Ипатьев, да, спасибо за справедливое замечание. Тогда я бы построил запрос так:

SELECT book_id, COUNT(*) AS A_Count
FROM test_table
WHERE author_id IN (1,3)
GROUP BY book_id
HAVING A_Count = 2

[Ипатьев]

BasiC2k "...и вышел таки обратно на Дерибасовскую" - это запрос и написан в вопросе :)
У автора вопрос не в том, как получить все книги, а в том как это сделать быстро

[Sergey В.]

Ипатьев, "ну, тогда наши полномочия... всё..." ))

[asdzxx]

Ипатьев,
> В вопросе ничего не написано про индексы. Понятно что без индексов будет тормозить.
В вопросе ясно написано, что стоит составной индекс (author_id, book_id).

> Вы план запроса хоть смотрели? Про команду EXPLAIN слышали?
Да, смотрел. Этот индекс и использовался в запросе.

> Теперь ещё осталось понять, зачем нужен limit 10000 в запросе
Для того, чтобы протестировать его скорость работы на разных объемах данных.