Slow SQL query

[modlin]

Hello all,

I have a shared hosting Prestashop site. My hosting provider complains that SQL query takes too long to execute - 4 sec, and examines over 360 000 rows. Here is the query:

 

Time: 120522 13:28:19
# User@Host: **** @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 10 Rows_examined: 364881
use ****;
SELECT SQL_CALC_FOUND_ROWS p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
t.`rate`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name
,(
SELECT SUM(weight)
FROM search_word sw
LEFT JOIN search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 3
AND si.id_product = p.id_product
AND (sw.word LIKE 'жени%')
) as position
FROM product p
LEFT JOIN `product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 3)
LEFT OUTER JOIN `image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
LEFT JOIN `image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 3)
LEFT JOIN `tax` t ON (p.`id_tax` = t.`id_tax`)
LEFT JOIN `manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
WHERE p.id_product IN (
SELECT id_product
FROM search_word sw
LEFT JOIN search_index si ON sw.id_word = si.id_word
WHERE sw.id_lang = 3
AND sw.word LIKE 'жени%'
)
AND p.active = 1
AND p.`id_product` IN (
SELECT cp.`id_product`
FROM `category_group` cg
LEFT JOIN `category_product` cp ON (cp.`id_category` = cg.`id_category`)
WHERE cg.`id_group` = 1
)
ORDER BY price ASC
LIMIT 910,10;

What can I do to improve performance of that query? Also how do I find which php file executes this query?

 

EDIT: On second look this must be the code which is executed and selects 10 random products when a user clicks on a tag in the tag-cloud.

Edited June 5, 2012 by modlin (see edit history)