Problem with search

[WaoX]

Hello,

 

I have a big problem with search.

 

For some word it takes few minutes to finish searching.

 

Mysql slow query log shows following info:

# Time: 121204 16:06:25
# User@Host: root[root] @ localhost []
# Query_time: 62.693404  Lock_time: 0.000120 Rows_sent: 277  Rows_examined: 12304213
SET timestamp=1354629985;
SELECT DISTINCT cp.`id_product`
 FROM `ps_category_group` cg
 INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
 INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
 INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
 WHERE c.`active` = 1 AND p.`active` = 1
 AND cg.`id_group` = 1
 AND  p.id_product  IN (
 SELECT id_product
 FROM ps_search_word sw
 LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
 WHERE sw.id_lang = 6
 AND sw.word LIKE 'pirn%'
);
# Time: 121204 16:07:29
# User@Host: root[root] @ localhost []
# Query_time: 63.711820  Lock_time: 0.000071 Rows_sent: 277  Rows_examined: 12304213
SET timestamp=1354630049;
SELECT DISTINCT cp.`id_product`
 FROM `ps_category_group` cg
 INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
 INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
 INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
 WHERE c.`active` = 1 AND p.`active` = 1
 AND cg.`id_group` = 1
 AND  p.id_product  IN (
	SELECT id_product
	FROM ps_search_word sw
	LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
	WHERE sw.id_lang = 6
	AND sw.word LIKE 'pirn%'
   );

 

As you can see it runs this query 2 times and it takes one time around 60+ seconds.

 

This is very bad and most of customers dont wate this long.

I was on Joomla + Virtumart before I started using Prestashop and I didn't have this kind of problem not once. Maximum wait time was 10 seconds.

 

 

Hope someone can help me.

 

PS! I searched "pirn" and it return with 200 results. Website www.cn-parts.ee

[Alex Simonchik BelVG]

Hi,

 

try to disable "ajax search" and increase "Minimum word length" param. (Backend->Preferences->Search)

 

Regards

[WaoX]

Hi,

 

try to disable "ajax search" and increase "Minimum word length" param. (Backend->Preferences->Search)

 

Regards

 

Thanyou that helped, but it is still 20 or so seconds, what is not good.

 

I changed word length to 5 and disabled ajax & instant search.

 

Maybe it is possible to remove some info from search index.

For example these are not important: Tags weight,Attributes weight,Features weight, Short description weight.

 

 

# Time: 121205 10:04:56
# User@Host: root[root] @ localhost []
# Query_time: 23.894448  Lock_time: 0.000147 Rows_sent: 147  Rows_examined: 3875819
SET timestamp=1354694696;
SELECT DISTINCT cp.`id_product`
 FROM `ps_category_group` cg
 INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
 INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
 INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
 WHERE c.`active` = 1 AND p.`active` = 1
 AND cg.`id_group` = 1
 AND  p.id_product  IN (
 SELECT id_product
 FROM ps_search_word sw
 LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
 WHERE sw.id_lang = 6
 AND sw.word LIKE 'pirnid%'
   );
# Time: 121205 10:05:19
# User@Host: root[root] @ localhost []
# Query_time: 23.307913  Lock_time: 0.000070 Rows_sent: 147  Rows_examined: 3875819
SET timestamp=1354694719;
SELECT DISTINCT cp.`id_product`
 FROM `ps_category_group` cg
 INNER JOIN `ps_category_product` cp ON cp.`id_category` = cg.`id_category`
 INNER JOIN `ps_category` c ON cp.`id_category` = c.`id_category`
 INNER JOIN `ps_product` p ON cp.`id_product` = p.`id_product`
 WHERE c.`active` = 1 AND p.`active` = 1
 AND cg.`id_group` = 1
 AND  p.id_product  IN (
    SELECT id_product
    FROM ps_search_word sw
    LEFT JOIN ps_search_index si ON sw.id_word = si.id_word
    WHERE sw.id_lang = 6
    AND sw.word LIKE 'pirnid%'
   );

Edited December 5, 2012 by WaoX (see edit history)

[Alex Simonchik BelVG]

 

Maybe it is possible to remove some info from search index.

For example these are not important: Tags weight,Attributes weight,Features weight, Short description weight.

 

 

Yes, it makes sense. Also you can try to increase perfomance of your server.

[WaoX]

Yes, it makes sense. Also you can try to increase perfomance of your server.

 

Maybe the problem is in server. I have 70 000 products and 8000 categories. Database is about 1gb. Search index table is 550mb. Right now I have VPS with 2 cores and 2gb of ram.

 

I hoped i can first sort this out before i have to start paying 120€/month more only because of search is slow. Website itself is blazing fast.

[WaoX]

I found another problem with prestashop. When I change minimum word length to 5 then when person searches 4 letter word it find no results. Prestashop should warn people that minimum search word length is 5 or what ever set in backend. Because you can lose many customers that way.

 

Also it should be on system requirements that prestashop requires private server if you have more that *** categories and products.

 

We have spend more that 600€ on prestashop and it is slower than our last platform. If I had know that search dosent work then I would have not spend months on going from virtuemart to prestashop and spending that much money on it.

 

Any way I don't recommend prestashop do large online shops because it is unusable!

 

Virtuemart search with 40 000 products took only 4-5 seconds.