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.
Final Thoughts
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
Recent News
7 Best B2B Ecommerce Platforms for Businesses at a Bigger Scale
06 Jun 2023A B2B store requires features & functionality to meet the unique business needs. So let’s compare 7 B2B commerce platforms.Personalized Product Recommendations: Know Your Customers
26 May 2023In the eCommerce industry, strong customer relationships are crucial. Magento product recommendations help you achieve this goal.Top 5 Magento Stores Examples & Magento 2 Demo Store [Bonus]
04 May 2023If you wonder who uses Magento websites, keep reading our selection of five Magento online stores with an overview of features their customers are fond of.
Magento Team
Combining the expertise of digital marketers and tech professionals, we share our insights about the realms of eCommerce development, digital marketing trends, and the latest tech breakthroughs.