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

Question

[Егор Вавилов]

На странице выводится 10 последних записей из БД. Для каждой из этих записей необходимо отобразить количество комментариев из таблицы `comments`.

1 вариант. При выборке записей для каждой считать комментарии. Получается 1 запрос на выборку + 10 для подсчёта = 11 запросов.
2 вариант. При добавлении комментария увеличивать на 1 поле `count_comments` в таблице с записями. Получается только 1 запрос на выборку.

Какой из этих вариантов более предпочтительнее. Или может быть есть третий?

Answer 1

[Sergey Lerg]

LEFT JOIN и GROUP BY.
Что-то вроде

SELECT
   r.id,
   r.record,
   COUNT(c.id) CountOfComments
FROM records r LEFT JOIN
    comments c ON r.id = c.record_id
GROUP BY r.id

Comments

[deleted-mifki]

Вопрос. Допустим, таких запросов очень много и хотелось бы всё-таки не выполнять подсчёт каждый раз. Есть смысл сделать view по этому запросу — я просто не в курсе, оно будет внутри по-умному кэшироваться и пересчитываться только при необходимости?

[Sergey Lerg]

На сколько я знаю view это не кэш и смысла в нём здесь нет, но MySQL не дурак и будет кэшировать такие запросы (если они идентичны полностью).
Вы можете организовать кэширование на стороне PHP с помощью memcache(d) если оно вам так важно.

[deleted-mifki]

Точно, нашел, что view не кэшируются, спасибо. Хотя по мне, было бы логично…

[Wott]

вообще-то view быват merge и temptable. Вторые кэшируются, но ненадолго :) Честно говоря не проверял насколько долго они живут.

Answer 2

[patashnik]

Ещё как вариант:
1-й запрос на выборку комментариев;
2-й запрос вида:

SELECT COUNT(c.id) CountOfComments
FROM comments
WHERE c.record_id IN (... IDS ...)
GROUP BY c.record_id

В IN просто перечисляете ID записей, полученных из 1-го запроса.

Вообще, думаю, что при интенсивных запросах на выборку всех записей вполне подойдет 2-й вариант.

Comments

[Wott]

c.reсord_id потеряли, я не думаю что количество коментов интересно для всех постов, скорее для каждого поста в отдельности

[patashnik]

Да, точно, спасибо. Тогда будет вот так:

SELECT c.record_id, COUNT(c.id) CountOfComments
FROM comments
WHERE c.record_id IN (... IDS ...)
GROUP BY c.record_id

Answer 3

[Powerhead]

По-моему, второй вариант предпочтительней. Он полностью избавит от головной боли в будущем, когда количество данных может увеличиться. Эффективней тратить вычислительные ресурсы в моменты, возникающие реже — в данном случае, добавление/удаление комментариев происходит значительно реже, чем их просмотр.
Реализовать эту логику можно с помощью mysql триггеров, которые будут автоматически обновлять число комментариев при каждом добавлении/удалении

CREATE TRIGGER `comment_add` AFTER INSERT ON `post_comments`
FOR EACH ROW BEGIN
SET @post_id = NEW.post_id;
SET @comments_num = (SELECT COUNT(id) FROM post_comments WHERE post_id = @post_id);
UPDATE `post` SET `comments_num` = @comments_num WHERE `id` = @post_id;
END;


Полный дамп тут

Comments

[Wott]

теоретически надо бы еще и на UPDATE сделать, один фиг там тупая выборка

[Powerhead]

Дык а при апдейте число комментариев не меняется

[Wott]

А если поменять пост, к которому привязан коммент :)
Лучше перебдеть в таких вопросах как целостность данных

Answer 4

[Wott]

правильно, с точки зрения нормализации вариант №1, когда нет дублирующей и зависимой информации. Если это не критичное для производительности место, то лучше не создавать лишних сущностей.

Но если запрос такого рода часто используется или выполняется медленно, то стоит ввести дополнительное поле. Имхо лучше делать триггер на изменения в таблице с комментариями и в нем апдейтить поле в таблице с постами, сделать для него дефолт в 0 и вообще забыть о его модификации в приложении.

Answer 5

