MySql suddenly started to choose different index for query, which causes it to be slow

Question

Database is from Magento 2's site. 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.

What could be causing this issue?

Query is automatically created by Magento. 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

score 1 · Answer 1 · answered Feb 25 '22 at 17:17

Give these a try:

at_status:  INDEX(entity_id,  value_id, value, attribute_id, store_id)
at_status_default:  INDEX(entity_id,  value, attribute_id, store_id)
at_visibility:  INDEX(entity_id,  value_id, value, attribute_id, store_id)
at_visibility_default:  INDEX(entity_id,  value, attribute_id, store_id)
e:  INDEX(created_at, entity_id)
product_website:  INDEX(product_id,  website_id)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

That looks like an EAV schema; such is notoriously difficult to optimize, even when the Optimizer is picking a less-optimal path.

MySql suddenly started to choose different index for query, which causes it to be slow

1 Answers1