Extremely Slow SQL query: MySQL ps_category_product select

[peterjsnow]

Using New Relic, I've been able to identify an SQL query which is taking a large amount of time to process on our site with 3000+ products. It is taking upwards of 30 seconds in some instances.

 

It is occurring in the query 'MySQL ps_category_product select':

 

SELECT DISTINCT cp.id_product, pl.name, pl.description_short, i.id_image, pl.link_rewrite, ps.show_price, p.ean?, cl.link_rewrite category FROM ps_category_product cp LEFT JOIN ps_product_lang pl ON (cp.id_product = pl.id_product)        LEFT JOIN ps_product p ON (pl.id_product = p.id_product)        LEFT JOIN ps_image i ON (i.id_product = cp.id_product)        LEFT JOIN ps_product_shop ps ON (pl.id_product = ps.id_product)        LEFT JOIN ps_category_lang cl ON (cl.id_category = ps.id_category_default)        WHERE cp.id_category = ? AND i.cover = ? AND ps.active = ? ORDER BY RAND() LIMIT ?

I'm not too familiar with SQL, so I'm wondering what could be the possible cause of this? Should I get developers to look into optimising the query, or is it more likely that something is not working correctly (eg a module incompatibility) etc.

 

Thanks for your help.