Подсчет количества объектов в дочерных категориях

Цитата ("ArtPro"):

ой не факт. sql сам не дурак, да еще если ключи нормально сделаны...

Фишка в том, что производительность системы в целом сначала упирается в производительность БД, а потом в производительность жестких дисков (это в общем случае).
Я все-же всегда стараюсь по максимуму разгрузить БД.

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

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



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

тоесть составить карту категорий:

select t1.`id`, t2.`root`, (select count(t2.`id`) FROM cats t2 where t2.`root` = t1.`id`) as childs FROM cats t1

должно получится:

$categoryMap = array(
=> array('parent' => , 'ownerChilds' => , 'allChilds' => 0),
);

и рекурсивно полученный массив пройти и подсчитывать все дочерные чтобы заполнить 'allChilds'..

И в конце, когда все родительские установлены (тоесть все внутренние пройдены), создавать sql-запрос который в отдельное поле будет писать количество всех дочерних документов для этого id..

..Как один из варианта решения.. Только что проснулся, вроде ниче не напутал

Цитата ("Miller_time"):

Фишка в том, что производительность системы в целом сначала упирается в производительность БД, а потом в производительность жестких дисков (это в общем случае).
Я все-же всегда стараюсь по максимуму разгрузить БД.

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

Решил сделать все таки подсчет по крону в нужное время.

Вот первый вариант кода который получился:

$list = DB::select("SELECT cats.id AS cat_id, cats.title, (SELECT COUNT(objects.id) FROM objects WHERE objects.cat=cats.id) AS obj_count FROM cats  GROUP BY cats.id");//получаем реальное количество объектов для всех категорий без учета дочерних


$max_level = 0;

$cats_list = array();

$count_tot = 0;

foreach($list as $k=>$v){//определяем максимальный уровень вложенности и общее количество объектов

	$max_level = max($v, $max_level);

	$cats_list] = $v;

	$count_tot+=$v;

} 

$cats_list_init = $cats_list;//сохраняем полученный список, до того как будут сумироватся объекты дочерних категорий

foreach($cats_list as $k=>$v){//сумируем количество объектов дочерних категорий для всех категорий

	$childs = $cat->getAllChild($root_id=$v);//получаем все дочерние категории текущей категории

	foreach($childs as $child){

		$cats_list+=$cats_list;//сумируем

	}

}


foreach($cats_list as $k=>$v){//убираем категории в которых не были сделаны изменения(нет объявлений в дочерних категориях), для снижения количества запросов UPDATE

	if($cats_list==$cats_list_init)

		unset($cats_list);

}


foreach($cats_list as $k=>$v){//записываем обновленную статистику в базу

	DB::update("UPDATE cats SET obj_count=".$v." WHERE id=".$k);

}

Есть еще идеи на счет оптимизации, потом покажу.
В базе сейчас 380! категорий. Правда объектов не много(кстати от этого тоже зависит время выполнения первого запроса?). Время выполнения всего скрипта примерно 0.07 сек.

Процедурку хранимую написать слабо?

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

P.S.
Цитата:

Время выполнения всего скрипта примерно 0.07 сек.

Но если кол-во категорий сильно не измениться - тогда норма

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

ArtPro, и что даст в данном случае хранимая процедура? Вы код внимательно смотрели?
Тут один SELECT и столько UPDATE, сколько в скольких категорий были добавлены объекты.

Задача усложнилась. Кроме категорий, есть еще города. Надо посчитать количество объектов в категориях, еще с учетом выбранного города.
Думал держать отдельную таблицу, с 2-мя ключами: категория и город(местоположение, которое тоже имеет вложенность). Может у кого есть другие идеи?

Цитата ("WebDesignStudio"):

Задача усложнилась. Кроме категорий, есть еще города. Надо посчитать количество объектов в категориях, еще с учетом выбранного города.
Думал держать отдельную таблицу, с 2-мя ключами: категория и город(местоположение, которое тоже имеет вложенность). Может у кого есть другие идеи?

А много ли городов, и какого характера вложенность у местоположения?

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

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

Создать две вспомогательных таблицы:

1. Связка между категориями и объектами:
- id категории
- id объекта

Причем для каждой категории будут присутсвовать все объекты, включая объекты подкатегорий.

