{"data":{"id":"ACSD-68040","source":"qpt","sourceRef":"magento/quality-patches@1.1.84","title":"This fix improves the performance of the storefront search page in the case of a large number of historical search requests.","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-72/acsd-68040","path":"help/tools/quality-patches-tool/patches-available-in-qpt/v1-1-72/acsd-68040.md","blobSha":"49f939947b9f06c3ea79aab6e9c983521c485c4c","description":"The frontend search page slows significantly on MariaDB 10.6 when processing a high volume of historical search requests. A query on the search_query table takes up to 13 seconds on MariaDB 10.6, compared to under 0.5 seconds on MariaDB 11.4.","stepsToReproduce":"1. Install a clean instance with MariaDB 10.6 for testing purposes. 1. Populate the search_query table with 2 million entries. 1. Execute the following SQL query: SELECT COUNT(*) FROM ( SELECT 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 = 'seed_q_0000009'); 1. Observe that the query takes approximately 13 seconds to complete. 1. Upgrade the database to MariaDB 11.4. 1. Re-execute the same SQL query: SELECT COUNT(*) FROM ( SELECT 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 = 'seed_q_0000009'); 1. Observe that the query now completes in approximately 0.4–0.5 seconds.","introducedInQpt":"1.1.72","createdFor":["2.4.6-p12"],"documentedRange":"2.4.4 - 2.4.6-p12","scheduledFixRelease":null},"files":[{"package":"magento/magento2-base","constraint":">=2.4.4 <2.4.5-p16 || >=2.4.6 <2.4.7","path":"patches/os/ACSD-68040_2.4.6-p12.patch","blobSha":"bf8a741ed2a6ca031e063ac29b7a7d17fa08e46a","url":"https://raw.githubusercontent.com/magento/quality-patches/11d565bdde197b9c7bac859a5698e99098b48457/patches/os/ACSD-68040_2.4.6-p12.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-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-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":null,"deprecated":false},{"package":"magento/magento2-base","constraint":">=2.4.7 <2.4.8","path":"patches/os/ACSD-68706_2.4.7-p6.patch","blobSha":"4944e6d0f39dd7d2a12cb0bba723bab22ed49cf5","url":"https://raw.githubusercontent.com/magento/quality-patches/11d565bdde197b9c7bac859a5698e99098b48457/patches/os/ACSD-68706_2.4.7-p6.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":null,"deprecated":false},{"package":"magento/magento2-base","constraint":">=2.4.8 <2.4.9","path":"patches/os/ACSD-69201_2.4.8-p3.patch","blobSha":"9b17f61dc9701e0393c608eb4d578a544d33b776","url":"https://raw.githubusercontent.com/magento/quality-patches/11d565bdde197b9c7bac859a5698e99098b48457/patches/os/ACSD-69201_2.4.8-p3.patch","thirdParty":false,"modifies":[{"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.8-p5","2.4.8-p4","2.4.8-p3","2.4.8-p2","2.4.8-p1","2.4.8"],"magento-commerce":["2.4.8-p5","2.4.8-p4","2.4.8-p3","2.4.8-p2","2.4.8-p1","2.4.8"],"mage-os":[]},"unknownVersions":[],"requires":[],"replacedWith":null,"deprecated":false}],"applicableVersions":{"magento-community":["2.4.8-p5","2.4.8-p4","2.4.8-p3","2.4.8-p2","2.4.8-p1","2.4.8","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-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.8-p5","2.4.8-p4","2.4.8-p3","2.4.8-p2","2.4.8-p1","2.4.8","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-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-68040 details"}