Database hangs

[plamensl]

I receive queries which lock the database and i have no idea why. No load on the server. Database is small ~ 8 000 items

Here is the query :

 

 

# Time: 120919 14:31:38

# User@Host: premiumpharma[premiumpharma] @ localhost [127.0.0.1]

# Query_time: 1057.872841 Lock_time: 0.004413 Rows_sent: 0 Rows_examined: 8858281

SET timestamp=1348054298;

SELECT DISTINCT p.id_product, pl.name pname, cl.name cname, cl.link_rewrite crewrite, pl.link_rewrite prewrite ,(

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 = 6

AND si.id_product = p.id_product

AND (sw.word LIKE '%')

) position

FROM ps_product p

INNER JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 6)

INNER JOIN `ps_category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 6)

WHERE p.`id_product` IN (27309,27325 ...

 

 

Rows_examined is enormous : 8858281

sw.word LIKE '%' ?

 

 

Ha anyone experienced this ?

[triaffath]

may be server temporary files is full, need to clean up temporary files server

[El Patron]

what version of ps?

[plamensl]

Version is 1.4.9.0

[El Patron]

the call is from classes/Search.php

 

here is a link to forge items that are similar to what you described. To many for me to read for now and hope it helps.

 

http://forge.prestashop.com/secure/IssueNavigator.jspa?

[plamensl]

I found the same report here http://forge.prestashop.com/browse/PSCFI-4556

No solution though.

I think this part of the query is the problem - should wildcard empty searches like 'sw.word LIKE '%' be allowed ?

There should be 3 symbols minimum, according to configuration.

 

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 = 6

AND si.id_product = p.id_product

AND (sw.word LIKE '%')