Introduction

One common challenge faced by our clients is the suboptimal performance of Magento search autocomplete, the presence of irrelevant data, and slow response times. Queries can take up to 0.5 seconds, and the results are based on Search Terms. We decided to analyze why the response time is low and if we can adjust search results and provide product names instead of Search Terms.

Performance

To begin, we examined how much code, MySQL and Redis queries are executed during a search query. We found that there are 10 MySQL and 20 Redis queries involved. Here's a breakdown of the SQL queries:


SQL Queries:

 

connect
SET NAMES utf8
SELECT search_query.* FROM search_query WHERE (query_text = 'bre') AND (store_id = '1') LIMIT 1
SELECT flag.* FROM flag WHERE (flag.flag_code='config_hash')
SELECT url_rewrite.* FROM url_rewrite WHERE (request_path IN ('search/ajax/suggest', 'search/ajax/suggest/')) AND (store_id IN ('1'))
SELECT store.* FROM store
SELECT DISTINCT main_table.query_text, main_table.num_results, main_table.popularity FROM search_query AS main_table WHERE (num_results > 0 AND display_in_terms = 1 AND query_text LIKE 'bre%') AND (store_id = 1) ORDER BY popularity DESC
SELECT url_rewrite.*, relation.category_id, relation.product_id FROM url_rewrite LEFT JOIN catalog_url_rewrite_product_category AS relation ON url_rewrite.url_rewrite_id = relation.url_rewrite_id WHERE (url_rewrite.request_path IN ('search/ajax/suggest', 'search/ajax/suggest/')) AND (url_rewrite.store_id IN ('1')) AND (relation.category_id IS NULL)
SELECT store_group.* FROM store_group
SELECT store_website.* FROM store_website

Redis Queries:

"select" "3"
"hget" "zc:k:887_SYSTEM_DEFAULT" "d"
"hget" "zc:k:887_RESOLVED_STORES_D1BEFA03C79CA0B84ECC488DEA96BC68" "d"
"hget" "zc:k:887_GLOBAL__RESOURCESCACHE" "d"
"hget" "zc:k:887_SYSTEM_STORES_DEFAULT" "d"
"hget" "zc:k:887_EAV_ENTITY_TYPES" "d"
"hget" "zc:k:887_EXTENSION_ATTRIBUTES_CONFIG" "d"
"hget" "zc:k:887_GLOBAL__EVENT_CONFIG_CACHE" "d"
"hget" "zc:k:887_FRONTEND__EVENT_CONFIG_CACHE" "d"
"hget" "zc:k:887_DB_IS_UP_TO_DATE" "d"
"hget" "zc:k:887_FRONTEND__RESOURCESCACHE" "d"
"hget" "zc:k:887_APP_ACTION_LIST" "d"
"hget" "zc:k:887_FRONTEND__ROUTESCONFIG" "d"
"hget" "zc:k:887_UI_COMPONENT_CONFIGURATION_DEFINITION_DATA" "d"
"hget" "zc:k:887_THEME_BY_ID_3" "d"
"hget" "zc:k:887_TRANSLATE_EN_US_FRONTEND_DEFAULT_MAGENTO_LUMA_MAGENTO_SEARCH" "d"
"hget" "zc:k:887_APP_4169C5DEEEB8F84FEFBFAC574BE2769B" "d"
"hget" "zc:k:887_DESIGN_CHANGE_954100A7F229D365749D50D87A5685AC" "d"
"hget" "zc:k:887_GLOBAL__CSP_WHITELIST_CONFIG" "d"
"hget" "zc:k:887_FRONTEND__CSP_WHITELIST_CONFIG" "d"

If MySQL and Redis are on separate servers from the Magento backend server, network latency within 1ms adds an additional 30-50ms for connections to Redis and MySQL. Consequently, approximately 25-50ms are lost solely due to data exchange over the network only. Executing MySQL and Redis queries also consumes some time.


Next comes the Magento bootstrap and PHP logic. Here, the losses are dynamic and depend on PHP configuration, web server, MySQL, hardware power, and table size. However, achieving response times below 100ms can be quite challenging.

Even on a clean Magento installation with Sample Data, the results are suboptimal.

Search Quality

Since the search is based on search terms, clients are often dissatisfied with the results. It's an unconventional approach that can lead to the expansion of the search terms table with irrelevant data. Clients typically require real queries to Elasticsearch for real products or categories with additional information.

Often, on e-commerce websites, one can encounter attempts at malicious attacks that lead to the accumulation of irrelevant data in the search terms table, at the very least.

Additionally, a high volume of fake queries impacts the database's size and overall performance.

Objective and Solution

We decided to revamp Magento search autocomplete to achieve the following goals:


  • Provide product names as search results
  • Make it easily customizable for each client, allowing the display of additional fields or entities if needed
  • Ensure that a search query works almost instantly with text input from the client (within 50ms)

To implement these goals, we employed the following techniques:

  • Direct connection to Elasticsearch
  • Full abandonment of MySQL and Redis queries
  • Complete elimination of Magento's bootstrap
  • Creation of a simple PHP endpoint and configuration file for full search control (requiring minor web server config modification)
  • Implementation of a Native Magento mode, which doesn't require web server modifications but is not as fast
  • During development, we discovered that Magento stores records for complex types (e.g., configurable) in Elasticsearch, including the name of the product and all its children. Consequently, we had to add a separate attribute for search to ensure proper functionality.

Technical details will be explained in more details in a separate article

Results

The result is a Magento 2 Fast Search and Autocomplete module that can be configured as outlined on GitHub. Local performance can easily stay within 50ms even without additional PHP tweaks or web server enhancements, even on standard development hardware.

Magento 2 Fast Search and Autocomplete by Freento

Frontend demo can be checked here: https://fast-search-autocomplete-demo.freento.com/