Randomly getting flooded with queries from related / upsell / crosssell blocks and price indexing

[ioweb-gr]

Preconditions and environment

• Magento 2.4.1
• Multiple websites at least 5
• At least 50 price rules
• 150k products
• 800+ categories
• At least 15 customer groups

Steps to reproduce

I don't have the exact steps, other than the fact that reindexing the price rules takes too long. However in my case I see hundreds of queries like this

Which take too long to finish and drop our website

Expected result

The site is still working and queries are much faster and won't bring the site down.

Actual result

The website is down with a lot of queries in queue. Over 2k siilar to this

SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position` FROM `catalog_product_entity` AS `e` INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '2' INNER JOIN `catalog_category_product_index_store5` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=5 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id INNER JOIN `inventory_stock_5` AS `stock_status_index` ON product.sku = stock_status_index.sku INNER JOIN `catalog_product_link` AS `links` ON links.linked_product_id = e.entity_id AND links.link_type_id = 4 LEFT JOIN `catalog_product_link_attribute_int` AS `link_attribute_position_int` ON link_attribute_position_int.link_id = links.link_id AND link_attribute_position_int.product_link_attribute_id = '3' INNER JOIN `catalog_product_entity` AS `product_entity_table` ON links.product_id = product_entity_table.entity_id WHERE (inventory_in_stock.is_salable = 1) AND (stock_status_index.is_salable = 1) AND (links.product_id in ('80468')) AND (`e`.`entity_id` != '80468') ORDER BY `position` ASC

Additional information

No response

Release note

No response

Triage and priority

• [X] Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• [ ] Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• [ ] Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• [ ] Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• [ ] Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

[ioweb-gr]

Rows_examined: 1139192931

This is actually an absurd amount. I've added temporarily a sleep(10) to make the query get logged in mariadb slow log and executed it myself, it said rows examined 53k

There's something that's causing the rows to expand to millions for thousands but I can't quite get a grasp of what causes it.

READ COMMITTED made our website drop 10 times more daily than normally.

What we know so far after I've discussed this in mariadb bug tracker:

When RENAME table kicks in, if there's any active select, it has to wait for the select to finish in order to acquire it's exclusive lock. While the exclusive lock required is pending, no further queries will execute on this table.

An active SELECT query would hold a metadata lock for the duration of the query, preventing the RENAME TABLE operation from acquiring the lock it needs until the SELECT query is complete.

So, even if you change the isolation level to READ COMMITTED, you'll still have to wait for ongoing SELECT queries to finish before you can successfully rename the table.

Finally it seems that MariaDB is picking a different query execution plan every time this issue occurs than when running it standalone and for some reason the amount of rows examined is totally different

[ioweb-gr]

I'm trying to eliminate the duplicate joins as I think it's the main cause for high rows to be examined as the multiplication in such cases to create a tmp table can be high. Any ideas where to look for those joins?

[fuhye]

hello

i dont about MariaDb i work with SQL Server i see it problem on freelancer platform

if you have result sets with 1139192931 rows you have optimize your queries

so i belive the problem could be that in "AND (e.entity_id != '80468')". With that instruction Instead of use seek index it force to use index scan.

SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, cat_index.position AS cat_index_position, stock_status_index.is_salable, links.link_id, links.product_id AS _linked_to_product_id, link_attribute_position_int.value AS position FROM catalog_product_entity AS e INNER JOIN inventory_stock_5 AS inventory_in_stock ON e.sku = inventory_in_stock.sku INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '2' INNER JOIN catalog_category_product_index_store5 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=5 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2 INNER JOIN catalog_product_entity AS product ON product.entity_id = e.entity_id INNER JOIN inventory_stock_5 AS stock_status_index ON product.sku = stock_status_index.sku INNER JOIN catalog_product_link AS links ON links.linked_product_id = e.entity_id AND links.link_type_id = 4 LEFT JOIN catalog_product_link_attribute_int AS link_attribute_position_int ON link_attribute_position_int.link_id = links.link_id AND link_attribute_position_int.product_link_attribute_id = '3' INNER JOIN catalog_product_entity AS product_entity_table ON links.product_id = product_entity_table.entity_id WHERE (inventory_in_stock.is_salable = 1) AND (stock_status_index.is_salable = 1) AND (links.product_id in ('80468')) AND (e.entity_id != '80468') ORDER BY position ASC

[Adel-Magebinary]

@ioweb-gr could it be a bug in MySQL? Maybe ask the Maria community? what could cause the ROW examed to be so high.

[ioweb-gr]

I've already done @Adel-Magebinary on the mariadb jira ticket. I've even talked to their company's support to see what is happening. Unfortunately they're too high budget for us to pursue that avenue but they hinted that we should upgrade to 10.6 and see if we can get better traces. So I did yesterday in an effort to see if we can get to the bottom of this.

Just waiting on the next occurrence of the issue now.

I'm sincerely hoping this will turn out to be an actual bug in mariadb that was solved in 10.6 because nothing makes sense on that.

But to summarize and answer to your questions guys

When the problem first appeared this is the explain for example

```sql # Thread_id: 360163 Schema: db_edomainnlive QC_hit: No -- # Query_time: 2400.601619 Lock_time: 0.000116 Rows_sent: 6 Rows_examined: 1580173995 # Rows_affected: 0 Bytes_sent: 3463 # Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 127296 # Full_scan: Yes Full_join: Yes Tmp_table: Yes Tmp_table_on_disk: No # Filesort: Yes Filesort_on_disk: No Merge_passes: 0 Priority_queue: No # # explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra # explain: 1 SIMPLE inventory_in_stock ALL PRIMARY,index_sku_qty NULL NULL NULL 1 85645.00 100.00 33.52 Using where; Using temporary; Using filesort # explain: 1 SIMPLE stock_status_index ALL PRIMARY,index_sku_qty NULL NULL NULL 1 85645.00 100.00 33.52 Using where; Using join buffer (flat, BNL join) # explain: 1 SIMPLE product ref PRIMARY,CATALOG_PRODUCT_ENTITY_SKU CATALOG_PRODUCT_ENTITY_SKU 195 db_edomainnlive.stock_status_index.sku 1 1.00 100.00 100.00 Using index # explain: 1 SIMPLE cat_index eq_ref PRIMARY,CAT_CTGR_PRD_IDX_STORE5_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,IDX_D66B6557126C2DE627A049511C72BE10 PRIMARY 10 const,db_edomainnlive.product.entity_id,const 1 0.31 100.00 99.95 Using where # explain: 1 SIMPLE price_index eq_ref PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE PRIMARY 10 db_edomainnlive.product.entity_id,const,const 1 0.99 100.00 100.00 # explain: 1 SIMPLE e eq_ref PRIMARY,CATALOG_PRODUCT_ENTITY_SKU PRIMARY 4 db_edomainnlive.product.entity_id 1 1.00 100.00 0.00 Using where # explain: 1 SIMPLE links eq_ref CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID 10 const,const,db_edomainnlive.product.entity_id 1 0.00 100.00 100.00 Using index # explain: 1 SIMPLE product_entity_table eq_ref PRIMARY PRIMARY 4 db_edomainnlive.links.product_id 1 1.00 100.00 100.00 Using index # explain: 1 SIMPLE link_attribute_position_int eq_ref CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID 7 const,db_edomainnlive.links.link_id 1 1.00 100.00 100.00 # explain: 1 SIMPLE link_attribute_qty_decimal eq_ref CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID 7const,db_edomainnlive.links.link_id 1 0.00 100.00 100.00 ```

Indeed it was doing a full scan which is why I added the indexers in is_salable and sku columns of inventorystock* tables

Which led to this explain

