Magento2: Magento 2.1.2 search extremely slow

[miro91]

Hello everyone,
I am having Magento 2.1.2 installed on 2 identical servers (dev and production) and we are seeing some strange behaviour with the search of the site on the production server only.
The search is taking 16-17 seconds to load the results page.
Some more background: we were using this extension https://mirasvit.com/magento-2-extensions/sphinx-search-ultimate.html but the issue is still there even without this module so it's not from the additional extension or something else.

Preconditions

1. Magento 2.1.2
2. Varnish enabled
3. Memcached enabled
4. Setup Blackfire to diagnose the website (see screenshots)
5. Multiwebsites created

Steps to reproduce

1. Search for anything from header search form
2. Hit enter and wait to open search results page

Expected result

1. Show the page after 1-2 seconds like the other page requests

Actual result

1. Wait 16 seconds to load the page
2. Executes 800+ queries like this:
   select count(distinct ?) from catalog_category_product as main_table join catalog_category_entity as e on main_table.category_id = e.entity_id join catalog_product_website as w on main_table.product_id = w.product_id where (e.entity_id = :entity_id) or (e.path like :c_path) and (w.website_id = ?)

The call seems to start here:
Magento\Catalog\Model\ResourceModel\Category\Collection::loadProductCount

where 863 calls are fone for the child function:

https://s23.postimg.org/g3bl99kor/Screen_Shot_2016_12_16_at_15_25_16.png

https://s23.postimg.org/6wtalzfgb/Screen_Shot_2016_12_16_at_15_25_56.png

Any help will be appreciated. Thanks in advance!

[caesarcxiv]

We experience the same issue. With that query however we see it on our catalog pages as well. We also have the Mirasvit extension. (tested both on and off)

Our catalog page load time is 7-10 seconds.

[farisshindsite]

I have the same problem the search is extremely slow when I add the second website with products.
The load takes more than 30s

[adinca]

I have the same situation. I'm using Mirasvit Elasticsearch module (which I assume it's almost similar to Sphinx), but the problem is definately somewhere in Magento. I did some digging and found the following:

1. Elasticsearch query responds in approx. 60ms for a 7000 products result out of a 60K catalog

2. Query build and execution takes less than 100ms

3. After the elasticsearch collection is returned it's all Magento (therefor I'm inclined to exclude Mirasvit or Elasticsearch being the culprits here)

4. The elasticsearch query is executed 45 times, no matter how many results I have (I don't have an explanation yet on this).
   In my case it's this class and method which is being called 45 times:
   vendor/mirasvit/module-search-elastic/src/SearchElastic/Adapter/ElasticAdapter.php::query()
   If I store the result as a class property and only load it once, the search load time drops to a half.

5. When ES is returning the collection of product IDs that match the search query, Magento loads the entire product collection and filters it by that ID list, without adding any limit to it. Even though in my case the number of products per page is 32. Any idea why?

Despite all this, I do have a related question. Why is the search result page not cached? Is it because Magento wouldn't know which query result to invalidate when updating the product information? Is there any other reason?

Thanks for the help

[franta79]

I have the same problem. Our site is very fast, but search is taking 15s. No long queries. Is there a solution to this? It's driving me crazy..

[magento-engcom-team]

Hi @miro91
You use the 3rd party code.
Please refer to the Community Forums or the Magento Stack Exchange site for advice or general discussion about this issue. The GitHub issue tracker is intended for Magento Core technical issues only.