extremly slow sql query

[GerryCze]

Hi, we have this query in our Prestashop in slow_log. It control table with 1M rows, but it examined more then 5M rows. Do somebody now if is this query important or if is it possible  to turn it off somehow or make some optimalization to be more faster?

 

# Time: 160530 21:15:44

# User@Host: xxx @ localhost []

# Query_time: 7.554834  Lock_time: 0.000225 Rows_sent: 2  Rows_examined: 5688736

SET timestamp=1464635744;

SELECT c.id_category, c.id_parent, cl.name, (SELECT count(DISTINCT p.id_product) #

 

                                        FROM ps_category_product cp

                                        LEFT JOIN ps_product p ON (p.id_product = cp.id_product)  INNER JOIN ps_product_shop product_shop

                ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)

LEFT JOIN `ps_manufacturer` m ON (m.id_manufacturer = p.id_manufacturer)

                        INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 1

                        AND psi.price_min <= 180 AND psi.price_max >= 0 AND psi.id_shop=1)

 

                                        WHERE cp.id_category = c.id_category

                                        AND product_shop.active = 1 AND product_shop.`visibility` IN ("both", "catalog") AND EXISTS (SELECT * FROM ps_feature_product fp WHERE fp.id_product = p.id_product AND fp.`id_feature_value` = 30 OR fp.`id_feature_value` = 31)

) count_products

                                        FROM ps_category c

                                        LEFT JOIN ps_category_lang cl ON (cl.id_category = c.id_category AND cl.`id_shop` = 1 and cl.id_lang = 1) RIGHT JOIN ps_category_group cg ON (cg.id_category = c.id_category AND cg.`id_group` IN (1)) WHERE c.nleft > 203

                                        AND c.nright < 208

 

                                        AND c.active = 1

[musicmaster]

It looks like this one comes from Blocklayered.php from the module with the same name. Probably it provides the numbers for the options. 

 

You could choose to do without numbers.