0

my sites category page slows down because firing query for search_tmp_* table take more than 1 second and it runs saveral time on one page load here is the query :

7624 ## QUERY

SQL: INSERT INTO search_tmp_5ce2b13088d284_52718722 SELECT main_select.entity_id, SUM(score) AS relevance FROM (SELECT DISTINCT eav_index.entity_id, (((0) + (0)) * 1 + SUM(CASE WHEN cea.search_weight > 1 THEN((CASE WHEN data_index LIKE ' bazookagoal ' THEN 5 ELSE 0 END+CASE WHEN data_index LIKE '% bazookagoal %' THEN 3 ELSE 0 END+CASE WHEN data_index LIKE '%bazookagoal%' THEN 2 ELSE 0 END+CASE WHEN 11 * ((11 / LENGTH(data_index)) + (1/(LENGTH(data_index) - ( LENGTH(data_index) - LOCATE("bazookagoal",data_index))))) THEN 11 * ((11 / LENGTH(data_index)) + (1/(LENGTH(data_index) - ( LENGTH(data_index) - LOCATE("bazookagoal",data_index))))) ELSE 0 END)) ELSE 0 END * POW(2, cea.search_weight))) AS score FROM catalog_product_index_eav AS eav_index INNER JOIN catalogsearch_fulltext_scope1 AS search_index ON eav_index.entity_id = search_index.entity_id LEFT JOIN catalog_eav_attribute AS cea ON search_index.attribute_id = cea.attribute_id INNER JOIN cataloginventory_stock_status AS stock_index ON stock_index.product_id = eav_index.entity_id AND stock_index.website_id = 0 AND stock_index.stock_status = 1 AND stock_index.stock_id = 1 INNER JOIN catalog_category_product_index_store1 AS category_ids_index ON eav_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (eav_index.store_id = '1') AND (eav_index.attribute_id = 99 AND eav_index.value in ('3', '4') AND eav_index.store_id = '1') AND (category_ids_index.category_id = 2) GROUP BY entity_id HAVING ((((GROUP_CONCAT(data_index) LIKE '%bazookagoal%'))))) AS main_select GROUP BY entity_id ORDER BY relevance DESC, entity_id DESC LIMIT 100000 AFF: 1 TIME: 1.5136

Please Help

  • This is Bug of Magento 2.Tmp table create the issue. Suggest to upgrade the Magento system 2.3 and use Elastic search – Amit Bera May 20 at 15:10
  • @AmitBera Thank you for your response. you are right but is there any way to resolve this issue in current Magento 2 version – nikunj May 21 at 3:45
  • current Magento 2 version is 2.2.6 – nikunj May 21 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.