Product query is suddenly really slow, because wrong index is being selected in MySql

Question

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

Answer 1

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!