Как правильно сделать счётчик объектов в категории

[Den1xxx]

Здравствуйте.
Имеется таблица

CREATE TABLE `itemcat` (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT 'Номер записи',
  `pid` int(5) DEFAULT '0' COMMENT 'Родительская категория',
  `code` varchar(255) NOT NULL COMMENT 'Код категории или штрихкод',
  `name` varchar(255) DEFAULT NULL COMMENT 'Имя категории',
  `small_icon` varchar(255) NOT NULL,
  `sort` int(5) DEFAULT NULL COMMENT 'Порядок сортировки',
  `keywords` varchar(255) NOT NULL COMMENT 'Meta keywords',
  `description` varchar(255) NOT NULL COMMENT 'Meta description',
  `desc` text COMMENT 'Описание категории',
  `full` text NOT NULL,
  `img_width` int(5) NOT NULL,
  `img_height` int(5) NOT NULL,
  `search` text COMMENT 'Поле для сортировки',
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Категории объектов''

id — id категории
pid — parent id, то есть родительская категория
Другими словами, в категориях объектов имеется бесконечная степень вложенности.
Имеется необходимость ввести счётчик объектов, принадлежащих к категории.
Вопрос к гуру, которые уже делали или сталкивались с подобным.
Как правильно сделать?
Считать на лету? Сделать счётчик в самой таблице? Сделать в отдельной таблице?
Считать на лету — при большой вложенности может быть нагрузка, хранить счётчик — при частых апдейтах тоже нагрузка. Само собой, конечная цель разработки счётчика должна быть минимальная нагрузка на БД, как при апдейте, так и особенно при выводе.
Помогите, подскажите, буду благодарен.

 

[Bezhev]

select *, COUNT(pid) as childs from itemcat group by pid

покажет кол-во вложенных.

отошлёшь вторым запросом и потом достанете кол-во, где надо.


лучше делать на лету, т.к. если хранить, то придётся слишком много манипуляций делать, чтобы сохранить консистентность.
как вариант, можно поменять хранилище на редис или мемкэш. Т.е. берёшь раз в час (на усмотрение) и обновляешь счётчик. а сам всё время лезешь в кэш.

 

[Den1xxx]

select *, COUNT(pid) as childs from itemcat group by pid

покажет кол-во вложенных.

отошлёшь вторым запросом и потом достанете кол-во, где надо.


лучше делать на лету, т.к. если хранить, то придётся слишком много манипуляций делать, чтобы сохранить консистентность.
как вариант, можно поменять хранилище на редис или мемкэш. Т.е. берёшь раз в час (на усмотрение) и обновляешь счётчик. а сам всё время лезешь в кэш.

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

 

[latteo]

Если по простому - выбираешь из таблицы всё, считаешь на php. При больших объёмах кешируешь в memcached/file с инвалидацией кеша по update/insert или по таймеру. И это рабочий кейс, который я наблюдал на очень приличном сайте.

Если по-грамотному, то садишься читать про nested sets (вложенные множества) или ему подобные:
Для просмотра ссылки Войди или Зарегистрируйся
Для просмотра ссылки Войди или Зарегистрируйся

Пробуешь запилить сам, для лучшего понимания:
Для просмотра ссылки Войди или Зарегистрируйся
Для просмотра ссылки Войди или Зарегистрируйся

Потом ищешь популярную библиотеку на гитхаб:
Для просмотра ссылки Войди или Зарегистрируйся

И так же запросто найти под любой популярный фреймворк уже готовый плагин:
Для просмотра ссылки Войди или Зарегистрируйся - демка для yii2 с возможностью добавлять новый уровень и перемешать уже созданный выше/ниже (сортировка) или переносить в подкатегорию / в родительскую. И еще куча нужных и не очень нужных наворотов. Хотя подсчёт там не запилили

 

[Den1xxx]

Если по простому - выбираешь из таблицы всё, считаешь на php. При больших объёмах кешируешь в memcached/file с инвалидацией кеша по update/insert или по таймеру. И это рабочий кейс, который я наблюдал на очень приличном сайте.

Если по-грамотному, то садишься читать про nested sets (вложенные множества) или ему подобные:
Для просмотра ссылки Войди или Зарегистрируйся
Для просмотра ссылки Войди или Зарегистрируйся

Пробуешь запилить сам, для лучшего понимания:
Для просмотра ссылки Войди или Зарегистрируйся
Для просмотра ссылки Войди или Зарегистрируйся

Потом ищешь популярную библиотеку на гитхаб:
Для просмотра ссылки Войди или Зарегистрируйся

И так же запросто найти под любой популярный фреймворк уже готовый плагин:
Для просмотра ссылки Войди или Зарегистрируйся - демка для yii2 с возможностью добавлять новый уровень и перемешать уже созданный выше/ниже (сортировка) или переносить в подкатегорию / в родительскую. И еще куча нужных и не очень нужных наворотов. Хотя подсчёт там не запилили

Конечно же, большинство из этого я читал. От nested sets отказался 3 года назад, когда делал вложенные категории.
И сейчас работает через 1 SQL выборку и рекурсивную функцию на PHP.
В категории товаров редко бывает вложенность более 3, и nested sets — при выборке ощутимого результата не дадут, а вот код сильно усложняют. Система кеширования в системе своя, родная, никаких сторонних не нужно. По умолчанию отключена.
Вот сейчас планирую запилить подсчёт во вложенных категориях. Код не нужно, пример не нужно. Мне просто нужен совет от людей, которые делали что-то подобное.

Возможно, я не точно выразился. Начнем сначала.

Нужен счётчик объектов в категории, а категории теоретически могут иметь бесконечную вложенность.
Работать должно почти на любом хостинге, потому MySQL, а не PostgreSQL
Три варианта реализации.
1. При установке счётчика в категории. При добавлении объекта в категорию придётся пересчитывать счётчики всех родителей.
При переносе подкатегории в подчиненность другой категории — получится лавина апдейтов
В момент апдейта одновременно будут стучаться в таблицу выборки от других пользователей и может получиться deadlock
Тем не менее, в реализации это самый простой путь.
2. При установке счётчика в отдельную, специально предназначенную для этого таблицу, deadlock мало вероятен.
Однако получится лишний запрос при выборке. Пока сам склоняюсь к этому варианту.
3. Считать нагрузку «на лету». Ну это имхо насилие над сервером. Но может кто знает вариант с малой нагрузкой? Надежды мало, но вопрос положено задать.

 

[Bezhev]

Мне кажется, что если изначально проектировать так, чтобы потом подставлять костыли с кешем

глупости, кэш костыль? о чём ты?

Даже движок нулледа может работать с редиской, чтобы в неё складывать кол-во сообщений! а его (xenforo) пишут не глупые люди с n-годами опыта за плечами.

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

Возможно, я не точно выразился. Начнем сначала.

Ответ был дан выше, мной и latteo.