```sql # Time: 230918 12:29:22 -- # User@Host: db_edomainnlive[db_edomainnlive] @ localhost [] # Thread_id: 2128135 Schema: db_edomainnlive QC_hit: No # Query_time: 2645.962335 Lock_time: 0.000091 Rows_sent: 0 Rows_examined: 1094333226 # Rows_affected: 0 Bytes_sent: 0 # Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 127296 # Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No # # explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra # explain: 1 SIMPLE inventory_in_stock ref PRIMARY,index_sku_qty,index_is_salable,index_sku index_is_salable 1 const 1 17071.00 100.00 100.00 Using index; Using temporary; Using filesort # explain: 1 SIMPLE stock_status_index ref PRIMARY,index_sku_qty,index_is_salable,index_sku index_is_salable 1 const 1 22144.90 100.00 100.00 Using index # explain: 1 SIMPLE product ref PRIMARY,CATALOG_PRODUCT_ENTITY_SKU CATALOG_PRODUCT_ENTITY_SKU 195 db_edomainnlive.stock_status_index.sku 1 1.00 100.00 100.00 Using index # explain: 1 SIMPLE cat_index eq_ref PRIMARY,CAT_CTGR_PRD_IDX_STORE1_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,IDX_216E521C8AD125E066D2B0BAB4A08412 PRIMARY 10 const,db_edomainnlive.product.entity_id,const 1 0.30 100.00 99.88 Using where # explain: 1 SIMPLE price_index eq_ref PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE PRIMARY 10 db_edomainnlive.product.entity_id,const,const 1 0.99 100.00 100.00 # explain: 1 SIMPLE e eq_ref PRIMARY,CATALOG_PRODUCT_ENTITY_SKU PRIMARY 4 db_edomainnlive.product.entity_id 1 1.00 100.00 0.00 Using where # explain: 1 SIMPLE links eq_ref CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID 10 const,const,db_edomainnlive.product.entity_id 1 0.00 100.00 100.00 Using index # explain: 1 SIMPLE product_entity_table eq_ref PRIMARY PRIMARY 4 db_edomainnlive.links.product_id 1 1.00 100.00 100.00 Using index # explain: 1 SIMPLE link_attribute_position_int eq_ref CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID 7 const,db_edomainnlive.links.link_id 1 1.00 100.00 100.00 # explain: 1 SIMPLE link_attribute_qty_decimal eq_ref CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID 7 const,db_edomainnlive.links.link_id 1 0.00 100.00 100.00 # SET timestamp=1695032962; SELECT `e`.*, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`, `stock_status_index`.`is_salable`, `links`.`link_id`, `links`.`product_id` AS `_linked_to_product_id`, `link_attribute_position_int`.`value` AS `position`, `link_attribute_qty_decimal`.`value` AS `qty` FROM `catalog_product_entity` AS `e` INNER JOIN `inventory_stock_7` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1' INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2 INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id INNER JOIN `inventory_stock_7` AS `stock_status_index` ON product.sku = stock_status_index.sku INNER JOIN `catalog_product_link` AS `links` ON links.linked_product_id = e.entity_id AND links.link_type_id = 1 LEFT JOIN `catalog_product_link_attribute_int` AS `link_attribute_position_int` ON link_attribute_position_int.link_id = links.link_id AND link_attribute_position_int.product_link_attribute_id = '2' LEFT JOIN `catalog_product_link_attribute_decimal` AS `link_attribute_qty_decimal` ON link_attribute_qty_decimal.link_id = links.link_id AND link_attribute_qty_decimal.product_link_attribute_id = '6' INNER JOIN `catalog_product_entity` AS `product_entity_table` ON links.product_id = product_entity_table.entity_id WHERE (inventory_in_stock.is_salable = 1) AND (stock_status_index.is_salable = 1) AND (links.product_id in ('179427')) AND (`e`.`entity_id` != '179427') ORDER BY `position` ASC; ```

Which solved the full scan issue, but still got a huge rows examined issue

When exexcuting normally without the choked situation I get a normal explain

```sql +--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------------------------------------------+ -- \|id\|select_type\|table \|type \|possible_keys \|key \|key_len\|ref \|rows\|Extra \| +--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------------------------------------------+ \|1 \|SIMPLE \|links \|ref \|CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID\|CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID\|6 \|const,const \|6 \|Using index; Using temporary; Using filesort\| \|1 \|SIMPLE \|cat_index \|eq_ref\|PRIMARY,CAT_CTGR_PRD_IDX_STORE5_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,IDX_D66B6557126C2DE627A049511C72BE10 \|PRIMARY \|10 \|const,db_edomainnlive.links.linked_product_id,const\|1 \|Using where \| \|1 \|SIMPLE \|price_index \|eq_ref\|PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE \|PRIMARY \|10 \|db_edomainnlive.links.linked_product_id,const,const\|1 \| \| \|1 \|SIMPLE \|e \|eq_ref\|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU \|PRIMARY \|4 \|db_edomainnlive.links.linked_product_id \|1 \|Using where \| \|1 \|SIMPLE \|inventory_in_stock \|eq_ref\|PRIMARY,index_sku_qty \|PRIMARY \|194 \|db_edomainnlive.e.sku \|1 \|Using where \| \|1 \|SIMPLE \|product \|eq_ref\|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU \|PRIMARY \|4 \|db_edomainnlive.links.linked_product_id \|1 \|Using where \| \|1 \|SIMPLE \|stock_status_index \|eq_ref\|PRIMARY,index_sku_qty \|PRIMARY \|194 \|db_edomainnlive.product.sku \|1 \|Using where \| \|1 \|SIMPLE \|product_entity_table \|eq_ref\|PRIMARY \|PRIMARY \|4 \|db_edomainnlive.links.product_id \|1 \|Using index \| \|1 \|SIMPLE \|link_attribute_position_int\|eq_ref\|CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID \|CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID \|7 \|const,db_edomainnlive.links.link_id \|1 \| \| \|1 \|SIMPLE \|link_attribute_qty_decimal \|eq_ref\|CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID \|CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID \|7 \|const,db_edomainnlive.links.link_id \|1 \| \| +--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------------------------------------------+ ```

But because the query is by no means slow, 70-100ms, it's not logged in the slow log with full info about the rows examined.

The entity_id != clause is needed to filter out the same product from the linked products and it's only coming after the in(entity_id) clause

I'm seriously buffled about this because everything looks normally in place but an absurd amount of rows examined.

I wonder if this is a fault in the mariadb optimizer.

I'll ask in Jira about this @fuhye to see if it could be a possible cause but the deal here is that whenever I run it myself, it ends fast. So I cannot replicate the slowness to see if something would fix it but only through observation.

E.g.

• adding the indexes changed the slow log output and it could be observed in the explain but didnt' solve the slowness issue.
• changing the ids to integers instead of strings to prevent type casting didn't help either

[ioweb-gr]

I'm beginning to think it's the optimizer switching the execution plan order of tables.

What I notice is on the slow log explain looks like this examining 199043934 rows

# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  inventory_in_stock      ref     PRIMARY,index_sku_qty,index_is_salable,index_sku        index_is_salable        1       const   1       5822.00 100.00  100.00  Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  stock_status_index      ref     PRIMARY,index_sku_qty,index_is_salable,index_sku        index_is_salable        1       const   1       17093.62        100.00  100.00  Using index
# explain: 1    SIMPLE  e       ref     PRIMARY,CATALOG_PRODUCT_ENTITY_SKU      CATALOG_PRODUCT_ENTITY_SKU      195     db_edomainnlive.inventory_in_stock.sku  1       1.00    100.00  100.00  Using index condition
# explain: 1    SIMPLE  links   eq_ref  CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID   CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID   10      const,const,db_edomainnlive.e.entity_id 1       0.00    100.00  100.00  Using index
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60       PRIMARY 10      const,db_edomainnlive.e.entity_id,const 1       NULL    100.00   NULL    Using where
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE   PRIMARY 10      db_edomainnlive.e.entity_id,const,const 1NULL    100.00  NULL
# explain: 1    SIMPLE  product eq_ref  PRIMARY,CATALOG_PRODUCT_ENTITY_SKU      PRIMARY 4       db_edomainnlive.e.entity_id     1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  product_entity_table    eq_ref  PRIMARY PRIMARY 4       db_edomainnlive.links.product_id        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  link_attribute_position_int     eq_ref  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID     7       const,db_edomainnlive.links.link_id      1       NULL    100.00  NULL
# explain: 1    SIMPLE  link_attribute_qty_decimal      eq_ref  CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID      CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID     7const,db_edomainnlive.links.link_id     1       NULL    100.00  NULL

When I execute it the order differs

