QUALITY PATCH

ACSD-62577: Large search_query tables significantly slow down storefront searches…

Large search_query tables significantly slow down storefront searches, increasing frontend response times due to inefficient queries and lack of optimized table indexes. Quality patch ACSD-62577 for magento/module-search applies to Magento Open Source and Adobe Commerce 2.4.4 to 2.4.7-p10.

Categories: Performance, Catalog Search
Components: magento/module-search
Origin: adobe-commerce-support
Since QPT: 1.1.56

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. 1. Access the SQL command line and delete the search_query table using the following commands: SET FOREIGN_KEY_CHECKS = 0; DROP TABLE search_query; SET FOREIGN_KEY_CHECKS = 1; 1. Populate the search_query table with a large number of records, ex: 4 million records. 1. Trigger reindexing and flush caches. bin/magento indexer:reindex bin/magento c:c bin/magento c:f 1. Enable database debug logs: bin/magento dev:query-log:enable 1. Search a term in the storefront search bar, e.g., http://your_magento_instance/default/catalogsearch/result/?q=test. 1. Check the db.log for the query execution time for the following SQL: 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')

Adobe's page lists 2.4.4 - 2.4.7-p3 as compatible; the versions below are resolved from the current QPT constraints and are the ones the tool will offer.

Adobe scheduled the permanent fix for 2.4.8.

Install

Install the Quality Patches Tool with composer require magento/quality-patches, apply the prerequisites listed for all files applicable to your distribution and version first, then run vendor/bin/magento-patches apply ACSD-62577. On Cloud, add ACSD-62577 under stage.build.QUALITY_PATCHES in .magento.env.yaml.

For cweagans/composer-patches, choose a compatible version below and download the bundle. Copy its ACSD-62577/ folder into patches/composer/, merge composer.patches.json into composer.json, then run composer install. Bundle prerequisites the same way and list them first. Paths are relative to each package root, using the default -p1 level. Prefer local files when configuring composer-patches; a remote URL can change.