2. Связка между городами и объектами:
- id местоположения (город, или область, и т.д., или я не знаю какие у Вас в проекте заданы принципы для местоположения)
- id объекта

Тут также, для каждого местоположения будут присутсвовать все объекты, включая объекты вложенного уровня местоположений.

===
В результате, для каждого запроса с сайта будете делать запрос к базе данных к этим двум таблицам.
Результат выполнения запроса обязательно кешируйте по ключу категория+местоположение, так как наверняка подавляющее большинство запросов будет к одним и тем же категориям и городам (например, Москва/все категории).

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

2-мя таблицами не получится.

Вот как у меня примерно:

1. Таблица категорий
id - идентификатор
root_id - идентификатор родителя
level- уровень

2. Таблица местоположений
id - идентификатор
root_id - идентификатор родителя
level- уровень

то есть эти 2 таблицы с неограниченной вложенности.

3. Также есть таблица объектов
id - идентификатор
cat - идентификатор категории
loc- идентификатор местоположения

Предложенный вами способ не подойдет, так как тут идет связка по категории и местоположении одновременно. Если не выбрана категория и город, на главной посилання для категория 1-го и 2-го уровня делается подсчет для всех городов. А если выбран город, то только для того города. То же самое, если например выбрана категория посилання. Как то вот так.

Почему же мое решение не подойдет? При описанной Вами структуре данных еще и как подойдет.

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

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

Примечание 1: Тип поля "id категории" и "id города" делайте минимального размера, и определяйте как NOT NULL. Скорее всего в Вашем случае это будет SMALLINT. Это принципиально важно для производительности.
Примечание 2: Очень внимательно отнеситесь к индексированию этих двух таблиц, стройте их в зависимости от того, какие запросы используются. Это также принципиально важно для производительности.
Примечание 3: Процедура восстановления БД из бакапа будет немного сложнее, а именно: 1. восстановление схемы (DDL), 2. выключение триггеров 3. восстановление данных (DML) 4. включение триггеров.
Примечание 4: Если Ваша БД не обеспечивает целостность данных, на всякий случай нужен будет отдельный скрипт для заполнения этих таблиц (хотя, в идеале, он никогда не будет запускаться). Этот скрипт может пригодиться в случае сбоя триггеров (мало ли, может быть админ будет БД на другой сервер переносить и ошибется), или если кто-то кривыми руками в БД залезет и выключит триггеры.

Вот примерный запрос, который выведет количества объектов для каждой категории первых двух уровней, с учетом города. С использоваинем предложенных мной таблиц-связок.
Запрос я делал без проверки на MySQL, но, надеюсь, смысл будет понятен.

SELECT c.id, COUNT(oc.object)
FROM category c
INNER JOIN object_category oc ON oc.cat = c.id
INNER JOIN object_location ol ON ol.object = oc.object AND ol.loc = ?
WHERE c.level <= 2
GROUP BY c.id;

Да, данный запрос не совсем легкий, потому и предлагаю кешировать результат.
Кстати, сколько хоть всего объектов ожидаете иметь в БД? Надеюсь, не миллионы? (если миллионы — в этом случае решение будет более сложным)

tvv, я уже выше писал что у категорий и местоположений есть вложенность(теоретически она может быть бесконечная) и надо учесть также объекты которые находится в дочерних категориях. Если я вас правильно понял, то эти ваши таблицы получатся очень большими, так как надо будет туда записать все возможные комбинации город+категория но с учетом дочерних категориях. И что будет если у одного объекта меняется категория? Или одна категория, переносится в другую(у меня и это есть).

Цитата ("tvv"):

Причем для каждой категории будут присутсвовать все объекты, включая объекты подкатегорий

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

object_id
cat_id
loc_id

Цитата ("WebDesignStudio"):

tvv, я уже выше писал что у категорий и местоположений есть вложенность(теоретически она может быть бесконечная) и надо учесть также объекты которые находится в дочерних категориях. Если я вас правильно понял, то эти ваши таблицы получатся очень большими, так как надо будет туда записать все возможные комбинации город+категория но с учетом дочерних категориях. И что будет если у одного объекта меняется категория? Или одна категория, переносится в другую(у меня и это есть).

