Background of the Issue
While searching using MySQL, when you go to the search results page in Magento, an adapter creates a temporary table that records all the results. The table is created with the “memory” type, but when it gets a large size, it’s recorded to the disk.
The search text and the number of results are stored in the search_query table, as well as the number of times users enter it and the technical information.
By default, when connecting to ElasticSearch, turning on auto-suggest, and going to the results page, a temporary table is not created, while popular search queries are listed in the search_query.
Also, when entering text in the search field, an AJAX request is sent. A match by LIKE is selected at the beginning of the entry from the search_query table. If there are matches in the search text, they are displayed in the admin panel and the number of results for each search text.
The third-party search autocomplete modules, such as Amasty and Mirasvit, send AJAX requests when entering text into the search field. In fact, they call the module method Magento — catalog_search, which forms a collection for the search result page and creates a temporary table accordingly. The requests are sent as many times as there were keystrokes after entering three characters.
In Simple Terms
There is one thing in the Magento that everyone encounters inherently. We have a regularly updated table, in which all the search queries are added with all the results. As the website is being used, the table content is growing, and in the end, it reaches such volumes that the website can’t cope with it and falls.
To avoid the website falling, the table is often cleaned, and a part of the search queries is deleted accordingly.
Ways of Solution
Idea #1. In default Magento, with ElasticSearch connected, you can create a module that changes the behavior of an adapter when using auto-suggest. Use ElasticSearch instead of the search_query table.
Idea #2. Rewrite a part of code responsible for auto-suggest and use ElasticSearch instead of MySQL. Instead of the Magento adapter, create and use an adapter that allows selecting a final number of results for the test release, as well as a total count, without selecting all the records.
As a result, we’ll keep on working on resolving the issue with Magento search and temporary tables. Also, we’ll share our further progress in the news and blog articles.
If you have any questions, clarifications, or suggestions on the content of our post, write to us. We’re looking forward to talking to you!
Subscribeto our newsletter
3 Steps to Writing SEO-Friendly Ecommerce Product Description10 Dec 2020In the wake of the pandemic, we saw many industries suffer from the lockdown. A lot of businesses had to close, many lost a lot of money and had to let their staff go.
Buy Now Pay Later Functionality for Ecommerce Business03 Nov 2020We’ve recently discovered the CNN Business report, which reveals that big brands and retailers use the so-called ‘buy now, pay later’ feature, which has become a booming trend in the whole industry.
In-Store Pickup Functionality for Magento 2 Stores: Best Practices & How to Implement27 Oct 2020As stated in Retail TouchPoints, more than 90% of brands are expected to offer their customers an in-store pickup by 2021.