Как правильно организовать ленту постов?

Question

[neronru]

Здравствуйте, очень нужен ваш совет в плане правильной организации БД/хранилища для ленты постов. Суть заключается в следующем: нужно вывести все лайкнутые статьи,всех на кого ты подписан, но при этом нужно группировать, так, что позиция статьи зависит от первого лайка....Сейчас на примере попытаюсь это показать.
Есть у нас пользователи A,B,C. A подписан на B и на C. Пусть пользователи B и C лайкнули одну статью. (Сначала B потом C). Так вот в ленте позиция этой статьи должна быть по первому лайку, то есть по времени лайка B.
Сначала были сделаны две таблицы в MySQL:

1. user_relationships(idUser INT(11), idSub INT(11))
   
2. post_likes (id INT(11), idPost INT (11), idUser INT(11), size INT(11), date DATETIME)
   

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

SELECT  min(id) as id, idPost, posts_likes.idUser 
FROM `post_likes`,`users_relationships` WHERE 
    post_likes.idUser=users_relationships.idSub AND
     users_relationships.idUser=$user_id AND post_likes.size >= 0 
GROUP BY idPost ORDER BY id DESC

Но с заполнением таблицы post_likes время запроса стало резко увеличиваться, я посмотрел explain, и стало понятно, что запрос не может использовать индексы...Посмотрев документацию мускула, я понял, что это происходит из-за того что группировка и сортировка производятся по разным полям. Может быть вы подскажите, как это можно преодолеть?
Так же возникла идея генерировать ленту в Redis, ну и вообще полностью перенести эти две таблички туда. Но тут так же проблема с генерацией ленты...Структура в Redis'е такая: для каждого пользователя хранятся все его лайкнутые статьи. Для того чтобы сгенерировать ленту, мы просто используем zunionstore для каждого на кого подписан пользователь, и потом сохраняем ленту для пользователя. Но тут возникает проблема с удалением статей, если пользователь отписался(уж очень много обходить), и с большим количеством перерасходывания памяти.(Частично генерировать не получится, так как может быть такая ситуация: пользователь B лайкнул давным давно статью, а пользователь C только что, и может получится так, что пост появится два раза...)
Очень прошу помочь, как все таки сделать правильнее. Заранее спасибо.

Answer 1

[Станислав Макаров]

1) не могу понять зачем вам id и size в таблице post_likes. Для регистрации факта и времени лайка достаточно этого: (idPost, idUser, date) с ключом (idPost, idUser).
2) почему ORDER BY id а не по дате?
3) сделайте нормальные нужные вам индексы, включающие поля по которым вы фильтруете и сортируете (с указанием порядка сортировки).

Под большой нагрузкой с такой задачей лучше графовую базу пробовать (когда много связей, как у вас с подписчиками).

Comments

[neronru]

1)size это коэффициент зависящий от кармы пользователя, на момент лайка
2) ну тут без разницы по чем сортировать, ибо PRIMARY это id, а сортировка по целочисленному параметру быстрее, чем по дате.
3) В том то и дело, что я группирую по idPost, а сортирую по по id...И из-за этого индекс не может быть применен, к тому же join происходит по первой таблице...Так как сначала нужно сделать выборку всех статей, которые лайкнули пользователи, а потом группировку

[Fortop]

neronru: я бы решил вопрос вне sql. Выбрал бы список с критериями сортировки и отсортировал уже в коде.

[Станислав Макаров]

neronru 1) ясно, окей
2) вот зачем вам id вы не ответили. Я придерживаюсь того, что он вам не нужен. Сортировка по дате с идексом будет достаточно быстрая в сравнении с интом (не знаю, может конечно с mysql конкретно с этим проблемы, не слышал). Дело в том, что для вас потом будет гемором этот int - вам нужна гарантия его последовательного роста, и если вы когда-либо захотите базу бить на партишены, тогда у вас будут проблемы. Вам для каждого нового id нужно знать id предыдущий, раз вы хотите по нему упорядочить. Для даты вам ничего знать не надо: когда чел лайкает вы пишете в базу то, что вам УЖЕ известно: кто лайкнул, что лайкнул, и когда.
3) еще раз повторю: сделайте себе индексы по полям, по которым надо, https://dev.mysql.com/doc/refman/5.0/en/create-ind..., статей в инете по тому, какие колонки и как выбрать - ну просто миллион mysqldba.blogspot.ru/2008/06/how-to-pick-indexes-f...
Вне SQL я делать не советую, на то базы данных и созданы, чтобы на их уровне отфильтровать большую часть ненужной инфы и оставить 0.00..1% того что надо прямо сейчас.

[neronru]

Я вам объясняю, с текущим запросом индекс не заюзать, так как в explain стоит первая таблица user_relationships, и уже к ней джойнится posts_likes. Изменять порядок нельзя, так как сначала нужно выбрать всех подписчиков, а потом уже джойнить и группировать посты...Так как я выбираю минимальный среди пользователей.Так тут ещё и нужно группировать по постам, а сортировать по дате(или id). В доках мускула точно написано, что если группируется и сортируется по разным колонкам, то индекс нельзя заюзать. Сами индексы по которым выборка происходит стоят, НО сама сортировка и группировка уже занимает довольно приличное время, именно поэтому и пришлось искать альтерантивное решение