Как правильно организовать выборку данных без повторов?

Question

[StanSemenoff]

Есть первая сущность — статьи. Пока не важно как они хранятся в БД.
Есть вторая сущность — журналы. То же нет никаких ограничений, как их хранить.
Одна и та же статья может одновременно публиковаться в нескольких журналах (например, в двух).
Есть третья сущность — подписчики. Один подписчик может читать сразу несколько журналов.

Как для подписчика выбрать все опубликованные в этих журналах (которые подписчик читает) статьи упорядоченные по дате публикации и без повторов.

Самый простой способ, как я вижу:

1. Сделать таблицу со статьями:
posts
p_id, j1_id, j2_id, text, date

2. Сделать таблицу с подписками:
follows
f_id, u_id, j_id (u_id — это id пользователя из какой-то таблицы users)

3. Сделать выборку:

select posts.* from posts inner join follows on (j_id = j1_id or j_id = j2_id) where u_id = 1 order by date desc

Это запрос возвращает данные с дубликатами. Можно использовать всякие механизмы DISTINCT или GROUP BY, но это создает дополнительную операцию сортировки для удаления дубликатов.

Можно сделать с помощью UNION, но он тоже задействует механизм DISTINCT.

(select posts.* from posts inner join follows on j_id = j1_id where u_id = 1)
union
(select posts.* from posts inner join follows on j_id = j2_id where u_id = 1)
order by date desc

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

Собственно вопрос, возможно ли как-то решить эту задачу, чтобы минимизировать время нужной выборки на больших данных?

Answer 1

[Alexey]

Схема некорректна: в posts вы создаете поля для связи с журналами. А если статья сразу в 30 журналах будет, Вы будете 30 полей создавать?
Помимо этого включение в «where» условия отбора через оператор «or» может в некоторых случаях привести к катастрофическому падения производительности.

Правильно:
posts: p_id, text, date
posts_rel_journals: p_id, j_id
journals: j_id
follows: u_id, j_id

Запрос:
select posts.* from posts where p_id in
(select p_id
from posts_rel_journals
join follows on follows.u_id = 1 and follows.j_id = posts_rel_journals .j_id)

На таблицы связи ключи должны включать оба поля:
posts_rel_journals: primary_key (p_id, j_id)
follows(u_id, j_id)

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

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

Answer 2

[rPman]

А что именно тормозит при выгрузке списка с дубликатами постов? Не устраивает, с какой скоростью обрабатывает distinct, делайте дедубликацию самостоятельно, а чтобы не выгружать сами статьи, сначала получите список id а затем на их основе выгрузите нужные записи из posts
И делать это можно прямо на стороне сервера, складывая id во временную таблицу (in memory)
p.s. кстати, если количество статей за запрос сравнительно небольшое — сотни, вы можете делать это запросом select * from posts where id in (....)

Comments

[StanSemenoff]

Я не могу сказать, насколько тормозит DISTINCT для больших данных, просто была когда-то статья, в которой разница заметно описана
webbtechsolutions.com/2009/07/24/the-effects-of-distinct-in-a-sql-query/
Поэтому хотелось бы его избежать.
Хабр, кстати, также отображает статьи по подписке, где статьи могут размещаться в разных хабах. Если нас читают уважаемые админы или разработчики, было бы здорово услышать их опыт.

Answer 3

[Ruslan_Y]

А простой вариант через exists или in (select ...) вместо inner join не подойдет?
select posts.* from posts where id in (select j_id from follows where u_id = 1)
или
select posts.* from posts where exists (select j_id from follows where u_id = 1 and posts.id = j_id)

Comments

[Ruslan_Y]

Еще один вариант:
select * from posts p inner join (SELECT distinct j_id FROM follows where u_id = 1) f on (f.j_id = p.id) order by date desc

[StanSemenoff]

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