DocumentationCommerceTools

ACSD-62577: Storefront search performance optimization

Last update: Thu Dec 19 2024 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Experienced
  • Admin
  • Developer

The ACSD-62577 patch fixes the issue with slow performance of storefront search queries by optimizing both query and table indexes. This patch is available with the Quality Patches Tool (QPT) 1.1.56. The patch ID is ACSD-62577. Please note that the issue was is scheduled to be fixed in Adobe Commerce 2.4.8.

Affected products and versions

The patch is created for Adobe Commerce version:

Adobe Commerce (all deployment methods) 2.4.6, 2.4.7-p2

Compatible with Adobe Commerce versions:

Adobe Commerce (all deployment methods) 2.4.4 - 2.4.7-p3

NOTE
The patch might become applicable to other versions with new Quality Patches Tool releases. To check if the patch is compatible with your Adobe Commerce version, update the magento/quality-patches package to the latest version and check the compatibility on the Quality Patches Tool: Search for patches page. Use the patch ID as a search keyword to locate the patch.

Issue

Large search_query tables significantly slow down storefront searches, increasing frontend response times due to inefficient queries and lack of optimized table indexes.

Steps to reproduce:

  1. Set up Adobe Commerce Develop using the performance toolkit small.xml.

  2. Access the SQL command line and delete the search_query table using the following commands:

    code language-none 
    SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE search_query;
SET FOREIGN_KEY_CHECKS = 1;

  3. Populate the search_query table with a large number of records, ex: 4 million records.

  4. Trigger reindexing and flush caches.

    code language-none 
    bin/magento indexer:reindex
bin/magento c:c
bin/magento c:f

  5. Enable database debug logs:

    code language-none 
    bin/magento dev:query-log:enable

  6. Search a term in the storefront search bar, e.g.,
    http://your_magento_instance/default/catalogsearch/result/?q=test.

  7. Check the db.log for the query execution time for the following SQL:

    code language-none 
    SELECT COUNT(*) FROM (
SELECT DISTINCT `main_table`.`query_text`
FROM `search_query` AS `main_table`
WHERE (main_table.store_id IN (1))
AND (main_table.num_results > 0)
ORDER BY `main_table`.`popularity` DESC
LIMIT 100  ) AS `result` WHERE (result.query_text = 'test')

Expected results:

The query execution time is optimized, resulting in a less significant increase in response time when processing large search_query tables.

Actual results:

The query execution time increases significantly due to inefficient handling of the large search_query table:

TIME: 10.8520 seconds

Apply the patch

To apply individual patches, use the following links depending on your deployment method:

To learn more about Quality Patches Tool, refer to:

recommendation-more-help
c2d96e17-5179-455c-ad3a-e1697bb4e8c3