0

Issue suddenly started to occur on our live site, dev site has the same database, but just with older data and same query is running completely fine there. I've also tried dumping those three tables from dev and imported them to live dump, but the issue persists.

I've found out with EXPLAIN that the query suddenly stopped using index for catalog_product_website table, there are two possible keys to use and it suddenly selects none of them. This is from the live database, which is problematic.

+------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------+
| id   | select_type | table                 | type   | possible_keys                                                                                                                                                                                 | key                                                        | key_len | ref                              | rows  | Extra                                                        |
+------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------+
|    1 | SIMPLE      | e                     | ALL    | PRIMARY                                                                                                                                                                                       | NULL                                                       | NULL    | NULL                             | 97344 | Using where; Using temporary; Using filesort                 |
|    1 | SIMPLE      | at_status             | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | magento2.e.entity_id,const,const | 1     |                                                              |
|    1 | SIMPLE      | at_visibility         | ref    | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE     | 4       | const,const                      | 1     | Using where                                                  |
|    1 | SIMPLE      | product_website       | range  | PRIMARY,CATALOG_PRODUCT_WEBSITE_WEBSITE_ID                                                                                                                                                    | CATALOG_PRODUCT_WEBSITE_WEBSITE_ID                         | 2       | NULL                             | 97960 | Using where; Using index; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | at_status_default     | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | magento2.e.entity_id,const,const | 1     |                                                              |
|    1 | SIMPLE      | at_visibility_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | magento2.e.entity_id,const,const | 1     |                                                              |
+------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+----------------------------------+-------+--------------------------------------------------------------+

And this if from the dev database, which runs that same query just fine, like live one did before:

+------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+---------------------------------+-------+-----------------------------+
| id   | select_type | table                 | type   | possible_keys                                                                                                                                                                                 | key                                                        | key_len | ref                             | rows  | Extra                       |
+------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+---------------------------------+-------+-----------------------------+
|    1 | SIMPLE      | e                     | ALL    | PRIMARY                                                                                                                                                                                       | NULL                                                       | NULL    | NULL                            | 94528 | Using where; Using filesort |
|    1 | SIMPLE      | at_status_default     | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | magento2.e.entity_id,const,const | 1     |                             |
|    1 | SIMPLE      | at_status             | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | magento2.e.entity_id,const,const | 1     |                             |
|    1 | SIMPLE      | at_visibility         | ref    | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE     | 4       | const,const                     | 1     | Using where                 |
|    1 | SIMPLE      | at_visibility_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | magento2.e.entity_id,const,const | 1     |                             |
|    1 | SIMPLE      | product_website       | eq_ref | PRIMARY,CATALOG_PRODUCT_WEBSITE_WEBSITE_ID                                                                                                                                                    | PRIMARY                                                    | 6       | magento2.e.entity_id,const       | 1     | Using index                 |
+------+-------------+-----------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+---------------------------------+-------+-----------------------------+

I've tried ANALYZE and OPTIMIZE on all of those tables, but unfortunately no help from there. Haven't found any structural changes for those tables, everything seems to be same.

Has anyone had anything similar happen? What could be causing this?

This particular query is not the only problematic one. Other ones that use those three tables and same logic seem to suffer from the same issue. 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` 
FROM
   `catalog_product_entity` AS `e` 
   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` = '97'
      )
      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` = '97'
      )
      AND 
      (
         `at_status`.`store_id` = 2
      )
   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` = '99'
      )
      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` = '99'
      )
      AND 
      (
         `at_visibility`.`store_id` = 2
      )
   INNER JOIN
      `catalog_product_website` AS `product_website` 
      ON product_website.product_id = e.entity_id 
      AND product_website.website_id IN
      (
         2
      )
WHERE
   (
(`e`.`created_at` > '2021-01-01 00:00:00')
   )
ORDER BY
   `e`.`created_at` ASC LIMIT 50
1
  • I've found out that removing the CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE index makes problematic queries, where these three tables are present, work fine again. So far I haven't found any side effects caused by the removal of that index. Does anyone have any idea, what that index is used for and why the removal of it seems to fix this issue?
    – V.V
    Mar 10 at 10:10

1 Answer 1

0

Because MySQL has chosen the wrong index.

You can visit this article about how MySQL selects the wrong index. Here is the link below:

https://blog.actorsfit.com/a?ID=01300-33e9ffff-ebbb-426a-8bc1-54792f144e6a

But this article is plagiarized from someone else, and the original link is https://time.geekbang.org/column/article/71173, I hope this may solve ur problem!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.