Для ускорения множественных джойнов - добавить доп. индекс по полям feature_value_id, sku_id в таблице shop_product_features
Пример запроса есть здесь: https://support.webasyst.ru/forum/39275/baza-padaet-mnozhestvennye-zaprosy/
Еще пример запроса с кучей джойнов(которые без доп индекса вы полняется по несколько сотен секунд или вообще вешает базу):
EXPLAIN SELECT COUNT(DISTINCT p.id) FROM shop_product p
JOIN shop_category_products cp1
ON p.id = cp1.product_id
JOIN shop_product_skus ps1
ON ps1.product_id = p.id
JOIN shop_product_features pf1
ON p.id = pf1.product_id AND pf1.feature_id = 43
JOIN shop_product_features pf2
ON p.id = pf2.product_id AND pf2.feature_id = 44
JOIN shop_product_features pf3
ON p.id = pf3.product_id AND pf3.feature_id = 71
JOIN shop_product_features pf4
ON p.id = pf4.product_id AND pf4.feature_id = 3
JOIN shop_product_features pf5
ON p.id = pf5.product_id AND pf5.feature_id = 18
JOIN shop_product_features pf6
ON p.id = pf6.product_id AND pf6.feature_id = 21
JOIN shop_product_features pf7
ON p.id = pf7.product_id AND pf7.feature_id = 72
JOIN shop_product_features pf8
ON p.id = pf8.product_id AND pf8.feature_id = 40
JOIN shop_product_features pf9
ON p.id = pf9.product_id AND pf9.feature_id = 4
JOIN shop_product_features pf10
ON p.id = pf10.product_id AND pf10.feature_id = 8
JOIN shop_product_features pf11
ON p.id = pf11.product_id AND pf11.feature_id = 115
JOIN shop_product_features pf12
ON p.id = pf12.product_id AND pf12.feature_id = 136
JOIN shop_product_features pf13
ON p.id = pf13.product_id AND pf13.feature_id = 137
JOIN shop_product_features pf14
ON p.id = pf14.product_id AND pf14.feature_id = 139
JOIN shop_product_features pf15
ON p.id = pf15.product_id AND pf15.feature_id = 140
JOIN shop_product_features pf16
ON p.id = pf16.product_id AND pf16.feature_id = 144
WHERE p.type_id IN (1,2,3,4,5,6,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,30,31,32)
AND p.status = 1
AND cp1.category_id IN(772)
AND ps1.available > 0 AND ps1.status > 0
AND (ps1.primary_price) <= 1283
AND pf1.feature_value_id IN (2721) AND (pf1.sku_id IS NULL OR pf1.sku_id = ps1.id)
AND pf2.feature_value_id IN (2941) AND (pf2.sku_id IS NULL OR pf2.sku_id = ps1.id)
AND pf3.feature_value_id IN (3827) AND (pf3.sku_id IS NULL OR pf3.sku_id = ps1.id)
AND pf4.feature_value_id IN (11) AND (pf4.sku_id IS NULL OR pf4.sku_id = ps1.id)
AND pf5.feature_value_id IN (592) AND (pf5.sku_id IS NULL OR pf5.sku_id = ps1.id)
AND pf6.feature_value_id IN (5490) AND (pf6.sku_id IS NULL OR pf6.sku_id = ps1.id)
AND pf7.feature_value_id IN (7762) AND (pf7.sku_id IS NULL OR pf7.sku_id = ps1.id)
AND pf8.feature_value_id IN (2499) AND (pf8.sku_id IS NULL OR pf8.sku_id = ps1.id)
AND pf9.feature_value_id IN (17515) AND (pf9.sku_id IS NULL OR pf9.sku_id = ps1.id)
AND pf10.feature_value_id IN (804) AND (pf10.sku_id IS NULL OR pf10.sku_id = ps1.id)
AND pf11.feature_value_id IN (5849) AND (pf11.sku_id IS NULL OR pf11.sku_id = ps1.id)
AND pf12.feature_value_id IN (5986) AND (pf12.sku_id IS NULL OR pf12.sku_id = ps1.id)
AND pf13.feature_value_id IN (5996) AND (pf13.sku_id IS NULL OR pf13.sku_id = ps1.id)
AND pf14.feature_value_id IN (6091) AND (pf14.sku_id IS NULL OR pf14.sku_id = ps1.id)
AND pf15.feature_value_id IN (6125) AND (pf15.sku_id IS NULL OR pf15.sku_id = ps1.id)
AND pf16.feature_value_id IN (6191) AND (pf16.sku_id IS NULL OR pf16.sku_id = ps1.id);После добавления индекса fv_sku(feature_value_id, sku_id) в таблице shop_product_features
- время выполнения стало 60 мс:
7 комментариев
Даже если эти поля есть в других индексах, что видно на скрине - для mysql/mariadb(myisam) очень часто важен порядок полей в индексе.
Если в SQL запросах для WHERE прописаны условия сперва feature_value_id, а потом для sku_id - то для ускорения/быстрого выполнения желательно(важно) в базе иметь индекс из полей той же последовательности.
А есть sql запрос на добавление индекса?
db_name - замените названием своей базы.
ps: и предварительно сделайте бекап базы.
Протестировано на базе MariadB 10.5.18
Можно конечно попробовать изменить порядок полей в существующем индексе feature.
Но при условии, что в shop-script больше нигде не используется тот порядок полей - что задан в индексе feature.
Надеюсь, разрабы обратят внимание на это.
Так как для магазинов с 50-100 тысяч значениями хар-тик и более это важно ;)
Наконец то, еще один единомышленник, и кто хотя бы сколько то заботиться об ускорении работ по базе. Единственное Наил, можно вопрос, а с MariaDB 10.5 не имеешь проблем с некоторыми плагинами по не совместимости запросов, насколько мне известно там требования к построению запросов более строгий, чем в 10.3 и 10.1 соответственно. Просто с MariaDB - сталкивался что часть плагинов делает кривые запросы, которые не проходят по синтаксису даже MySQL5.7, не говоря о MySQL8.0.
Решается включением менее строгих правил построения запросов, но тогда вся производительность тоже падает. Пока самым стабильных для любой сборки плагинов считаю, из практики MySQL5.6. Причем у части заказчиков их переваливает за 100 штук.
Также с удовольствуем поделюсь своими наработками по ускорению и готов взять на вооружение ваши. У меня есть на части проектов и MariaDB 10.3.
Пока вдогонку посоветую несколько параметров для BD.
# эти строчки чтобы засекать запросы, которые выполняются более 1 секунды, причем куда складываете создайте файл, с правами mysql | mysql - владелец | группа (иначе получите не возможность запуска BD)
tmpdir = /dev/shm
# эта настройка нужна чтобы временные таблицы, строились в оперативке, т.к. этот раздел в Linux - и есть оперативка
max_allowed_packet=1M
# важен уменьшенный для перехода на InnoDB - если не используете MyIsam, либо их крайне мало
# настройки для ускорения, но не особо значимые
# количество потоков, для InnoDB