QUALITY PATCH

ACSD-68040: The frontend search page slows significantly on MariaDB 10.6…

The frontend search page slows significantly on MariaDB 10.6 when processing a high volume of historical search requests. Quality patch ACSD-68040 for magento/module-search applies to Magento Open Source and Adobe Commerce 2.4.4 to 2.4.8-p5.

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

Issue

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.

Steps to reproduce

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.

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

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-68040. On Cloud, add ACSD-68040 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-68040/ 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.