{"data":{"id":"ACSD-62577","source":"qpt","sourceRef":"magento/quality-patches@1.1.84","title":"Fixes the issue with slow performance of storefront search queries by optimizing both query and table indexes.","categories":["Performance","Catalog Search"],"components":["magento/module-search"],"origin":"adobe-commerce-support","documentation":{"url":"https://experienceleague.adobe.com/en/docs/commerce-operations/tools/quality-patches-tool/patches-available-in-qpt/v1-1-56/acsd-62577-storefront-search-performance-optimization","path":"help/tools/quality-patches-tool/patches-available-in-qpt/v1-1-56/acsd-62577-storefront-search-performance-optimization.md","blobSha":"c7033d273dd8619f6fef4b6c12b45a7518cb915a","description":"Large search_query tables significantly slow down storefront searches, increasing frontend response times due to inefficient queries and lack of optimized table indexes.","stepsToReproduce":"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')","introducedInQpt":"1.1.56","createdFor":["2.4.6","2.4.7-p2"],"documentedRange":"2.4.4 - 2.4.7-p3","scheduledFixRelease":"2.4.8"},"files":[{"package":"magento/magento2-base","constraint":">=2.4.4 <2.4.7","path":"patches/os/ACSD-62577_2.4.6.patch","blobSha":"fcb7c713a2b6d261d0d0c662265b6a028414c7f0","url":"https://raw.githubusercontent.com/magento/quality-patches/11d565bdde197b9c7bac859a5698e99098b48457/patches/os/ACSD-62577_2.4.6.patch","thirdParty":false,"modifies":[{"path":"vendor/magento/module-search/Model/ResourceModel/Query/Collection.php","change":"modified","package":"magento/module-search"},{"path":"vendor/magento/module-search/etc/db_schema.xml","change":"modified","package":"magento/module-search"},{"path":"vendor/magento/module-search/etc/db_schema_whitelist.json","change":"modified","package":"magento/module-search"}],"packages":["magento/module-search"],"hasTests":false,"applicableVersions":{"magento-community":["2.4.6-p15","2.4.6-p14","2.4.6-p13","2.4.6-p12","2.4.6-p11","2.4.6-p10","2.4.6-p9","2.4.6-p8","2.4.6-p7","2.4.6-p6","2.4.6-p5","2.4.6-p4","2.4.6-p3","2.4.6-p2","2.4.6-p1","2.4.6","2.4.5-p17","2.4.5-p16","2.4.5-p15","2.4.5-p14","2.4.5-p13","2.4.5-p12","2.4.5-p11","2.4.5-p10","2.4.5-p9","2.4.5-p8","2.4.5-p7","2.4.5-p6","2.4.5-p5","2.4.5-p4","2.4.5-p3","2.4.5-p2","2.4.5-p1","2.4.5","2.4.4-p18","2.4.4-p17","2.4.4-p16","2.4.4-p15","2.4.4-p14","2.4.4-p13","2.4.4-p12","2.4.4-p11","2.4.4-p10","2.4.4-p9","2.4.4-p8","2.4.4-p7","2.4.4-p6","2.4.4-p5","2.4.4-p4","2.4.4-p3","2.4.4-p2","2.4.4-p1","2.4.4"],"magento-commerce":["2.4.6-p15","2.4.6-p14","2.4.6-p13","2.4.6-p12","2.4.6-p11","2.4.6-p10","2.4.6-p9","2.4.6-p8","2.4.6-p7","2.4.6-p6","2.4.6-p5","2.4.6-p4","2.4.6-p3","2.4.6-p2","2.4.6-p1","2.4.6","2.4.5-p17","2.4.5-p16","2.4.5-p15","2.4.5-p14","2.4.5-p13","2.4.5-p12","2.4.5-p11","2.4.5-p10","2.4.5-p9","2.4.5-p8","2.4.5-p7","2.4.5-p6","2.4.5-p5","2.4.5-p4","2.4.5-p3","2.4.5-p2","2.4.5-p1","2.4.5","2.4.4-p18","2.4.4-p17","2.4.4-p16","2.4.4-p15","2.4.4-p14","2.4.4-p13","2.4.4-p12","2.4.4-p11","2.4.4-p10","2.4.4-p9","2.4.4-p8","2.4.4-p7","2.4.4-p6","2.4.4-p5","2.4.4-p4","2.4.4-p3","2.4.4-p2","2.4.4-p1","2.4.4"],"mage-os":[]},"unknownVersions":[],"requires":[],"replacedWith":"ACSD-68040","deprecated":false},{"package":"magento/magento2-base","constraint":">=2.4.7 <2.4.8","path":"patches/os/ACSD-61957_2.4.7-p2.patch","blobSha":"5e63f68e0a0e237e3c2c3172f7e896f33b56b036","url":"https://raw.githubusercontent.com/magento/quality-patches/11d565bdde197b9c7bac859a5698e99098b48457/patches/os/ACSD-61957_2.4.7-p2.patch","thirdParty":false,"modifies":[{"path":"vendor/magento/module-search/Model/ResourceModel/Query/Collection.php","change":"modified","package":"magento/module-search"},{"path":"vendor/magento/module-search/etc/db_schema.xml","change":"modified","package":"magento/module-search"},{"path":"vendor/magento/module-search/etc/db_schema_whitelist.json","change":"modified","package":"magento/module-search"}],"packages":["magento/module-search"],"hasTests":false,"applicableVersions":{"magento-community":["2.4.7-p10","2.4.7-p9","2.4.7-p8","2.4.7-p7","2.4.7-p6","2.4.7-p5","2.4.7-p4","2.4.7-p3","2.4.7-p2","2.4.7-p1","2.4.7"],"magento-commerce":["2.4.7-p10","2.4.7-p9","2.4.7-p8","2.4.7-p7","2.4.7-p6","2.4.7-p5","2.4.7-p4","2.4.7-p3","2.4.7-p2","2.4.7-p1","2.4.7"],"mage-os":[]},"unknownVersions":[],"requires":[],"replacedWith":"ACSD-68040","deprecated":false}],"applicableVersions":{"magento-community":["2.4.7-p10","2.4.7-p9","2.4.7-p8","2.4.7-p7","2.4.7-p6","2.4.7-p5","2.4.7-p4","2.4.7-p3","2.4.7-p2","2.4.7-p1","2.4.7","2.4.6-p15","2.4.6-p14","2.4.6-p13","2.4.6-p12","2.4.6-p11","2.4.6-p10","2.4.6-p9","2.4.6-p8","2.4.6-p7","2.4.6-p6","2.4.6-p5","2.4.6-p4","2.4.6-p3","2.4.6-p2","2.4.6-p1","2.4.6","2.4.5-p17","2.4.5-p16","2.4.5-p15","2.4.5-p14","2.4.5-p13","2.4.5-p12","2.4.5-p11","2.4.5-p10","2.4.5-p9","2.4.5-p8","2.4.5-p7","2.4.5-p6","2.4.5-p5","2.4.5-p4","2.4.5-p3","2.4.5-p2","2.4.5-p1","2.4.5","2.4.4-p18","2.4.4-p17","2.4.4-p16","2.4.4-p15","2.4.4-p14","2.4.4-p13","2.4.4-p12","2.4.4-p11","2.4.4-p10","2.4.4-p9","2.4.4-p8","2.4.4-p7","2.4.4-p6","2.4.4-p5","2.4.4-p4","2.4.4-p3","2.4.4-p2","2.4.4-p1","2.4.4"],"magento-commerce":["2.4.7-p10","2.4.7-p9","2.4.7-p8","2.4.7-p7","2.4.7-p6","2.4.7-p5","2.4.7-p4","2.4.7-p3","2.4.7-p2","2.4.7-p1","2.4.7","2.4.6-p15","2.4.6-p14","2.4.6-p13","2.4.6-p12","2.4.6-p11","2.4.6-p10","2.4.6-p9","2.4.6-p8","2.4.6-p7","2.4.6-p6","2.4.6-p5","2.4.6-p4","2.4.6-p3","2.4.6-p2","2.4.6-p1","2.4.6","2.4.5-p17","2.4.5-p16","2.4.5-p15","2.4.5-p14","2.4.5-p13","2.4.5-p12","2.4.5-p11","2.4.5-p10","2.4.5-p9","2.4.5-p8","2.4.5-p7","2.4.5-p6","2.4.5-p5","2.4.5-p4","2.4.5-p3","2.4.5-p2","2.4.5-p1","2.4.5","2.4.4-p18","2.4.4-p17","2.4.4-p16","2.4.4-p15","2.4.4-p14","2.4.4-p13","2.4.4-p12","2.4.4-p11","2.4.4-p10","2.4.4-p9","2.4.4-p8","2.4.4-p7","2.4.4-p6","2.4.4-p5","2.4.4-p4","2.4.4-p3","2.4.4-p2","2.4.4-p1","2.4.4"],"mage-os":[]},"conditions":null,"requirements":null},"_documentation":"https://magento.watch/api","_description":"Quality patch ACSD-62577 details"}