2

Why does magento2 calls following query in category page?

INSERT INTO `search_tmp_58aad5dedee6e0_33744149` SELECT `main_select`.`entity_id`, MAX(score) AS `relevance` FROM (SELECT `search_index`.`entity_id`, (((0)) * 1) AS score FROM `catalogsearch_fulltext_scope1` AS `search_index`
 LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
 LEFT JOIN `catalog_category_product_index` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id WHERE (category_ids_index.category_id = 4)) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC
 LIMIT 200000

Even there is no need of any search functionality. Have anyone got any clarification of this? I have a category that has lot of products and that category loads in about 15 second for the first time. So I am debugging and found above query is much slow.

Thanks in advance.

  • Try to down your limit from 200000 to some small number and are you using any kind of extension for list page or anything related to that ? And have you tried removing var/generation and cleaning your cache. After that reindex again and try ? – Shahzaib Hayat Khan Feb 20 '17 at 12:20
  • We have modified limit value to 200000, because we have about 1.5 lac product in category. But this is related search result, why this is coming on category list page? – Gulshan Maurya Feb 20 '17 at 12:28
4

Magento 2 treats Category Page as a Search result page. You can see it in https://github.com/magento/magento2/blob/develop/app/code/Magento/CatalogSearch/etc/search_request.xml There are three containers defined:

  • quick_search_container
  • advanced_search_container
  • catalog_view_container

All of these containers describe search queries for Catalog Search Fulltext Index (index="catalogsearch_fulltext").

Just consider Category View page is the same as Quick Search but without Search Phrase. All other filters and Layered Navigation built the same way.

Temporary Table which is created under the hood (in your case - search_tmp_58aad5dedee6e0_33744149) is used for performance optimization as a temporary storage for document ids retrieved from search and passed to build Layered Navigation.

The query you provided has a limit 200000 which is way more than our default limit 10.000 This limitation (10000) was chosen taking into account that category which has huge amount of products will worsen customer experience and will lead to performance degradation of the whole store. Thus providing ability to create such a big category will introduce potential vulnerability for Magento store.

So, I recommend you to decrease category limit to default one - 10000 and rebuild catalogsearch_fulltext index.

  • But this will limit no of products to 10000 in category page. – Gulshan Maurya Feb 20 '17 at 14:17
  • Is there any way to not use search_tmp functionality for product list page? – Gulshan Maurya Feb 21 '17 at 7:01
  • @gulshanmaurya yes, that will introduce a limit to 10 000 per category, but why do you need more than 10k products per category? – Igor Minyaylo Feb 21 '17 at 9:40
  • search_tmp is not an additional functionality, which you could just switch off. Imagine you have a set of results which you got from search request or category view page. And you should build LN for this set of products. Temporary table - is just a suitable place to hold product IDs. If not use temp table you have to use JOINs each time for each LN query, or just handle this filter in PHP, like where product_id in (list_of_up_to_10k_ids). Temporary table in this case is the most efficient way to do. – Igor Minyaylo Feb 21 '17 at 9:46
  • Client's need, as they have about 1.5 lac products and want to make all of them filterable under one category. – Gulshan Maurya Feb 21 '17 at 14:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.