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
Ecommerce Online Business Conferences to Attend in 2020-202101 Jul 2021A list of the biggest and best ecommerce conferences that will take place in 2020–2021 online that you should consider attending for inspiration.
Basics of Magento Store Testing for Merchants [Website Testing Checklist]25 Jun 2021It is obviously better if testing a website is provided by specialists who know the platform and process perfectly well.
Magento 2 Marketplace Extensions, Themes & Best Practices11 Jun 2021While the ecommerce industry is rapidly increasing, marketplaces and different B2C websites are currently in their golden age.