Оптимизация структуры БД, индексы, и скорость работы

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

Первая проблема: фильтры

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

Вот пример запроса:

SELECT MIN(`ps1`.price * `c1`.rate) min, MAX(`ps1`.price * `c1`.rate) max FROM shop_product p
        JOIN shop_product_features pf1
                ON p.id = pf1.product_id
        JOIN shop_product_features pf2
                ON p.id = pf2.product_id
        JOIN shop_product_features pf3
                ON p.id = pf3.product_id
        JOIN shop_product_features pf4
                ON p.id = pf4.product_id
        JOIN shop_product_features pf5
                ON p.id = pf5.product_id
        JOIN shop_category_products cp1
                ON p.id = cp1.product_id
        JOIN shop_product_skus ps1
                ON ps1.product_id = p.id and ps1.price <> 0
        JOIN shop_currency c1
                ON c1.code = p.currency
WHERE p.status = 1
        AND p.status = 1
        AND pf1.feature_id = 179 AND pf1.feature_value_id = 17127
        AND p.status = 1
        AND pf2.feature_id = 152 AND pf2.feature_value_id = 1559
        AND pf3.feature_id = 162 AND pf3.feature_value_id = 1129
        AND p.status = 1
        AND pf4.feature_id = 169 AND pf4.feature_value_id = 1168
        AND pf5.feature_id = 172 AND pf5.feature_value_id = 2157
        AND cp1.category_id IN(901,903,920,934,935,938,945,983,995,1007,3098,3108,1032,1033,1034,1035,1040,1163,1072,1999,1998,1088,1102,1103,1105,1106,1114,1117,3088,1359,2113,1733,1734,1735,1736,1737,1738,1739,3100,3095,3096,3097,2106,2112,2049,2193,1997,1990,2000,2001,2022,2073,2084,2091,2095,2097,2307,2100,2101,2105,2325,2893,3117,3118,3119,3120,3092,3091,3132,3133,3089,3094,3093,3165);

Этот запрос генерирует плагин SEO-фильтры (есть настройка для кеширования), и скорее всего shopProductsCollection. Такой-же запрос летит на COUNT(DISTINCT product.id) и сама выборка товаров.

Первое, что было сделано - все таблицы сконвертировали на InnoDB и сделали апдейт ПО. Результат улучшился, стало чуть меньше секунды.

Проблема в том, что в таблице нет индекса по feature_id, feature_value_id. Лечится путем создания индекса:

CREATE INDEX quasar_spf_perf_idx ON shop_product_features(feature_id, feature_value_id, product_id);

Как результат: было 12 секунд, стало 0.2 секунды.

Дальше - лучше.

Вторая проблема: не кеширует данные

Вот список того, что кеширует фреймворк:

1. валюты
2. категории (даже не отрендеренное дерево, а просто массив)
3. блоки
4. списки товаров

Из всего этого, хорошая идея кешировать категории, но лучше это делать сразу в отрендеренном виде, и вставлять в шаблон как строку. Туда же нужно писать все тяжелые запросы, а это выборки min/max, всякие COUNT(*) и другое. Возможно, стоит подумать над тем, чтобы кеш использовался более активно?

Третья проблема: shop_category_products

Товары в категориях хранятся не очень эффективно. Намного лучше было бы пойти "в тупую", и сделать category_id, product_id, sort, при этом для каждой категории с include_sub_categories=1, добавлять записи в таблицу, выбирая все товары из дочерних категорий, и привязывая их к текущей, образуя лесенку. Не знаю, на сколько тяжелее с точки зрения добавления и удаления товаров, но выгода заметна не вооруженным глазом.

Я не поленился, написал рекурсивный CTE, создал таблицу, где category_id и product_id пара создается для каждой дочерней категории, если include_sub_categories = 1, перенес туда все данные из старой категории.

Вот что я получил:

1. Теперь запросы строятся через WHERE category_id = N, это позволяет в один прыжок спустится по дереву BTREE, и получить все товары, а не передвигаться по листьям дерева. Да и больше нету этого IN (1, 2, 3, ..., 10050)
2. Все запросы с агрегатами выполняются значительно быстрее.
3. Не нужно делать DISTINCT. PK по category_id, product_id это гарантирует.

На моем примере, в исходной таблице, как сейчас хранит Shop-Script - 28000 записей. В модифицированной таблице - 46635. Даже если в ней будет в 10 раз больше записей, она все равно будет работать быстрее. 

Предыдущий запрос на выборку, включая добавленный индекс, занимает 0.027 sec (0.531 sec без индекса), при этом новая структура позволяет делать выборки за 0.003 sec. Такая структура ускорит не один запрос, а минимум три - на выбор мин-макс цены, на выбор товаров, на подсчет количество товаров в категории. На скорости загрузки страницы, и на нагрузке, это скажется очень позитивно.

Проблема четвертая: MyISAM

Я понимаю, что это тяжело, но я думаю уже пора переезжать на InnoDB. Там и FK CONSTRAINT, и кластеризированные индексы, и транзакции, и многое-многое другое. Да и вообще, сам движок развивается намного активней, чем MyISAM, который оставлен с целью совместимости. Не знаю, чтобы кто-то в 2018 году использовал MyISAM. 

Результат

Вот примерный результат до создания индекса, используя старую структуру таблиц, и новый результат: после создания индекса, и с новой структурой. Учтите, что на MyISAM было не 0.5 секунд, а 12, т.е. это... в 3000 раз быстрее! Надеюсь, хотя бы индексы после этого поста добавят в таблице.