Нет, не нужно хранить все комбинации локейшены+категория. Таблиц — две. В одной — категории+объекты, в другой — города+объекты.
Например, для каждой категории будут поставлены в соответствие все объекты из этой категории, и все объекты из дочерних категорий.
Аналогично для каждого локейшена, но уже в таблице локейшенов.
То есть не нужно хранить комбинации, надеюсь, теперь понятна мысль?

Если добавится новая категория и т.д. то ничего не произойдет, так как в ней нет объектов. Когда добавится объект в эту категорию, триггер добавит соответствующую запись в таблицу-связку категорий и объектов для текущей категории, и для каждой категории вышестоящего уровня. Всё это будет прозрачно для программиста, PHP кода для этого писать не нужно, это берет на себя БД.

Цитата ("WebDesignStudio"):

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

object_id
cat_id
loc_id

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

Могу допустить, что например размер таблицы-связки объектов и категорий не будет превышать количество объектов в 3 раза (в реальности — меньше, это зависит от набора данных), если средняя вложенность категорий будет составлять 3. Учитывая, что в такой таблице лишь две колонки типа SMALLINT (или SMALLINT и INT, если число объектов превышает 64K), для выборки будет достаточно исключительно индексов, и только их. То есть обращения к данным помимо индексов не будет, и производительность будет весьма высока.

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

Цитата ("WebDesignStudio"):

И что будет если у одного объекта меняется категория?

Если у объекта меняется категория, то Вам достаточно будет вызвать из PHP кода:

UPDATE object SET cat = <new_category_id> WHERE id = <object_id>;

Все. Остальное возьмут на себя триггеры. А именно, сработает триггер на UPDATE таблицы object. Код триггера сравнит новую категорию и старую, и, если они отличаются, то выполнит следующее:

DELETE object_category WHERE object = <object_id>;
INSERT object_category(category, object) VALUES(<new_category_id>, <object_id> );

Также, триггер пройдется в цикле по всем вышестоящим категориям по цепочке, выполняя для каждой:

INSERT object_category(category, object) SELECT root_id, <object_id> FROM category WHERE id = <category_id>;

Надеюсь, принцип понятен?

Цитата ("WebDesignStudio"):

Думал держать отдельную таблицу, с 2-мя ключами: категория и город(местоположение, которое тоже имеет вложенность).

Я объясню, почему я привел свой способ выше, а не развиваю предложенный Вами способ из этой Вашей цитаты.

Дело в том, что мне не известно количество объектов и городов. Мой способ будет прекрасно работать, если городов много, а объектов относительно не очень много (не миллионы).
Ваш же способ будет требовать таблицы, размер которой будет стремиться к <количество_категорий> * (<количество_локейшенов> + 1), каждое поле из которой нужно будет периодически просчитывать отдельно в PHP скрипте.
То есть, Ваш способ может прекрасно работать в том случае, если городов будет очень мало, но на большом их количестве это будет весьма затратно.
По сути, код Вашего PHP скрипта должен будет выполняться N+1 раз, где N = число локейшенов, и так будет при каждом запуске.

Сегодня пришел в голову новый способ, лишенный предыдущих недостатков. Теперь не будет больших таблиц-связок с объектами.
Итак, потребуется создать две дополнительные таблицы:

1. Развернутая таблица категорий category_expand
- id
- child_id

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

2. Развернутая таблица локейшенов location_expand
- id
- child_id

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

===
Вот запрос, который выведет количества объектов для каждой категории первых двух уровней, для всех городов:

SELECT c.id, COUNT(o.id)
FROM category c
INNER JOIN category_expand ce ON ce.id = c.id
INNER JOIN object o ON o.cat = c.id OR o.cat = ce.child_id
WHERE c.level <= 2
GROUP BY c.id;

===
А вот запрос, который выведет количества объектов для каждой категории первых двух уровней, с учетом города (обозначено как '?':

SELECT c.id, COUNT(o.id)
FROM category c
INNER JOIN category_expand ce ON ce.id = c.id
INNER JOIN location_expand le ON le.id = ?
INNER JOIN object o ON (o.cat = c.id OR o.cat = ce.child_id) AND (o.loc = ? OR o.loc = le.child_id)
WHERE c.level <= 2
GROUP BY c.id;

Скорее всего данный SELECT запрос потребует оптимизации, но это уже детали.
Результат желательно кешировать.