LINUX.ORG.RU

MariaDB/MySQL оптимизировать подсчёт на основе foreign key

 , ,


0

1

Предположим имеется таблица items (id, name, desc, cat_id) и таблица cats (id, parent_id, name) где items.cat_id это foreign key указывающий на cats.id а cat.parent.id либо == NULL либо указывает на cats.id. Требуется выводить дерево категорий с количеством items относящейся к каждой из них.Т.к. размеры таблиц items и cats велики делать select count(*) from items where cat_id = id для каждой категории тормозит. Возможно ли как то оптимизировать эту ситуацию, например добавить колонку cats.totalCount и запрограммировать MariaDB/MySQL выполнять автоматически код для изменения cats.totalCount при добавлении/удалении item или изменении её cat_id? Конечно это можно сделать и в коде работающем с БД но если есть возможность сделать это в самой БД это было бы надёжнее для сохранения структуры данных.

Возможно ли как то оптимизировать эту ситуацию, например добавить колонку cats.totalCount и запрограммировать MariaDB/MySQL выполнять автоматически код для изменения cats.totalCount при добавлении/удалении item или изменении её cat_id?

http://dev.mysql.com/doc/refman/5.7/en/triggers.html

kiotoze ★★★★ ()
Ответ на: комментарий от kiotoze

В триггерах предусмотрена такая ситуация что если например после добавления записи в items система падает (отключается питание например или какие то другие проблемы с железом)? В этом случает триггер не будет выполнен и cats.totalCount будет содержать ошибочное значение. Можно ли как то недопустить такой ситуации с помощью Start Transaction.

iluha16 ()
Ответ на: комментарий от iluha16

не уверен, но логично предположить, что если поддерживаются транзакции (двиг innodb) и даже без явного использования транзакциями, транзакция будет автокомитится после отработки всех триггеров. Если кто в курсе, подтвердите сие

kiotoze ★★★★ ()
Ответ на: комментарий от goingUp

Плохо что в той части которая описывает triggers об этом ни слова не сказано. Используется MariaDB, ENGINE=InnoDB. Как это включить и желательно бы тогда ещё из скрипта как то проверять присутствие этой опции раз она так важна для data consitency.

iluha16 ()
Ответ на: комментарий от goingUp

Я вообще подумываю взять лучше PostgreSQL, подзапутался с этими всеми режимами mysql. Вроде бы слышал что PostgreSQL более строго относится к сохранности данных, но меньше с ней знаком чем с MySQL/MariaDB.

iluha16 ()

PostgreSQL

WITH RECURSIVE tree_cat as (
select name, parent_id from cats c where c.id = 10 
union all
select t.name || ' ' || c.name, c.parent_id from cats c join tree_cat t on c.id = t.parent_id 
)
select name from tree_cat order by name desc limit 1
RA ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.