Жесть на самом деле ;-)
Смотрите, начнем с того что каждый join это произведение матриц. Есть смысл как можно более сильно зарезать условие join, те вместо
LEFT JOIN oc_product_attribute p2a0 ON (p2a0.product_id=p2a.product_id)
писать
JOIN oc_product_attribute p2a0 ON (p2a0.product_id=p.product_id and p2a0.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%')
Обратите внимание что я ссылаюсь на таблицу p - в ней уже есть product_id
И убеждаемся что в oc_product_attribute есть индекс (product_id, attribute_id, text)
Далее:
Хороший способ оптимизировать запросы - разбить их на несколько, в данном случае это вполне возможно.
6 запросов вида
select product_id from oc_product_attribute p2a0 where (p2a0.product_id=p.product_id and p2a0.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%')
Дальше в ЯП выбираем product_id которые есть во всех 6 выборках.
Дальше
select * from oc_product where product_id IN (1,2,3 ...)