Search Crash our dedicated server PS 1.4.2.5

[Gipielle]

We have a big problem, if we search a term with like 2000 results, cpu goes in overload 600% and server crashes, for repair this problem we have set search for max 20 items but it's not a good solution.

What can we do ?

Thanks

[Gipielle]

This is the query that in search crash our server.

 SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`,
  tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name ,(
   SELECT SUM(weight)
   FROM ps_search_word sw
   LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
   WHERE sw.id_lang = 5
   AND si.id_product = p.id_product
   AND (sw.word LIKE 'chiavi%')
  ) position, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 2000 DAY)) > 0 new
 FROM ps_product p
 INNER JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 5)
 LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                            AND tr.`id_country` = 10
                                               AND tr.`id_state` = 0)
    LEFT JOIN `ps_tax` tax ON (tax.`id_tax` = tr.`id_tax`)
 LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
 LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
 LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 5)
 WHERE p.`id_product`  IN (..... [b]there are 2600 ID[/b]
ORDER BY  position desc
 LIMIT 0,10