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

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

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

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

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

На этой странице https://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html указано, что MySQL поддерживает ACID. Для того, что ты написал нужно включить doublewrite buffer. Транзакцию начинать не обязательно из-за autocommit mode.

Это называется autocommit mode

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

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

Включить в my.conf https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_... проверять не нужно :) show variables like «innodb_doublewrite»;

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