[Sild]

Сами записи в другой таблице хранятся? И по каким полям реализуется связь между таблицами?

Comments

[Егор Вавилов]

Записи хранятся, предположим, в `post` с полями `id`|`post`. А комментарии в `comments` с полями `id`|`id_post`|`comment`. Где `id_post` — ID записи из таблицы `post`, к которой добавлен комментарий.

[Егор Вавилов]

Я делаю так — селект-запросом достаю 10 строк из таблицы `post` и помещаю эти строки в массив $row. Потом циклом foreach пробегаюсь по массиву $row, и для каждой конкретной записи делаю COUNT-запрос на подсчёт комментариев.

[Sergey Lerg]

Если такой метод, то легче сделать вложенные запросы прямо в MySQL, тогда надобность в цикле исчезнет. Но это всё точно медленнее чем мой вариант.

Answer 6

[Егор Вавилов]

Спасибо. Буду тестировать различные варианты.

Answer 7

[Егор Вавилов]

Спасибо. Буду тестировать различные варианты.

Answer 8

[Николай Васильчук]

Палка о двух концах. Очевидные и наиболее адекватные варианты:
1. Выбрать посты, выбрать количество комментариев к ним. Итого 2 SELECT запроса на отображение.
2. Использовать LEFT JOIN и GROUP BY. Итого один, но медленный запрос на отображение.
3. Добавить столбец comments_count в таблицу постов. На отображение будет один быстрый запрос, но на вставку нового комментария добавится один UPDATE (или триггер), плюс нужно будет внимательно следить за этим значением (уменьшать при удалении комментария и т.п.).

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

Answer 9

[egorinsk]

Только второй вариант. Не слушайте теоретиков. MySQL все эти джойны и аггрегацию никак не соптимизирует.

Comments

[Melkij]

Вы просто не умеете их готовить.

Answer 10

[egorinsk]

Только второй вариант. Не слушайте теоретиков. MySQL все эти джойны и аггрегацию никак не соптимизирует.

Answer 11

[eaa]

Поделюсь своим опытом… в старые добрые времена, когда я был еще мелким, мне надо было считать количество комментариев к новостям. Джойны я не любил и применил первый вариант. Потом статистика увеличилась, добавились поля, алгоритмы усложнились. В результате на одну статью стало выполняться по 10-15 селектов. Потом оказалось, что надо показывать не 10 статей, а 50 — итого порядка 500 запросов на одну html-страничку. И не мне это уже понадобилось, а главному программисту, а он недолго думая решил просто вызвать мой метод, не заглядывая, как он реализован. Ну вы можете себе представить его реакцию, когда он посмотрел, как быстро sql пишет запросы в лог-файл :)

Выводы делайте сами.

Answer 12

[larikov]

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

Comments

[eaa]

Просто есть такая интересная штука — называется «Документирование». Вот тогда ничего нигде чесать не надо. И если это принято в проекте делать через триггеры — то так и надо делать, а если через код — то через код.
В свое время писал систему — там вся логика была в сторед процедурах и триггерах, а в коде чисто интерфейсные плюшечки. Так что от проекта зависит.

[larikov]

Редко где увидишь эту интересную штуку как «Документирование», да еще и актуальную.

В вашей системе проблем с тестированием кода не было? Если вдруг приходилось переименовывать поля, использованные в процедуре — в скольких местах приходилось править?
Меняем MySQL на Postgres или на sqlite — что будет с процедурами?

[eaa]

С оракла переходить на постгрес или mysql как-то не тянуло :)
С переименованием проблем не припомню, у нас на эту тему дока была — как правильно что называть, и проблема просто отсутствовала (хотя, по всей видимости дока появилась, когда проблема все-таки была). И да, дока была актуальной, это действительно редкость в наше время.

[Wott]

Я, при создании триггеров или создании вью создаю коммент в коде, где пишу сам триггер и описание. Поиск по коду — это сила, если все на месте и комменты по делу

Answer 13

[igrishaev]

Однозначно второй способ.
Если у вас используется ORM, то в классе модели комментария в методе сохранения в базу добавьте инкремент счетчика, а в методе удаления — уменьшение.