+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+
|id|select_type|table                      |type  |possible_keys                                                                                                                        |key                                                           |key_len|ref                                                |rows|filtered|Extra                                       |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+
|1 |SIMPLE     |links                      |ref   |CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID|CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID|6      |const,const                                        |2   |100     |Using index; Using temporary; Using filesort|
|1 |SIMPLE     |cat_index                  |eq_ref|PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60                                                    |PRIMARY                                                       |10     |const,db_edomainnlive.links.linked_product_id,const|1   |100     |Using where                                 |
|1 |SIMPLE     |price_index                |eq_ref|PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE                                |PRIMARY                                                       |10     |db_edomainnlive.links.linked_product_id,const,const|1   |100     |                                            |
|1 |SIMPLE     |e                          |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_edomainnlive.links.linked_product_id            |1   |100     |Using where                                 |
|1 |SIMPLE     |inventory_in_stock         |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku                                                                                     |PRIMARY                                                       |194    |db_edomainnlive.e.sku                              |1   |100     |Using where                                 |
|1 |SIMPLE     |product                    |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_edomainnlive.links.linked_product_id            |1   |100     |Using where                                 |
|1 |SIMPLE     |stock_status_index         |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku                                                                                     |PRIMARY                                                       |194    |db_edomainnlive.product.sku                        |1   |100     |Using where                                 |
|1 |SIMPLE     |product_entity_table       |eq_ref|PRIMARY                                                                                                                              |PRIMARY                                                       |4      |db_edomainnlive.links.product_id                   |1   |100     |Using index                                 |
|1 |SIMPLE     |link_attribute_position_int|eq_ref|CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID                                               |CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_edomainnlive.links.link_id                |1   |100     |                                            |
|1 |SIMPLE     |link_attribute_qty_decimal |eq_ref|CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID                                           |CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_edomainnlive.links.link_id                |1   |100     |                                            |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+

[ioweb-gr]

The more I think about it the problem must be the optimizer, the query plans are too different and the only real limitation on columns is the entity_id in the links table and the product_entity_table.

I think the optimizer may just need a hint to use those tables always as the order that magento joins the tables hints to a bad query plan.

So I was thinking about how I can modify this

\Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection::_joinLinks

So that

        $joinCondition = [
            'links.linked_product_id = e.entity_id',
            $connection->quoteInto('links.link_type_id = ?', $this->_linkTypeId),
        ];

Will actually create something like this

         INNER JOIN `catalog_product_link` AS `links` FORCE INDEX (CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID, CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID)
                    ON links.linked_product_id = e.entity_id AND links.link_type_id = 1

So that maybe the optimizer can consistently use the index which is used as a filter in where clause.

@Adel-Magebinary Do you happen to know for how to force the index using the abstraction layer? I think it's worth a shot

I think this is the relevant function

    /**
     * Join linked products and their attributes
     *
     * @return $this
     */
    protected function _joinLinks()
    {
        $select = $this->getSelect();
        $connection = $select->getConnection();

        $joinCondition = [
            'links.linked_product_id = e.entity_id',
            $connection->quoteInto('links.link_type_id = ?', $this->_linkTypeId),
        ];
        $joinType = 'join';
        $linkField = $this->getLinkField();
        if ($this->productIds) {
            if ($this->_isStrongMode) {
                $this->getSelect()->where('links.product_id in (?)', $this->productIds);
            } else {
                $joinType = 'joinLeft';
                $joinCondition[] = $connection->quoteInto('links.product_id in (?)', $this->productIds);
            }
            if (count($this->productIds) === 1) {
                $this->addFieldToFilter(
                    $linkField,
                    ['neq' => array_values($this->productIds)[0]]
                );
            }
        } elseif ($this->_isStrongMode) {
            $this->addFieldToFilter(
                $linkField,
                ['eq' => -1]
            );
        }
        if ($this->_hasLinkFilter) {
            $select->{$joinType}(
                ['links' => $this->getTable('catalog_product_link')],
                implode(' AND ', $joinCondition),
                ['link_id' => 'link_id', '_linked_to_product_id' => 'product_id']
            );
            $this->joinAttributes();
        }
        return $this;
    }

[fuhye]

I think a better option would be something like this

CREATE TABLE myTable ( id MEDIUMINT NOT NULL AUTO_INCREMENT, identity_id int NOT NULL, PRIMARY KEY (id) );

-- add entity_ids

SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, cat_index.position AS cat_index_position, stock_status_index.is_salable, links.link_id, links.product_id AS _linked_to_product_id, link_attribute_position_int.value AS position, link_attribute_qty_decimal.value AS qty FROM catalog_product_entity AS e INNER JOIN inventory_stock_7 AS inventory_in_stock ON e.sku = inventory_in_stock.sku INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '1' INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2 INNER JOIN catalog_product_entity AS product ON product.entity_id = e.entity_id INNER JOIN inventory_stock_7 AS stock_status_index ON product.sku = stock_status_index.sku INNER JOIN catalog_product_link AS links ON links.linked_product_id = e.entity_id AND links.link_type_id = 1 LEFT JOIN catalog_product_link_attribute_int AS link_attribute_position_int ON link_attribute_position_int.link_id = links.link_id AND link_attribute_position_int.product_link_attribute_id = '2' LEFT JOIN catalog_product_link_attribute_decimal AS link_attribute_qty_decimal ON link_attribute_qty_decimal.link_id = links.link_id AND link_attribute_qty_decimal.product_link_attribute_id = '6' INNER JOIN catalog_product_entity AS product_entity_table ON links.product_id = product_entity_table.entity_id

LEFT JOIN myTable as t on t.Identity_Id = e.entity_id

WHERE (inventory_in_stock.is_salable = 1) AND (stock_status_index.is_salable = 1) AND (links.product_id in ('179427')) AND t.identity_id is null ORDER BY position ASC;

El mar, 19 sept 2023 a las 8:40, IOWEB TECHNOLOGIES (< @.***>) escribió:

I'm beginning to think it's the optimizer switching the execution plan order of tables.

What I notice is on the slow log explain looks like this examining 199043934 rows

explain: id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra

explain: 1 SIMPLE inventory_in_stock ref PRIMARY,index_sku_qty,index_is_salable,index_sku index_is_salable 1 const 1 5822.00 100.00 100.00 Using index; Using temporary; Using filesort

explain: 1 SIMPLE stock_status_index ref PRIMARY,index_sku_qty,index_is_salable,index_sku index_is_salable 1 const 1 17093.62 100.00 100.00 Using index

explain: 1 SIMPLE e ref PRIMARY,CATALOG_PRODUCT_ENTITY_SKU CATALOG_PRODUCT_ENTITY_SKU 195 db_edomainnlive.inventory_in_stock.sku 1 1.00 100.00 100.00 Using index condition

explain: 1 SIMPLE links eq_ref CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID 10 const,const,db_edomainnlive.e.entity_id 1 0.00 100.00 100.00 Using index

explain: 1 SIMPLE cat_index eq_ref PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60 PRIMARY 10 const,db_edomainnlive.e.entity_id,const 1 NULL 100.00 NULL Using where

explain: 1 SIMPLE price_index eq_ref PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE PRIMARY 10 db_edomainnlive.e.entity_id,const,const 1NULL 100.00 NULL

explain: 1 SIMPLE product eq_ref PRIMARY,CATALOG_PRODUCT_ENTITY_SKU PRIMARY 4 db_edomainnlive.e.entity_id 1 NULL 100.00 NULL Using where

explain: 1 SIMPLE product_entity_table eq_ref PRIMARY PRIMARY 4 db_edomainnlive.links.product_id 1 NULL 100.00 NULL Using index

explain: 1 SIMPLE link_attribute_position_int eq_ref CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID 7 const,db_edomainnlive.links.link_id 1 NULL 100.00 NULL

explain: 1 SIMPLE link_attribute_qty_decimal eq_ref CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID 7const,db_edomainnlive.links.link_id 1 NULL 100.00 NULL

When I execute it the order differs

