Poor performance for some layered navigation queries

[k4emic]

Preconditions (*)

1. Magento 2.3.0 (2.3.1? any 2.x.x?)
2. Percona SQL Server >= 5.6
3. Lots of products
4. Lots of filterable attributes for layered navigation

Steps to reproduce (*)

1. Open any category with layered navigation enabled (anchor)

Expected result (*)

1. Page should show

Actual result (*)

1. Several seconds pass, page might give a timeout

I can't say entirely for sure that the above steps will make this problem reproducible for everyone, since the sql query optimizer seems to have a mind of its own, so I'll break down the technical details and discovery.

Tehcnical information

We initially discovered this after creating a new boolean attribute and making it filterable (see #3283). Shortly after deployment to an internal test-server, we began seeing the database workers eating a lot of CPU.

SHOW FULL PROCESSLIST hinted at this query being the problem:

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_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_1` AS `stock_index` ON stock_index.sku = product.sku
             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` = 99 AND `search_index`.`value` in ('2', '4') AND
             `search_index`.`store_id` = 1)
        AND (category_ids_index.category_id = 36800)
        AND (search_index.entity_id IN (
        select entity_id
        from (SELECT `e`.`entity_id`, IFNULL(current_store.value, main_table.value) AS `on_sale`
              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 = '565') AND (main_table.store_id = 0))
                AND (e.created_in <= '1522659288')
                AND (e.updated_in > '1522659288')
              HAVING (`on_sale` IN (1))) as filter
      ))) AS `main_select`
GROUP BY `entity_id`
ORDER BY `relevance` DESC, `entity_id` DESC
LIMIT 1000;

We identified the slow part being the following:

SELECT `e`.`entity_id`, IFNULL(current_store.value, main_table.value) AS `on_sale`
              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 = '565') AND (main_table.store_id = 0))
                AND (e.created_in <= '1522659288')
                AND (e.updated_in > '1522659288')
              HAVING (`on_sale` IN (1))

Fetching 500 rows takes several seconds

sql> SELECT SQL_NO_CACHE `e`.`entity_id`, IFNULL(current_store.value, main_table.value) AS `on_sale`
     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 = '565') AND (main_table.store_id = 0))
       AND (e.created_in <= '1522659288')
       AND (e.updated_in > '1522659288')
     HAVING (`on_sale` IN (1))
[2019-02-04 15:34:15] 500 rows retrieved starting from 1 in 18 s 167 ms (execution: 18 s 131 ms, fetching: 36 ms)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	main_table	NULL	ref	CATALOG_PRODUCT_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID	CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID	2	const	131420	50	Using where
1	SIMPLE	e	NULL	eq_ref	PRIMARY,CATALOG_PRODUCT_ENTITY_CREATED_IN,CATALOG_PRODUCT_ENTITY_UPDATED_IN	PRIMARY	4	x9792.main_table.row_id	1	25	Using where
1	SIMPLE	current_store	NULL	ref	CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID	CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID	2	x9792.main_table.attribute_id	7120	100	Using where

Modifying the main_table.attribute_id = '565' to main_table.attribute_id = 565 gave a significant performance-boost.

sql> explain SELECT SQL_NO_CACHE `e`.`entity_id`, IFNULL(current_store.value, main_table.value) AS `on_sale`
     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 = 565) AND (main_table.store_id = 0))
       AND (e.created_in <= '1522659288')
       AND (e.updated_in > '1522659288')
     HAVING (`on_sale` IN (1))
[2019-02-04 15:36:43] 3 rows retrieved starting from 1 in 85 ms (execution: 56 ms, fetching: 29 ms)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	main_table	NULL	ref	CATALOG_PRODUCT_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID	CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID	2	const	131420	50	Using where
1	SIMPLE	e	NULL	eq_ref	PRIMARY,CATALOG_PRODUCT_ENTITY_CREATED_IN,CATALOG_PRODUCT_ENTITY_UPDATED_IN	PRIMARY	4	x9792.main_table.row_id	1	25	Using where
1	SIMPLE	current_store	NULL	index_merge	CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID	CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID	2,2	NULL	36260	100	Using intersect(CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID); Using where; Using join buffer (Block Nested Loop)

I could be missing something, but I think the conclusion here is that the sql query optimizer isn't working properly when a string is being provided in a WHERE clause, for a an int column.
Magento 1 has also been plagued by similar problems:

• cast to string in loadByIncrementId, fix big regression for numeric incrementIds OpenMage/magento-lts#381
• Use describeTable and then use prepareColumnValue to actually prepare… OpenMage/magento-lts#383

[magento-engcom-team]

Hi @k4emic. Thank you for your report.
To help us process this issue please make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Please make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, please, add a comment to the issue:

@magento-engcom-team give me 2.3-develop instance - upcoming 2.3.x release

For more details, please, review the Magento Contributor Assistant documentation.

@k4emic do you confirm that you was able to reproduce the issue on vanilla Magento instance following steps to reproduce?

• yes
• no

[magento-engcom-team]

Hi @engcom-backlog-nazar. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

• 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• 5. Verify that the issue is reproducible on 2.2-develop branch.
  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

Next steps are available in case you are a member of Quality Maintainers or Open Source Maintainers

• 6. Add label Issue: Confirmed once verification is complete.

• 7. Make sure that automatic system confirms that report has been added to the backlog.

[magento-engcom-team]

✅ Confirmed by @engcom-backlog-nazar
Thank you for verifying the issue. Based on the provided information internal tickets MAGETWO-98078, MAGETWO-98079 were created

Issue Available: @engcom-backlog-nazar, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[magento-engcom-team]

Hi @k4emic. Thank you for your report.
The issue has been fixed in #20971 by @k4emic in 2.3-develop branch
Related commit(s):

• c5c5e418e0af9a685c12fe7800477038882ed7c8
• 77b0ae817bdda6a3a76be0a4ccfab2a394a6bea1

The fix will be available with the upcoming 2.3.2 release.