Long running SELECT in MariaDB version > 10.4

SELECT FLOOR(MIN(IF (sp.id_specific_price IS NULL, (p_shop.price + IFNULL(pas.price, 0))*((IFNULL(t.rate,0)/100) + 1),IF (sp.reduction_type = 'percentage',IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1) * (1 - sp.reduction),(IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1)) - sp.reduction)))) AS min_price, CEIL(MAX(IF (sp.id_specific_price IS NULL, (p_shop.price + IFNULL(pas.price, 0))*((IFNULL(t.rate,0)/100) + 1),IF (sp.reduction_type = 'percentage',IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1) * (1 - sp.reduction),(IF(sp.price = -1, (p_shop.price + IFNULL(pas.price, 0)), (sp.price + IFNULL(pas.price, 0))) * ((IFNULL(t.rate,0)/100) + 1)) - sp.reduction)))) AS max_price

FROM `ps_product` p

INNER JOIN `ps_product_shop` p_shop ON p_shop.id_product = p.id_product AND p_shop.id_shop = 1

INNER JOIN `ps_category_product` cpro_p ON cpro_p.id_product = p_shop.id_product

INNER JOIN `ps_category_shop` cshop_p ON cshop_p.id_category = cpro_p.id_category AND cshop_p.id_shop = 1

LEFT JOIN `ps_product_attribute_shop` `pas` ON pas.id_product = p_shop.id_product AND pas.id_shop = 1

LEFT JOIN `ps_tax_rules_group` `trg` ON trg.id_tax_rules_group = p_shop.id_tax_rules_group

LEFT JOIN `ps_tax_rules_group_shop` `trgs` ON trgs.id_tax_rules_group = trg.id_tax_rules_group AND trgs.id_shop = 1

LEFT JOIN `ps_tax_rule` `tr` ON trg.`id_tax_rules_group` = tr.`id_tax_rules_group` AND tr.`id_country` = 37 AND tr.`id_state` = 0 AND tr.`zipcode_from` = 0

LEFT JOIN `ps_tax` `t` ON t.id_tax = tr.id_tax

LEFT JOIN `ps_specific_price` `sp` ON p_shop.id_product = sp.id_product AND sp.id_specific_price = (SELECT spc.id_specific_price

FROM `ps_specific_price` spc

WHERE (spc.id_product = p.id_product) AND (spc.id_shop IN (0, 1)) AND (spc.id_currency IN (0, 1)) AND (spc.id_country IN (0, 37)) AND (spc.id_group IN (0, 3)) AND (spc.from = '0000-00-00 00:00:00' OR '2022-05-31 23:12:00' >= spc.from) AND (spc.to = '0000-00-00 00:00:00' OR '2022-05-31 23:12:00' <= spc.to) AND (spc.id_product_attribute = 0)

ORDER BY spc.id_product_attribute DESC, spc.from_quantity DESC, (IF(spc.id_group = 3, 2, 0) + IF(spc.id_country = 37, 4, 0) + IF(spc.id_currency = 1, 8, 0) + IF(spc.id_shop = 1, 16, 0) + IF(spc.id_customer = 0, 32, 0)) DESC, spc.to DESC, spc.from DESC

LIMIT 1) 

INNER JOIN `ps_stock_available` stock_0 ON stock_0.id_product = p.id_product AND stock_0.id_product_attribute = IFNULL(pas.id_product_attribute, 0)

WHERE (p_shop.active = 1) AND (p_shop.visibility IN ("both", "catalog")) AND (cshop_p.id_category = 12 OR cshop_p.id_category IN (SELECT c.id_category

FROM `ps_category` c

WHERE (c.nleft >= 3) AND (c.nright <= 100)

)) AND (stock_0.quantity > 0) LIMIT 1;