Как оптимизировать такой SQL запрос?

EXPLAIN SELECT product_id FROM( ....

Жесть на самом деле ;-)

Смотрите, начнем с того что каждый 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 ...)

Нужно определить какой LEFT JOIN ложит ваш запрос. Потом проверить - индексы, если нет, сделать. Если есть, тогда подумать, может стоит добавить избыточность. Еще тут проблема в LIKE. Может есть смысл использовать какой поисковой движок, Elastic, Sphinx.

1. замените все left join на inner join. Это не обязательно принесет автоматически производительность, но будет правильнее. Потому что AND cp.path_id IN (3558) делает из всех "left join" "inner join".
2. уберите из "FROM" все соединения oc_product_attribute, и замените условия в where по этим таблицам на exists например:

and exists (select * 
                  from oc_product_attribute a 
                  where a.product_id=p2a.product_id 
                      and a.attribute_id = 12 
                      and a.text LIKE '%ЛПО%'
               )

таким образом вы избавитесь от distinct
если еще нет индекса на attribute_id, создайте его.
3. Вам не надо использовать подзапрос. Это лишнее
4. Оставьте в select только product_id
Это все, что можно сказать только по запросу. Все остальное будет гаданием на кофейной гуще. Впрочем, это тоже все может быть пальцем в небо :)

Подзапрос, дистинкт, джоины, нечёткий поиск с %%, сортировка по выражению, лимит - да это самая худшая ситуация, которую можно представить для реляционной субд.

Вам точно нужны запросы вида?
p2a0.text LIKE '%ЛПО%'
Возможно достаточно?
p2a0.text LIKE 'ЛПО'

Можно вот так попробовать (+то что советовали выше):

SELECT product_id FROM(
	SELECT DISTINCT p.product_id, pd.name, p.model, p.quantity, p.price, p.sort_order, p.date_added , p.price as realprice 
	  FROM oc_product p 
	LEFT JOIN oc_product_option_value pov ON pov.product_id = p.product_id
	LEFT JOIN oc_product_description pd ON pd.product_id = p.product_id 
	LEFT JOIN oc_product_to_store p2s ON p2s.product_id = p.product_id 
	LEFT JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
	LEFT JOIN oc_category_path cp ON cp.category_id = p2c.category_id
	LEFT JOIN oc_product_attribute p2 ON p2.product_id = p.product_id 
	WHERE 1 AND cp.path_id IN (3558) 
	  AND (
	  	   (p2.attribute_id = 12 AND p2a0.text LIKE '%ЛПО%') OR
	       (p2a1.attribute_id = 20 AND p2a1.text LIKE '%2х36 Вт%') OR
	       (p2a2.attribute_id = 22 AND p2a2.text LIKE '%накладны%') OR
	       (p2a3.attribute_id = 27 AND p2a3.text LIKE '%60 см%') OR
	       (p2a4.attribute_id = 29 AND p2a4.text LIKE '%T8%') OR
	       (p2a5.attribute_id = 30 AND p2a5.text LIKE '%220 В%')
	      )
	  AND pd.language_id = '1' AND p.status = '1' AND p2s.store_id = 0) as innertable 
WHERE 1 ORDER BY sort_order ASC, LCASE(name) ASC LIMIT 0,20

выкинуть left join'ы ибо они не на что не влияют