Magento Search & Usage Of Temporary Tables

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.