+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+ |id|select_type|table |type |possible_keys |key |key_len|ref |rows|filtered|Extra | +--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+ |1 |SIMPLE |links |ref |CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID|CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID|6 |const,const |2 |100 |Using index; Using temporary; Using filesort| |1 |SIMPLE |cat_index |eq_ref|PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60 |PRIMARY |10 |const,db_edomainnlive.links.linked_product_id,const|1 |100 |Using where | |1 |SIMPLE |price_index |eq_ref|PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE |PRIMARY |10 |db_edomainnlive.links.linked_product_id,const,const|1 |100 | | |1 |SIMPLE |e |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU |PRIMARY |4 |db_edomainnlive.links.linked_product_id |1 |100 |Using where | |1 |SIMPLE |inventory_in_stock |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku |PRIMARY |194 |db_edomainnlive.e.sku |1 |100 |Using where | |1 |SIMPLE |product |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU |PRIMARY |4 |db_edomainnlive.links.linked_product_id |1 |100 |Using where | |1 |SIMPLE |stock_status_index |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku |PRIMARY |194 |db_edomainnlive.product.sku |1 |100 |Using where | |1 |SIMPLE |product_entity_table |eq_ref|PRIMARY |PRIMARY |4 |db_edomainnlive.links.product_id |1 |100 |Using index | |1 |SIMPLE |link_attribute_position_int|eq_ref|CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID |CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID |7 |const,db_edomainnlive.links.link_id |1 |100 | | |1 |SIMPLE |link_attribute_qty_decimal |eq_ref|CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID |CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID |7 |const,db_edomainnlive.links.link_id |1 |100 | | +--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+

— Reply to this email directly, view it on GitHub https://github.com/magento/magento2/issues/36667#issuecomment-1725549906, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACP3V56UPXUQE5U4YMQGOKLX3GOGHANCNFSM6AAAAAATHYQTKQ . You are receiving this because you were mentioned.Message ID: @.***>

--

Jorge Carlos Cocom Pech Desarrollador .net (52)99 81 03 48 42 | @.*** Cancún, Q.Roo México https://maps.google.com/?q=Canc%C3%BAn,%20Q.Roo%20M%C3%A9xico

[Adel-Magebinary]

I'm beginning to think it's the optimizer switching the execution plan order of tables.

What I notice is on the slow log explain looks like this examining 199043934 rows

# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  inventory_in_stock      ref     PRIMARY,index_sku_qty,index_is_salable,index_sku        index_is_salable        1       const   1       5822.00 100.00  100.00  Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  stock_status_index      ref     PRIMARY,index_sku_qty,index_is_salable,index_sku        index_is_salable        1       const   1       17093.62        100.00  100.00  Using index
# explain: 1    SIMPLE  e       ref     PRIMARY,CATALOG_PRODUCT_ENTITY_SKU      CATALOG_PRODUCT_ENTITY_SKU      195     db_edomainnlive.inventory_in_stock.sku  1       1.00    100.00  100.00  Using index condition
# explain: 1    SIMPLE  links   eq_ref  CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID   CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID   10      const,const,db_edomainnlive.e.entity_id 1       0.00    100.00  100.00  Using index
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60       PRIMARY 10      const,db_edomainnlive.e.entity_id,const 1       NULL    100.00   NULL    Using where
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE   PRIMARY 10      db_edomainnlive.e.entity_id,const,const 1NULL    100.00  NULL
# explain: 1    SIMPLE  product eq_ref  PRIMARY,CATALOG_PRODUCT_ENTITY_SKU      PRIMARY 4       db_edomainnlive.e.entity_id     1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  product_entity_table    eq_ref  PRIMARY PRIMARY 4       db_edomainnlive.links.product_id        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  link_attribute_position_int     eq_ref  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID  CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID     7       const,db_edomainnlive.links.link_id      1       NULL    100.00  NULL
# explain: 1    SIMPLE  link_attribute_qty_decimal      eq_ref  CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID      CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID     7const,db_edomainnlive.links.link_id     1       NULL    100.00  NULL

When I execute it the order differs

+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+
|id|select_type|table                      |type  |possible_keys                                                                                                                        |key                                                           |key_len|ref                                                |rows|filtered|Extra                                       |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+
|1 |SIMPLE     |links                      |ref   |CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID,CATALOG_PRODUCT_LINK_PRODUCT_ID,CATALOG_PRODUCT_LINK_LINKED_PRODUCT_ID|CATALOG_PRODUCT_LINK_LINK_TYPE_ID_PRODUCT_ID_LINKED_PRODUCT_ID|6      |const,const                                        |2   |100     |Using index; Using temporary; Using filesort|
|1 |SIMPLE     |cat_index                  |eq_ref|PRIMARY,IDX_4B965DC45C352D6E4C9DC0FF50B1FCF5,IDX_47AB760CD6A893ACEA69A9C2E0112C60                                                    |PRIMARY                                                       |10     |const,db_edomainnlive.links.linked_product_id,const|1   |100     |Using where                                 |
|1 |SIMPLE     |price_index                |eq_ref|PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE                                |PRIMARY                                                       |10     |db_edomainnlive.links.linked_product_id,const,const|1   |100     |                                            |
|1 |SIMPLE     |e                          |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_edomainnlive.links.linked_product_id            |1   |100     |Using where                                 |
|1 |SIMPLE     |inventory_in_stock         |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku                                                                                     |PRIMARY                                                       |194    |db_edomainnlive.e.sku                              |1   |100     |Using where                                 |
|1 |SIMPLE     |product                    |eq_ref|PRIMARY,CATALOG_PRODUCT_ENTITY_SKU                                                                                                   |PRIMARY                                                       |4      |db_edomainnlive.links.linked_product_id            |1   |100     |Using where                                 |
|1 |SIMPLE     |stock_status_index         |eq_ref|PRIMARY,index_sku_qty,index_is_salable,index_sku                                                                                     |PRIMARY                                                       |194    |db_edomainnlive.product.sku                        |1   |100     |Using where                                 |
|1 |SIMPLE     |product_entity_table       |eq_ref|PRIMARY                                                                                                                              |PRIMARY                                                       |4      |db_edomainnlive.links.product_id                   |1   |100     |Using index                                 |
|1 |SIMPLE     |link_attribute_position_int|eq_ref|CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_INT_LINK_ID                                               |CAT_PRD_LNK_ATTR_INT_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_edomainnlive.links.link_id                |1   |100     |                                            |
|1 |SIMPLE     |link_attribute_qty_decimal |eq_ref|CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID,CATALOG_PRODUCT_LINK_ATTRIBUTE_DECIMAL_LINK_ID                                           |CAT_PRD_LNK_ATTR_DEC_PRD_LNK_ATTR_ID_LNK_ID                   |7      |const,db_edomainnlive.links.link_id                |1   |100     |                                            |
+--+-----------+---------------------------+------+-------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------+-------+---------------------------------------------------+----+--------+--------------------------------------------+

This is the root causes of the issue. Will check it again tonight and reply.

[ioweb-gr]

Also my initial estimate about price indexer is wrong, these queries are coming from the Related / Upsell blocks.

I added a logger in Collection afterLoad() function and with a debug backtrace ended up to this

