Skip to content

Performance degradation in Layered navigation using Yes/No attribute #21771

@stkec

Description

@stkec

Relates to #20969 and #3283

Preconditions

  1. Magento 2.3.0 (2.3.1? any 2.x.x?)
  2. Percona SQL Server >= 5.6
  3. About 65 000 of products with about 50 attributes in 15 attribute sets
  4. Lots of filterable attributes for layered navigation
  5. Applied patch from [FEATURE] [issue-3283] Added Filter Support for Yes/No (boolean) attr… #17823
  6. Using Yes/No attribute in Layered navigation.
  7. Using MySql Search Engine (set in admin panel)

Steps to reproduce

  1. Set Yes/No Product attribute 'Filterable'
  2. Go to Anchor Category Page on frontend
  3. Try to apply filter by Yes/No attribute

Expected result

  1. Filter applied in reasonable time

Actual result (

  1. Request lasts for several hours.
  2. MySql eats lot of CPU freezing the site

Root of a problem

After #3283 merged it is possible to use Yes/No attributes in Layered Navigation. But when MySql engine assembles the SQL query there are 2 more places where FrontendInput of the Layered Navigation Attribute is hardcoded as ['select', 'multiselect']. As a result, Yes/No attribute processed not as a 'Term' condition as it suppose to be for Layered Attribute but as a default unknown condition. In the end search query assembled as following:

SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT  `search_index`.`entity_id`, (((0) + (0) + (0)) * 1) AS `score` FROM `catalog_product_index_eav` AS `search_index`
 INNER JOIN `catalog_product_index_eav` AS `delivery_active_filter` ON `search_index`.`entity_id` = `delivery_active_filter`.`entity_id` AND `delivery_active_filter`.`attribute_id` = 549 AND `delivery_active_filter`.`store_id` = 1
 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = search_index.entity_id AND (product.created_in <= '1522659288' AND product.updated_in > '1522659288')
 INNER JOIN `inventory_stock_44` AS `stock_index` ON stock_index.sku = product.sku
 INNER JOIN `catalog_product_entity` AS `sub_product` ON sub_product.entity_id = search_index.source_id AND (sub_product.created_in <= '1522659288' AND sub_product.updated_in > '1522659288')
 INNER JOIN `inventory_stock_44` AS `sub_product_stock_index` ON sub_product_stock_index.sku = sub_product.sku
 INNER JOIN `catalog_product_index_eav` AS `visibility_filter` ON search_index.entity_id = visibility_filter.entity_id AND `visibility_filter`.`attribute_id` = 99 AND `visibility_filter`.`value` in ('2', '4') AND `visibility_filter`.`store_id` = '1'
 INNER JOIN `catalog_category_product_index_store1` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '1' WHERE (search_index.store_id = '1') AND (`search_index`.`attribute_id` = 549) AND (`search_index`.`value` in ('1')) AND (category_ids_index.category_id = 36799) AND (search_index.entity_id IN (
                select entity_id from  (SELECT `e`.`entity_id`, IFNULL(current_store.value, main_table.value) AS `delivery_active` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `main_table` ON main_table.row_id = e.row_id
 LEFT JOIN `catalog_product_entity_int` AS `current_store` ON current_store.attribute_id = main_table.attribute_id AND current_store.store_id = 1 WHERE ((main_table.attribute_id = '549') AND (main_table.store_id = 0)) AND (e.created_in <= '1522659288') AND (e.updated_in > '1522659288') HAVING (`delivery_active` IN ('1'))) as filter
            ))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC

Notice following in the 'WHERE' condition:

search_index.entity_id IN (
                select entity_id from  (SELECT `e`.`entity_id`, IFNULL(current_store.value, main_table.value) AS `delivery_active` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `main_table` ON main_table.row_id = e.row_id
 LEFT JOIN `catalog_product_entity_int` AS `current_store` ON current_store.attribute_id = main_table.attribute_id AND current_store.store_id = 1 WHERE ((main_table.attribute_id = '549') AND (main_table.store_id = 0)) AND (e.created_in <= '1522659288') AND (e.updated_in > '1522659288') HAVING (`delivery_active` IN ('1'))) as filter

The catalog_product_entity_int joined twice with 'HAVING' condition making this request vary hard to optimize for MySql.

The Fix

In Magento\CatalogSearch\Model\Adapter\Mysql\Filter\Preprocessor.php and Magento\CatalogSearch\Model\Search\CustomAttributeFilterCheck.php add 'boolean' to the Frontend Input array.
This will guide engine to handle Yes/No attributes same as select or multiselect - joining from catalog_product_index_eav table.
No double join with having - no performance struggling.

Metadata

Metadata

Assignees

Labels

Fixed in 2.3.xThe issue has been fixed in 2.3 release lineIssue: Format is validGate 1 Passed. Automatic verification of issue format passed

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions