MySQL. Медленный запрос Brainy Filters (Opencart)

Доброго времени суток!
Появился 1 медленный запрос после включения опции атрибуты в фильтре Brainy Filters на базе Opencart 2.1.0.2. Сделал SQL запрос EXPLAIN оставил в прикрепленных. Подскажите какой нужно прописать индекс на примере ALTER TABLE какя здесь таблица ADD INDEX и куда его прописывать , на примере этого кода?

Медленный запрос:
<div class='forum-code'><div class="sql" style="font-family:monospace;"><span style="color: #993333; font-weight: bold;">SELECT</span> a<span style="color: #66cc66;">.</span>attribute_group_id <span style="color: #993333; font-weight: bold;">AS</span> group_id<span style="color: #66cc66;">,</span> av<span style="color: #66cc66;">.</span>attribute_id <span style="color: #993333; font-weight: bold;">AS</span> attr_id<span style="color: #66cc66;">,</span> av<span style="color: #66cc66;">.</span>attribute_value_id <span style="color: #993333; font-weight: bold;">AS</span> val_id<span style="color: #66cc66;">,</span> agd<span style="color: #66cc66;">.</span>name <span style="color: #993333; font-weight: bold;">AS</span> group_name<span style="color: #66cc66;">,</span> ad<span style="color: #66cc66;">.</span>name <span style="color: #993333; font-weight: bold;">AS</span> attr_name<span style="color: #66cc66;">,</span> av<span style="color: #66cc66;">.</span>sort_order <span style="color: #993333; font-weight: bold;">AS</span> val_sort<span style="color: #66cc66;">,</span> av<span style="color: #66cc66;">.</span>value <span style="color: #993333; font-weight: bold;">FROM</span> <span style="color: #ff0000;">`oc_bf_product_attribute_value`</span> <span style="color: #993333; font-weight: bold;">AS</span> af <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #66cc66;">(</span> <span style="color: #993333; font-weight: bold;">SELECT</span> p<span style="color: #66cc66;">.*</span> <span style="color: #993333; font-weight: bold;">FROM</span> <span style="color: #ff0000;">`oc_product`</span> <span style="color: #993333; font-weight: bold;">AS</span> p <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_product_to_category`</span> <span style="color: #993333; font-weight: bold;">AS</span> p2c <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>p<span style="color: #66cc66;">.</span>product_id <span style="color: #66cc66;">=</span> p2c<span style="color: #66cc66;">.</span>product_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_category_path`</span> <span style="color: #993333; font-weight: bold;">AS</span> cp <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>cp<span style="color: #66cc66;">.</span>category_id <span style="color: #66cc66;">=</span> p2c<span style="color: #66cc66;">.</span>category_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">WHERE</span> cp<span style="color: #66cc66;">.</span>path_id <span style="color: #66cc66;">=</span> <span style="color: #ff0000;">'59'</span><span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">AS</span> p <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>af<span style="color: #66cc66;">.</span>product_id <span style="color: #66cc66;">=</span> p<span style="color: #66cc66;">.</span>product_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_bf_attribute_value`</span> <span style="color: #993333; font-weight: bold;">AS</span> av <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>af<span style="color: #66cc66;">.</span>attribute_value_id <span style="color: #66cc66;">=</span> av<span style="color: #66cc66;">.</span>attribute_value_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_attribute`</span> <span style="color: #993333; font-weight: bold;">AS</span> a <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>a<span style="color: #66cc66;">.</span>attribute_id <span style="color: #66cc66;">=</span> av<span style="color: #66cc66;">.</span>attribute_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_attribute_description`</span> <span style="color: #993333; font-weight: bold;">AS</span> ad <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>ad<span style="color: #66cc66;">.</span>attribute_id <span style="color: #66cc66;">=</span> a<span style="color: #66cc66;">.</span>attribute_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_attribute_group`</span> <span style="color: #993333; font-weight: bold;">AS</span> ag <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>ag<span style="color: #66cc66;">.</span>attribute_group_id <span style="color: #66cc66;">=</span> a<span style="color: #66cc66;">.</span>attribute_group_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_attribute_group_description`</span> <span style="color: #993333; font-weight: bold;">AS</span> agd <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>agd<span style="color: #66cc66;">.</span>attribute_group_id <span style="color: #66cc66;">=</span> a<span style="color: #66cc66;">.</span>attribute_group_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">INNER</span> <span style="color: #993333; font-weight: bold;">JOIN</span> <span style="color: #ff0000;">`oc_product_to_store`</span> <span style="color: #993333; font-weight: bold;">AS</span> ps <span style="color: #993333; font-weight: bold;">ON</span> <span style="color: #66cc66;">(</span>p<span style="color: #66cc66;">.</span>product_id <span style="color: #66cc66;">=</span> ps<span style="color: #66cc66;">.</span>product_id<span style="color: #66cc66;">)</span> <span style="color: #993333; font-weight: bold;">WHERE</span> agd<span style="color: #66cc66;">.</span>language_id <span style="color: #66cc66;">=</span> <span style="color: #ff0000;">'1'</span> <span style="color: #993333; font-weight: bold;">AND</span> ad<span style="color: #66cc66;">.</span>language_id <span style="color: #66cc66;">=</span> <span style="color: #ff0000;">'1'</span> <span style="color: #993333; font-weight: bold;">AND</span> av<span style="color: #66cc66;">.</span>language_id <span style="color: #66cc66;">=</span> <span style="color: #ff0000;">'1'</span> <span style="color: #993333; font-weight: bold;">AND</span> ps<span style="color: #66cc66;">.</span>store_id <span style="color: #66cc66;">=</span> <span style="color: #ff0000;">'0'</span> <span style="color: #993333; font-weight: bold;">AND</span> p<span style="color: #66cc66;">.</span>STATUS <span style="color: #66cc66;">=</span> <span style="color: #cc66cc;">1</span> <span style="color: #993333; font-weight: bold;">GROUP</span> <span style="color: #993333; font-weight: bold;">BY</span> av<span style="color: #66cc66;">.</span>attribute_value_id <span style="color: #993333; font-weight: bold;">ORDER</span> <span style="color: #993333; font-weight: bold;">BY</span> ag<span style="color: #66cc66;">.</span>sort_order<span style="color: #66cc66;">,</span> ag<span style="color: #66cc66;">.</span>attribute_group_id<span style="color: #66cc66;">,</span> a<span style="color: #66cc66;">.</span>sort_order<span style="color: #66cc66;">,</span> ad<span style="color: #66cc66;">.</span>name<span style="color: #66cc66;">,</span> av<span style="color: #66cc66;">.</span>sort_order<span style="color: #66cc66;">,</span> av<span style="color: #66cc66;">.</span>value</div></div>
cdn.adm.tools/forum_message/0388/55/screen_explain.jpg

Судя по запросу можно сделать вывод, что модуль нужно удалить. Я многое видел и много SQL запросов критиковал, но для того, что б в INNER JOIN втулить вложенный SELECT нужно быть говнокодером с большой буквы. IMHO тут индексы не помогут, нужно переписывать запрос.

Скажите, как зарубежному разработчику правильно указать(что написать) на причину?

Could you please optimize this query: [текст SQL запроса]. It would be perfect if you will not use subqueries in INNER JOIN construction.

Спасибо!