```json [ { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/module-eav/Model/Entity/Collection/AbstractCollection.php", "line": 943, "function": "_afterLoad", "class": "Magento\\Catalog\\Model\\ResourceModel\\Product\\Collection", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/module-catalog/Model/ResourceModel/Product/Collection.php", "line": 860, "function": "load", "class": "Magento\\Eav\\Model\\Entity\\Collection\\AbstractCollection", "object": {}, "type": "->", "args": [ false, false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 58, "function": "load", "class": "Magento\\Catalog\\Model\\ResourceModel\\Product\\Collection", "object": {}, "type": "->", "args": [ false, false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 138, "function": "___callParent", "class": "Magento\\Catalog\\Model\\ResourceModel\\Product\\Link\\Product\\Collection\\Interceptor", "object": {}, "type": "->", "args": [ "load", [ false, false ] ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 153, "function": "Magento\\Framework\\Interception\\{closure}", "class": "Magento\\Catalog\\Model\\ResourceModel\\Product\\Link\\Product\\Collection\\Interceptor", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/generated/code/Magento/Catalog/Model/ResourceModel/Product/Link/Product/Collection/Interceptor.php", "line": 23, "function": "___callPlugins", "class": "Magento\\Catalog\\Model\\ResourceModel\\Product\\Link\\Product\\Collection\\Interceptor", "object": {}, "type": "->", "args": [ "load", [], { "1": [ "Amasty_Rolepermissions::Product_Collection", "add_stock_information" ] } ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/module-catalog/Block/Product/ProductList/Upsell.php", "line": 113, "function": "load", "class": "Magento\\Catalog\\Model\\ResourceModel\\Product\\Link\\Product\\Collection\\Interceptor", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/module-catalog/Block/Product/ProductList/Upsell.php", "line": 137, "function": "_prepareData", "class": "Magento\\Catalog\\Block\\Product\\ProductList\\Upsell", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Element/AbstractBlock.php", "line": 1110, "function": "_beforeToHtml", "class": "Magento\\Catalog\\Block\\Product\\ProductList\\Upsell", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Element/AbstractBlock.php", "line": 1115, "function": "Magento\\Framework\\View\\Element\\{closure}", "class": "Magento\\Framework\\View\\Element\\AbstractBlock", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Element/AbstractBlock.php", "line": 675, "function": "_loadCache", "class": "Magento\\Framework\\View\\Element\\AbstractBlock", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 58, "function": "toHtml", "class": "Magento\\Framework\\View\\Element\\AbstractBlock", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 138, "function": "___callParent", "class": "Anowave\\Ec\\Preference\\Upsell\\Interceptor", "object": {}, "type": "->", "args": [ "toHtml", [] ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 153, "function": "Magento\\Framework\\Interception\\{closure}", "class": "Anowave\\Ec\\Preference\\Upsell\\Interceptor", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/generated/code/Anowave/Ec/Preference/Upsell/Interceptor.php", "line": 50, "function": "___callPlugins", "class": "Anowave\\Ec\\Preference\\Upsell\\Interceptor", "object": {}, "type": "->", "args": [ "toHtml", [], { "4": [ "returnEmptyStringWhenOutputEmpty", "ec" ] } ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 566, "function": "toHtml", "class": "Anowave\\Ec\\Preference\\Upsell\\Interceptor", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 542, "function": "_renderBlock", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "product.info.upsell" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 497, "function": "renderNonCachedElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "product.info.upsell" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 594, "function": "renderElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "product.info.upsell", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 544, "function": "_renderContainer", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "content", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 497, "function": "renderNonCachedElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "content" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 594, "function": "renderElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "content", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 544, "function": "_renderContainer", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "main", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 497, "function": "renderNonCachedElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "main" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 594, "function": "renderElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "main", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 544, "function": "_renderContainer", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "columns", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 497, "function": "renderNonCachedElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "columns" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 594, "function": "renderElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "columns", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 544, "function": "_renderContainer", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "main.content", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 497, "function": "renderNonCachedElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "main.content" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 594, "function": "renderElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "main.content", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 544, "function": "_renderContainer", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "page.wrapper", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 497, "function": "renderNonCachedElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "page.wrapper" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 594, "function": "renderElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "page.wrapper", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 544, "function": "_renderContainer", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "root", false ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 497, "function": "renderNonCachedElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "root" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Layout.php", "line": 963, "function": "renderElement", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [ "root" ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 58, "function": "getOutput", "class": "Magento\\Framework\\View\\Layout", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 138, "function": "___callParent", "class": "Magento\\Framework\\View\\Layout\\Interceptor", "object": {}, "type": "->", "args": [ "getOutput", [] ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 153, "function": "Magento\\Framework\\Interception\\{closure}", "class": "Magento\\Framework\\View\\Layout\\Interceptor", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/generated/code/Magento/Framework/View/Layout/Interceptor.php", "line": 41, "function": "___callPlugins", "class": "Magento\\Framework\\View\\Layout\\Interceptor", "object": {}, "type": "->", "args": [ "getOutput", [], { "4": [ "yireo_nextgenimages_replace_tags", "layout-model-caching-unique-name" ] } ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Result/Page.php", "line": 258, "function": "getOutput", "class": "Magento\\Framework\\View\\Layout\\Interceptor", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/View/Result/Layout.php", "line": 171, "function": "render", "class": "Magento\\Framework\\View\\Result\\Page", "object": {}, "type": "->", "args": [ {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 58, "function": "renderResult", "class": "Magento\\Framework\\View\\Result\\Layout", "object": {}, "type": "->", "args": [ {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 138, "function": "___callParent", "class": "Magento\\Framework\\View\\Result\\Page\\Interceptor", "object": {}, "type": "->", "args": [ "renderResult", [ {} ] ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/app/code/Ioweb/Html5/Plugin/Controller/ResultInterfacePlugin.php", "line": 34, "function": "Magento\\Framework\\Interception\\{closure}", "class": "Magento\\Framework\\View\\Result\\Page\\Interceptor", "object": {}, "type": "->", "args": [ {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 135, "function": "aroundRenderResult", "class": "Ioweb\\Html5\\Plugin\\Controller\\ResultInterfacePlugin", "object": {}, "type": "->", "args": [ {}, {}, {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/amasty/module-page-speed-tools/Plugin/ProcessPageResult.php", "line": 40, "function": "Magento\\Framework\\Interception\\{closure}", "class": "Magento\\Framework\\View\\Result\\Page\\Interceptor", "object": {}, "type": "->", "args": [ {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 135, "function": "aroundRenderResult", "class": "Amasty\\PageSpeedTools\\Plugin\\ProcessPageResult", "object": {}, "type": "->", "args": [ {}, {}, {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/app/code/Magezon/Core/Plugin/View/Result/Layout.php", "line": 24, "function": "Magento\\Framework\\Interception\\{closure}", "class": "Magento\\Framework\\View\\Result\\Page\\Interceptor", "object": {}, "type": "->", "args": [ {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 135, "function": "aroundRenderResult", "class": "Magezon\\Core\\Plugin\\View\\Result\\Layout", "object": {}, "type": "->", "args": [ {}, {}, {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/Interception/Interceptor.php", "line": 153, "function": "Magento\\Framework\\Interception\\{closure}", "class": "Magento\\Framework\\View\\Result\\Page\\Interceptor", "object": {}, "type": "->", "args": [ {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/generated/code/Magento/Framework/View/Result/Page/Interceptor.php", "line": 41, "function": "___callPlugins", "class": "Magento\\Framework\\View\\Result\\Page\\Interceptor", "object": {}, "type": "->", "args": [ "renderResult", [ {} ], null ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/App/Http.php", "line": 120, "function": "renderResult", "class": "Magento\\Framework\\View\\Result\\Page\\Interceptor", "object": {}, "type": "->", "args": [ {} ] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/generated/code/Magento/Framework/App/Http/Interceptor.php", "line": 23, "function": "launch", "class": "Magento\\Framework\\App\\Http", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/vendor/magento/framework/App/Bootstrap.php", "line": 263, "function": "launch", "class": "Magento\\Framework\\App\\Http\\Interceptor", "object": {}, "type": "->", "args": [] }, { "file": "/var/www/vhosts/e-domain.gr/httpdocs/index.php", "line": 39, "function": "run", "class": "Magento\\Framework\\App\\Bootstrap", "object": {}, "type": "->", "args": [ {} ] } ] ```

[ioweb-gr]

I've managed to get to the double join for inventory stock status

Here's the execution path that leads to the double join first of all

STEP 1: \Magento\Catalog\Block\Product\ProductList\Upsell::_prepareData

null collection

STEP 2: \Magento\Catalog\Model\Product::getUpSellProductCollection

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
WHERE (inventory_in_stock.is_salable = 1);

STEP 3: \Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection::setPositionOrder

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
WHERE (inventory_in_stock.is_salable = 1)
ORDER BY `position` ASC

STEP 4: \Magento\Catalog\Model\ResourceModel\Product\Collection::addStoreFilter

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
         INNER JOIN `catalog_product_website` AS `product_website`
                    ON product_website.product_id = e.entity_id AND product_website.website_id = 2
WHERE (inventory_in_stock.is_salable = 1)
ORDER BY `position` ASC

STEP 5: \Magento\Catalog\Block\Product\AbstractProduct::_addProductAttributesAndPrices

