i am using cs-cart ultimate v4.2.4 for my store www.maxbhi.com
till last week we were having products less than 100,000 so we were using searchanise without any problem. but now we have about 200k products so searchanise is not working for store with more then 100k products.
after disabling and uninstalling searchanise, the store conventional search takes too long to show results and also hangs the mysql server a lot. means if 10-20 users try search simultaneously, then the mysql db will lock down and hang all the traffic on the server.
i am using amazon EC2 with self managed settings and all. maybe i am missing to tune up something which i should to make store work correctly.
mysql query sample for search which takes too long to execute:
SELECT SQL_CALC_FOUND_ROWS products.*, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product, IF(shared_prices.product_id IS NOT NULL,MIN(IF(shared_prices.percentage_discount = 0, shared_prices.price, shared_prices.price - (shared_prices.price * shared_prices.percentage_discount)/100)),MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100))) as price, IF(shared_descr.product_id IS NOT NULL, shared_descr.short_description, descr1.short_description) as short_description, IF(shared_descr.product_id IS NOT NULL, IF(shared_descr.short_description = '', shared_descr.full_description, ''), IF(descr1.short_description = '', descr1.full_description, '')) as full_description, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_keywords, descr1.meta_keywords) as meta_keywords, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_description, descr1.meta_description) as meta_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.search_words, descr1.search_words) as search_words, IF(shared_descr.product_id IS NOT NULL, shared_descr.promo_text, descr1.promo_text) as promo_text, cscart_seo_names.name as seo_name, cscart_seo_names.path as seo_path, AVG(cscart_discussion_rating.rating_value) AS average_rating, cscart_discussion.type AS discussion_type, cscart_discussion.thread_id AS discussion_thread_id FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'en' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id AND shared_descr.company_id = 1 AND shared_descr.lang_code = 'en' LEFT JOIN cscart_ult_product_prices as shared_prices ON shared_prices.product_id = products.product_id AND shared_prices.lower_limit = 1 AND shared_prices.usergroup_id IN (0, 0, 1) AND shared_prices.company_id = 1 LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'en' AND ( cscart_seo_names.company_id = 1 OR cscart_seo_names.company_id = 0) LEFT JOIN cscart_discussion ON cscart_discussion.object_id = products.product_id AND cscart_discussion.object_type = 'P' AND cscart_discussion.company_id = 1 LEFT JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id AND cscart_discussion_posts.status = 'A' LEFT JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id AND cscart_discussion_rating.rating_value != 0 WHERE 1 AND (((descr1.search_words LIKE '%lcd for nokia 1200%') OR descr1.product LIKE '%lcd for nokia 1200%' OR descr1.short_description LIKE '%lcd for nokia 1200%' OR descr1.short_description LIKE '%lcd for nokia 1200%' OR descr1.full_description LIKE '%lcd for nokia 1200%' OR descr1.full_description LIKE '%lcd for nokia 1200%' OR (descr1.meta_keywords LIKE '%lcd for nokia 1200%' OR descr1.meta_description LIKE '%lcd for nokia 1200%') OR products.product_code LIKE '%lcd for nokia 1200%' OR products.product_code LIKE '%lcd for nokia 1200%')) AND cscart_categories.company_id = 1 AND products.status IN ('A') AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id ORDER BY products.timestamp desc LIMIT 0, 36
| 7282 | maxbhi_et | localhost | maxbhi_mbdb01 | Query | 33 | Copying to tmp table |
SELECT SQL_CALC_FOUND_ROWS products.*, IF(shared_descr.product_id IS NOT NULL, shared_descr.product, descr1.product) as product, IF(shared_prices.product_id IS NOT NULL,MIN(IF(shared_prices.percentage_discount = 0, shared_prices.price, shared_prices.price - (shared_prices.price * shared_prices.percentage_discount)/100)),MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100))) as price, IF(shared_descr.product_id IS NOT NULL, shared_descr.short_description, descr1.short_description) as short_description, IF(shared_descr.product_id IS NOT NULL, IF(shared_descr.short_description = '', shared_descr.full_description, ''), IF(descr1.short_description = '', descr1.full_description, '')) as full_description, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_keywords, descr1.meta_keywords) as meta_keywords, IF(shared_descr.product_id IS NOT NULL, shared_descr.meta_description, descr1.meta_description) as meta_description, IF(shared_descr.product_id IS NOT NULL, shared_descr.search_words, descr1.search_words) as search_words, IF(shared_descr.product_id IS NOT NULL, shared_descr.promo_text, descr1.promo_text) as promo_text, cscart_seo_names.name as seo_name, cscart_seo_names.path as seo_path, AVG(cscart_discussion_rating.rating_value) AS average_rating, cscart_discussion.type AS discussion_type, cscart_discussion.thread_id AS discussion_thread_id FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'en' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_ult_product_descriptions shared_descr ON shared_descr.product_id = products.product_id AND shared_descr.company_id = 1 AND shared_descr.lang_code = 'en' LEFT JOIN cscart_ult_product_prices as shared_prices ON shared_prices.product_id = products.product_id AND shared_prices.lower_limit = 1 AND shared_prices.usergroup_id IN (0, 0, 1) AND shared_prices.company_id = 1 LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'en' AND ( cscart_seo_names.company_id = 1 OR cscart_seo_names.company_id = 0) LEFT JOIN cscart_discussion ON cscart_discussion.object_id = products.product_id AND cscart_discussion.object_type = 'P' AND cscart_discussion.company_id = 1 LEFT JOIN cscart_discussion_posts ON cscart_discussion_posts.thread_id = cscart_discussion.thread_id AND cscart_discussion_posts.status = 'A' LEFT JOIN cscart_discussion_rating ON cscart_discussion.thread_id = cscart_discussion_rating.thread_id AND cscart_discussion_rating.post_id = cscart_discussion_posts.post_id AND cscart_discussion_rating.rating_value != 0 WHERE 1 AND (((descr1.search_words LIKE '%lcd for nokia 1200%') OR descr1.product LIKE '%lcd for nokia 1200%' OR descr1.short_description LIKE '%lcd for nokia 1200%' OR descr1.short_description LIKE '%lcd for nokia 1200%' OR descr1.full_description LIKE '%lcd for nokia 1200%' OR descr1.full_description LIKE '%lcd for nokia 1200%' OR (descr1.meta_keywords LIKE '%lcd for nokia 1200%' OR descr1.meta_description LIKE '%lcd for nokia 1200%') OR products.product_code LIKE '%lcd for nokia 1200%' OR products.product_code LIKE '%lcd for nokia 1200%')) AND cscart_categories.company_id = 1 AND products.status IN ('A') AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id ORDER BY products.timestamp desc LIMIT 0, 36 |
for now i have to temporarily remove the search dailog. if you want to test then you can use advance search and see how slow the search is working. https://www.maxbhi.c…products.search
please help me solve the problem, without search is really bad for customer experience.
i really think i am mistaking any php or mysql configuration. or i really cannot use search with 200k products in cs-cart??
Naman Agarwal
skype: elcotek
mail: info@elcotek.net