SELECT `e`.*,
       `price_index`.`price`,
       `price_index`.`tax_class_id`,
       `price_index`.`final_price`,
       IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price),
          price_index.min_price) AS `minimal_price`,
       `price_index`.`min_price`,
       `price_index`.`max_price`,
       `price_index`.`tier_price`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
         INNER JOIN `catalog_product_website` AS `product_website`
                    ON product_website.product_id = e.entity_id AND product_website.website_id = 2
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND
                       price_index.website_id = '2'
WHERE (inventory_in_stock.is_salable = 1)
ORDER BY `position` ASC

STEP 6: \Magento\CatalogInventory\Helper\Stock::addIsInStockFilterToCollection

SELECT `e`.*,
       `price_index`.`price`,
       `price_index`.`tax_class_id`,
       `price_index`.`final_price`,
       IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price),
          price_index.min_price) AS `minimal_price`,
       `price_index`.`min_price`,
       `price_index`.`max_price`,
       `price_index`.`tier_price`,
       `cat_index`.`position`    AS `cat_index_position`,
       `stock_status_index`.`is_salable`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND
                       price_index.website_id = '2'
         INNER JOIN `catalog_category_product_index_store5` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = 5 AND
                       cat_index.visibility IN (2, 4) AND cat_index.category_id = 2
         INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id
         INNER JOIN `inventory_stock_5` AS `stock_status_index` ON product.sku = stock_status_index.sku
WHERE (inventory_in_stock.is_salable = 1)
  AND (stock_status_index.is_salable = 1)
ORDER BY `position` ASC

On step 2 the flag is not set for the stock status filter so it's joined twice.

[ioweb-gr]

Broken down further it's these two culprits for the double join

\Magento\CatalogInventory\Model\Plugin\ProductLinks::afterGetProductCollection leads to

SELECT `e`.*
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
WHERE (inventory_in_stock.is_salable = 1)

\Magento\CatalogInventory\Helper\Stock::addIsInStockFilterToCollection leads to

SELECT `e`.*,
       `price_index`.`price`,
       `price_index`.`tax_class_id`,
       `price_index`.`final_price`,
       IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price),
          price_index.min_price) AS `minimal_price`,
       `price_index`.`min_price`,
       `price_index`.`max_price`,
       `price_index`.`tier_price`,
       `cat_index`.`position`    AS `cat_index_position`,
       `stock_status_index`.`is_salable`
FROM `catalog_product_entity` AS `e`
         INNER JOIN `inventory_stock_5` AS `inventory_in_stock` ON e.sku = inventory_in_stock.sku
         INNER JOIN `catalog_product_index_price` AS `price_index`
                    ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND
                       price_index.website_id = '2'
         INNER JOIN `catalog_category_product_index_store5` AS `cat_index`
                    ON cat_index.product_id = e.entity_id AND cat_index.store_id = 5 AND
                       cat_index.visibility IN (2, 4) AND cat_index.category_id = 2
         INNER JOIN `catalog_product_entity` AS `product` ON product.entity_id = e.entity_id
         INNER JOIN `inventory_stock_5` AS `stock_status_index` ON product.sku = stock_status_index.sku
WHERE (inventory_in_stock.is_salable = 1)
  AND (stock_status_index.is_salable = 1)
ORDER BY `position` ASC

Because the first one doesn't set the has_stock_status_filter flag. There's no need for the double join

[ioweb-gr]

It seems that those two functions are written in the following helper

\Magento\CatalogInventory\Helper\Stock

    /**
     * Adds filtering for collection to return only in stock products
     *
     * @param \Magento\Catalog\Model\ResourceModel\Product\Link\Product\Collection $collection
     * @return void
     */
    public function addInStockFilterToCollection($collection)
    {
        $manageStock = $this->scopeConfig->getValue(
            \Magento\CatalogInventory\Model\Configuration::XML_PATH_MANAGE_STOCK,
            \Magento\Store\Model\ScopeInterface::SCOPE_STORE
        );
        $cond = [
            '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=1 AND {{table}}.is_in_stock=1',
            '{{table}}.use_config_manage_stock = 0 AND {{table}}.manage_stock=0'
        ];

        if ($manageStock) {
            $cond[] = '{{table}}.use_config_manage_stock = 1 AND {{table}}.is_in_stock=1';
        } else {
            $cond[] = '{{table}}.use_config_manage_stock = 1';
        }

        $collection->joinField(
            'inventory_in_stock',
            'cataloginventory_stock_item',
            'is_in_stock',
            'product_id=entity_id',
            '(' . join(') OR (', $cond) . ')'
        );
    }

    /**
     * Add only is in stock products filter to product collection
     *
     * @param \Magento\Catalog\Model\ResourceModel\Product\Collection $collection
     * @return void
     */
    public function addIsInStockFilterToCollection($collection)
    {
        $stockFlag = 'has_stock_status_filter';
        if (!$collection->hasFlag($stockFlag)) {
            $isShowOutOfStock = $this->scopeConfig->getValue(
                \Magento\CatalogInventory\Model\Configuration::XML_PATH_SHOW_OUT_OF_STOCK,
                \Magento\Store\Model\ScopeInterface::SCOPE_STORE
            );
            $resource = $this->getStockStatusResource();
            $resource->addStockDataToCollection(
                $collection,
                !$isShowOutOfStock
            );
            $collection->setFlag($stockFlag, true);
        }
    }

The second is modified by a plugin

\Magento\InventoryCatalog\Plugin\CatalogInventory\Model\ResourceModel\Stock\Status\AdaptAddIsInStockFilterToCollectionPlugin::aroundAddIsInStockFilterToCollection

\Magento\InventoryCatalog\Model\ResourceModel\AddIsInStockFilterToCollection::execute

To give pretty much the same result, but doesn't prevent double joining the table due to the missing flag.

If they're doing the same thing, why aren't they unified?

[ioweb-gr]

I've added this optimization to the code, and will check again if the situation improves

Index: vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
--- a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php 
+++ b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php (date 1695246381224)
@@ -44,7 +44,7 @@
     public function afterGetProductCollection(Link $subject, Collection $collection)
     {
         if ($this->configuration->isShowOutOfStock() != 1) {
-            $this->stockHelper->addInStockFilterToCollection($collection);
+            $this->stockHelper->addIsInStockFilterToCollection($collection);
         }
         return $collection;
     }

It will utilize the same function for filtering in stock products, thus add the flag and remove the second useless join on inventorystock# table

[ioweb-gr]

I've added this optimization to the code, and will check again if the situation improves

Index: vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
--- a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php   
+++ b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php   (date 1695246381224)
@@ -44,7 +44,7 @@
     public function afterGetProductCollection(Link $subject, Collection $collection)
     {
         if ($this->configuration->isShowOutOfStock() != 1) {
-            $this->stockHelper->addInStockFilterToCollection($collection);
+            $this->stockHelper->addIsInStockFilterToCollection($collection);
         }
         return $collection;
     }

It will utilize the same function for filtering in stock products, thus add the flag and remove the second useless join on inventorystock# table

Running steady for 24 hours now and no occurrences of the slow query, this looks like it might be the culprit.

[ioweb-gr]

Second day the query didn't appear in the slow log. I think it's safe to say that the issue is in two areas

Performance of Upsell / Related / Cross-sells queries

1. Missing indexes for is_salable and sku column in the inventory_stock_# tables, causing the query to do full table scans
2. addInStockFilterToCollection not checking whether the is_salable condition is already added and the table inventory_stock_# is joined already
3. The plugin vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php causing the double join on the inventory_stock_# tables
4. The order of the joins
5. All of the above cause the database optimizer to choose different query plan alltogether leading to the wrong plan for execution forcing billions of rows to be examined.

Architectural flaw

The root cause of the downtime is the architectural flaw of renaming the index tables without calculating that long running queries will prevent the rename table action from taking place while it still tries to get an exclusive lock on the table.

@engcom-Dash since you were initially assigned to the task, could you re-examine it now after all information is here?

Steps to simulate the issue

1. Run a long running select query on the price index table (make it run for a long period).
2. Run a full reindex of prices indexer
3. Use systems that hold a single database node and do not do read-write splitting
4. Try to access the website while the rename tables action is in place and watch magento go down

For actual replication, you'd need a high traffic live environment with cross-sells / upsells and related products spamming queries all over the database and reindex to hit while those queries don't run optimally on any mariadb version 10.4 -> 10.6

[ioweb-gr]

2 weeks later, 0 downtimes after applying the patch. 0 records of the query in the slow log.

No noticeable issues.

I think I was spot on with this one. Can the engcom team please acknowledge this issue on both accounts?

1. Arcitectural flaw in the RENAME_TABLE procedure of index tables, blocks all other queries
2. Duplicate joins of IsInStockFilter can make the MariaDB optimizer to cause infinite blocking on the RENAME TABLE queries coming from Crosssell / Upsell and Related products blocks depending on the plan to execute the joins

Please this would be vital, so that we can brainstorm and get suggestions for possible solutions to this if possible.

cc @engcom-Dash @engcom-Alfa @engcom-Bravo @engcom-Charlie @engcom-Delta

[engcom-Hotel]

Hello @ioweb-gr,

Thanks for the detailed explanation here in this comment https://github.com/magento/magento2/issues/36667#issuecomment-1731315222.

We are trying to reproduce this issue meanwhile can you please elaborate on the below point:

1. Run a long running select query on the price index table (make it run for a long period).

Thanks

[ioweb-gr]

Hello @ioweb-gr,

Thanks for the detailed explanation here in this comment https://github.com/magento/magento2/issues/36667#issuecomment-1731315222.

We are trying to reproduce this issue meanwhile can you please elaborate on the below point:

1. Run a long running select query on the price index table (make it run for a long period).

Thanks

As explained this main issue is coming when a select query is executing that uses the price index table that is normally renamed by the indexer. If the rename action occurs while the select query is running, Magento will drop

In my case the queries from related, crosssells and upsells took ages to finish due to improper joins so they were the culprits but also other queries like product export queries can block it as well as they run for a prolonged period

[Adel-Magebinary]

@engcom-Hotel Try inserting 2m products with 10 MSI sources with upsell and related products as dummy data. Then, run an export from the command line. Then go to catalogue price rule and create a rule with a 20% discount. You should see the freeze. Make sure cron is changed to schedule.

This is a significant performance and stability issue for large sites. It should be looked into urgently. I'm sure most of the large sites are experiencing this issue and getting random downtime. They might not be able to look as deep as @ioweb-gr did. But this is definitely should be on the top of the top of the backlog P0.

[mtytula]

I've added this optimization to the code, and will check again if the situation improves

Index: vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
--- a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php 
+++ b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php (date 1695246381224)
@@ -44,7 +44,7 @@
     public function afterGetProductCollection(Link $subject, Collection $collection)
     {
         if ($this->configuration->isShowOutOfStock() != 1) {
-            $this->stockHelper->addInStockFilterToCollection($collection);
+            $this->stockHelper->addIsInStockFilterToCollection($collection);
         }
         return $collection;
     }

It will utilize the same function for filtering in stock products, thus add the flag and remove the second useless join on inventorystock# table

Running steady for 24 hours now and no occurrences of the slow query, this looks like it might be the culprit.

I can also confirm that this patch worked for me on Open Source 2.4.5-p4 with ~100k products with some catalog price rules.

[engcom-Hotel]

Hello @Adel-Magebinary,

Thanks for the steps!

We have tried to reproduce the issue with the vanilla 2.4-develop instance with the MSI package installed. But still the issue is not reproducible for us.

Please have a look at the below screenshot for reference:

Product Page

Cron setup on schedule mode

Catalog Price Rule

The only difference is, that we have started the export from the admin panel but not from CLI, can you please let us know which module you have used to run the export from CLI?

Thanks

[ioweb-gr]

@engcom-Hotel it's not so easy to reproduce the issue. Keep in mind we were seeing this happen 4-5 times / day on a moderate traffic website where the mariadb optimizer would be confused by the query and choosing randomly a different plan.

The underlying issue that the RENAME table queries break the website is reproducible by any type of select that needs to run for a long time

e.g. here's a sample query,

__SELECT__ `e`.*,

IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`,

IF(at_visibility.value_id > 0, at_visibility.value,

at_visibility_default.value) AS `visibility`,

`at_brand_logo`.`value` AS `brand_logo`,

IF(at_image.value_id > 0, at_image.value, at_image_default.value) AS `image`,

`at_manufacturer`.`value` AS `manufacturer`,

IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`,

IF(at_season.value_id > 0, at_season.value, at_season_default.value) AS `season`,

`at_sex`.`value` AS `sex`,

IF(at_short_description.value_id > 0, at_short_description.value,

at_short_description_default.value) AS `short_description`,

IF(at_small_image.value_id > 0, at_small_image.value,

at_small_image_default.value) AS `small_image`,

IF(at_thumbnail.value_id > 0, at_thumbnail.value,

at_thumbnail_default.value) AS `thumbnail`,

IF(at_url_key.value_id > 0, at_url_key.value,

at_url_key_default.value) AS `url_key`,

IF(at_special_price.value_id > 0, at_special_price.value,

at_special_price_default.value) AS `special_price`,

IF(at_special_from_date.value_id > 0, at_special_from_date.value,

at_special_from_date_default.value) AS `special_from_date`,

IF(at_special_to_date.value_id > 0, at_special_to_date.value,

at_special_to_date_default.value) AS `special_to_date`,

`at_price_type`.`value` AS `price_type`,

IF(at_price.value_id > 0, at_price.value, at_price_default.value) AS `price`,

`at_tax_class_id`.`value` AS `tax_class_id`,

`stock`.`qty`,

`stock`.`is_in_stock`,

`stock`.`manage_stock`,

`stock`.`use_config_manage_stock`,

`stock`.`backorders`,

`stock`.`use_config_backorders`,

MAX(DISTINCT request_path) AS `request_path`,

`curpc`.*,

`cpsl`.`parent_id`,

GROUP_CONCAT(DISTINCT categories.category_id) AS `categories_ids`,

`price_index`.`min_price`,

`price_index`.`max_price`,

`price_index`.`final_price`,

`price_index`.`price` AS `base_price`,

COUNT(DISTINCT e.entity_id) AS `total`

FROM `catalog_product_entity` AS `e`

INNER JOIN `catalog_product_website` AS `product_website`

ON product_website.product_id = e.entity_id AND product_website.website_id = 2

INNER JOIN `catalog_product_entity_int` AS `at_status_default`

ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND

(`at_status_default`.`attribute_id` = 84) AND `at_status_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_int` AS `at_status`

ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = 84) AND

(`at_status`.`store_id` = 5)

INNER JOIN `catalog_product_entity_int` AS `at_visibility_default`

ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND

(`at_visibility_default`.`attribute_id` = 91) AND `at_visibility_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_int` AS `at_visibility`

ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = 91) AND

(`at_visibility`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_int` AS `at_brand_logo`

ON (`at_brand_logo`.`entity_id` = `e`.`entity_id`) AND (`at_brand_logo`.`attribute_id` = 273) AND

(`at_brand_logo`.`store_id` = 0)

LEFT JOIN `catalog_product_entity_varchar` AS `at_image_default`

ON (`at_image_default`.`entity_id` = `e`.`entity_id`) AND

(`at_image_default`.`attribute_id` = 74) AND `at_image_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_varchar` AS `at_image`

ON (`at_image`.`entity_id` = `e`.`entity_id`) AND (`at_image`.`attribute_id` = 74) AND

(`at_image`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_int` AS `at_manufacturer`

ON (`at_manufacturer`.`entity_id` = `e`.`entity_id`) AND (`at_manufacturer`.`attribute_id` = 70) AND

(`at_manufacturer`.`store_id` = 0)

LEFT JOIN `catalog_product_entity_varchar` AS `at_name_default`

ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = 60) AND

`at_name_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_varchar` AS `at_name`

ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = 60) AND

(`at_name`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_varchar` AS `at_season_default`

ON (`at_season_default`.`entity_id` = `e`.`entity_id`) AND

(`at_season_default`.`attribute_id` = 319) AND `at_season_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_varchar` AS `at_season`

ON (`at_season`.`entity_id` = `e`.`entity_id`) AND (`at_season`.`attribute_id` = 319) AND

(`at_season`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_int` AS `at_sex`

ON (`at_sex`.`entity_id` = `e`.`entity_id`) AND (`at_sex`.`attribute_id` = 343) AND

(`at_sex`.`store_id` = 0)

LEFT JOIN `catalog_product_entity_text` AS `at_short_description_default`

ON (`at_short_description_default`.`entity_id` = `e`.`entity_id`) AND

(`at_short_description_default`.`attribute_id` = 62) AND

`at_short_description_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_text` AS `at_short_description`

ON (`at_short_description`.`entity_id` = `e`.`entity_id`) AND

(`at_short_description`.`attribute_id` = 62) AND (`at_short_description`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_varchar` AS `at_small_image_default`

ON (`at_small_image_default`.`entity_id` = `e`.`entity_id`) AND

(`at_small_image_default`.`attribute_id` = 75) AND `at_small_image_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_varchar` AS `at_small_image`

ON (`at_small_image`.`entity_id` = `e`.`entity_id`) AND (`at_small_image`.`attribute_id` = 75) AND

(`at_small_image`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_varchar` AS `at_thumbnail_default`

ON (`at_thumbnail_default`.`entity_id` = `e`.`entity_id`) AND

(`at_thumbnail_default`.`attribute_id` = 76) AND `at_thumbnail_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_varchar` AS `at_thumbnail`

ON (`at_thumbnail`.`entity_id` = `e`.`entity_id`) AND (`at_thumbnail`.`attribute_id` = 76) AND

(`at_thumbnail`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_varchar` AS `at_url_key_default`

ON (`at_url_key_default`.`entity_id` = `e`.`entity_id`) AND

(`at_url_key_default`.`attribute_id` = 86) AND `at_url_key_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_varchar` AS `at_url_key`

ON (`at_url_key`.`entity_id` = `e`.`entity_id`) AND (`at_url_key`.`attribute_id` = 86) AND

(`at_url_key`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_decimal` AS `at_special_price_default`

ON (`at_special_price_default`.`entity_id` = `e`.`entity_id`) AND

(`at_special_price_default`.`attribute_id` = 65) AND `at_special_price_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_decimal` AS `at_special_price`

ON (`at_special_price`.`entity_id` = `e`.`entity_id`) AND

(`at_special_price`.`attribute_id` = 65) AND (`at_special_price`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_datetime` AS `at_special_from_date_default`

ON (`at_special_from_date_default`.`entity_id` = `e`.`entity_id`) AND

(`at_special_from_date_default`.`attribute_id` = 66) AND

`at_special_from_date_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_datetime` AS `at_special_from_date`

ON (`at_special_from_date`.`entity_id` = `e`.`entity_id`) AND

(`at_special_from_date`.`attribute_id` = 66) AND (`at_special_from_date`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_datetime` AS `at_special_to_date_default`

ON (`at_special_to_date_default`.`entity_id` = `e`.`entity_id`) AND

(`at_special_to_date_default`.`attribute_id` = 67) AND `at_special_to_date_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_datetime` AS `at_special_to_date`

ON (`at_special_to_date`.`entity_id` = `e`.`entity_id`) AND

(`at_special_to_date`.`attribute_id` = 67) AND (`at_special_to_date`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_int` AS `at_price_type`

ON (`at_price_type`.`entity_id` = `e`.`entity_id`) AND (`at_price_type`.`attribute_id` = 109) AND

(`at_price_type`.`store_id` = 0)

LEFT JOIN `catalog_product_entity_decimal` AS `at_price_default`

ON (`at_price_default`.`entity_id` = `e`.`entity_id`) AND

(`at_price_default`.`attribute_id` = 64) AND `at_price_default`.`store_id` = 0

LEFT JOIN `catalog_product_entity_decimal` AS `at_price`

ON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = 64) AND

(`at_price`.`store_id` = 5)

LEFT JOIN `catalog_product_entity_int` AS `at_tax_class_id`

ON (`at_tax_class_id`.`entity_id` = `e`.`entity_id`) AND (`at_tax_class_id`.`attribute_id` = 85) AND

(`at_tax_class_id`.`store_id` = 0)

LEFT JOIN `cataloginventory_stock_item` AS `stock` ON stock.product_id = e.entity_id

LEFT JOIN `url_rewrite` AS `url` ON url.entity_id = e.entity_id AND url.target_path NOT LIKE '%category%' and

is_autogenerated = '1' AND url.entity_type = 'product' AND url.store_id = 5

LEFT JOIN `catalog_url_rewrite_product_category` AS `curpc` ON url.url_rewrite_id = curpc.url_rewrite_id

LEFT JOIN `catalog_product_super_link` AS `cpsl` ON cpsl.product_id = e.entity_id

LEFT JOIN `catalog_product_entity` AS `cpslcpe` ON cpsl.parent_id = cpslcpe.entity_id

INNER JOIN `catalog_category_product` AS `categories`

ON (categories.product_id = e.entity_id OR categories.product_id = cpslcpe.entity_id) AND

categories.category_id IN

(4, 5, 6, 8, 10, 11, 21, 29, 30, 34, 35, 43, 44, 48, 92, 93, 95, 96, 116, 156, 157, 159, 160,

161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 190, 191, 193, 194, 195, 196, 197, 198, 208,

209, 210, 213, 214, 216, 217, 237, 240, 254, 261, 262, 269, 272, 275, 284, 290, 293, 294, 295,

298, 299, 317, 318, 321, 326, 376, 377, 378, 379, 380, 381, 382, 383, 384, 385, 386, 387, 388,

389, 390, 391, 392, 393, 394, 511, 512, 513, 514, 524, 525, 534, 540, 541, 542, 543, 544, 551,

556, 557, 683, 710, 761, 770, 772, 774, 775, 776, 777, 778, 780, 781, 782, 784, 830, 839, 854,

889, 901, 981, 982, 1032, 1033, 1034, 1035, 1036, 1055, 1056, 1058, 1060, 1127, 1128, 1129,

1130, 1131, 1132, 1133, 1134, 1135, 1136, 1137, 1138, 1139, 1140, 1141, 1142, 1143, 1144, 1145,

1167, 1177)

INNER JOIN `catalog_category_entity` AS `cce`

ON categories.category_id = cce.entity_id AND cce.path LIKE '1/2/%'

LEFT JOIN `catalog_product_index_price` AS `price_index`

ON price_index.entity_id = e.entity_id AND customer_group_id = 0 AND price_index.website_id = 2

WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')

AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) IN ('4'))

AND ((IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1'))

LIMIT 1

You may need to create the mentioned attributes / categories etc and populate them with lots of sample data.

If you have enough records in the price index table due to products and catalog rules and customer groups, this query should take more than 10 minutes to finish.

Then during that time, try to reindex prices so that the rename operation on the price index table hits while the select is still running.

Moreover, it has to be such a large dataset that the select query will be stuck in the sending data phase where it cannot release the lock for the RENAME operation because it's actively fetching the data.

Then you will see the RENAME operation take long time to finish and finally the site will drop.

Similar to my early image

[Adel-Magebinary]

I think you are missing multiple stock sources & stock assigned to the products. @engcom-Hotel

Please also assign related/upsell products, then trigger the index with the catalog price rule.

[github-jira-sync-bot]

:white_check_mark: Jira issue https://jira.corp.adobe.com/browse/AC-10991 is successfully created for this GitHub issue.

[m2-assistant[bot]]

:white_check_mark: Confirmed by @engcom-Hotel. Thank you for verifying the issue.
Issue Available: @engcom-Hotel, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[lewster32]

I've added this optimization to the code, and will check again if the situation improves

Index: vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php
--- a/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php   
+++ b/vendor/magento/module-catalog-inventory/Model/Plugin/ProductLinks.php   (date 1695246381224)
@@ -44,7 +44,7 @@
     public function afterGetProductCollection(Link $subject, Collection $collection)
     {
         if ($this->configuration->isShowOutOfStock() != 1) {
-            $this->stockHelper->addInStockFilterToCollection($collection);
+            $this->stockHelper->addIsInStockFilterToCollection($collection);
         }
         return $collection;
     }

It will utilize the same function for filtering in stock products, thus add the flag and remove the second useless join on inventorystock# table

I can confirm this appears to have fixed an issue we were having with frequent lock-ups, Magento CE 2.4.6-p2 on PHP 8.1.