Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-27381

Wrong indexes used by query optimizer

    XMLWordPrintable

    Details

      Description

      Composite unique indexes aren't used when they are available. A query from real world application
      (Magento 2.4.3) takes almost 2 minutes vs 0 seconds when correct indexes are used.
      A database dump with data is attached.

      Sample query below should use CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID key for at_price table but it doesn't.

      Results from MariaDB 10.4.22 on CentOS 7. I didn't test it with newer versions. MariaDB 10.4 is recommended for Magento 2.4.3:
      https://devdocs.magento.com/guides/v2.4/install-gde/system-requirements.html

      EXPLAIN EXTENDED SELECT at_tax_class_id.value_id, at_price.value_id
      FROM `catalog_product_entity` AS `e`
      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` = 115) AND (`at_tax_class_id`.`store_id` = 1)
      LEFT JOIN `catalog_product_entity_decimal` AS `at_price` ON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = 69) AND (`at_price`.`store_id` = 0)
      LIMIT 20;
      

      Results from 10.4.22-MariaDB on CentOS 7:

      +------+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------+-------+----------+-------------+
      | id   | select_type | table           | type   | possible_keys                                                                                                                                                                                 | key                                                        | key_len | ref                          | rows  | filtered | Extra       |
      +------+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------+-------+----------+-------------+
      |    1 | SIMPLE      | e               | index  | NULL                                                                                                                                                                                          | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID                    | 2       | NULL                         | 11887 |   100.00 | Using index |
      |    1 | SIMPLE      | at_tax_class_id | 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       | aaa2.e.entity_id,const,const | 1     |     0.02 | Using index |
      |    1 | SIMPLE      | at_price        | ref    | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                            | CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                | 2       | const                        | 12108 |    50.00 | Using where |
      +------+-------------+-----------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+------------------------------+-------+----------+-------------+
      

      Real world query from Magento 2. Sample database with data attached:

      EXPLAIN EXTENDED
      SELECT `e`.*, IF(at_tax_class_id.value_id > 0, at_tax_class_id.value, at_tax_class_id_default.value) AS `tax_class_id`,
      `at_special_price`.`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`.`value` AS `price`,
      `at_cost`.`value` AS `cost`,
      IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`
      FROM `catalog_product_entity` AS `e`
       LEFT JOIN `catalog_product_entity_int` AS `at_tax_class_id_default` ON (`at_tax_class_id_default`.`entity_id` = `e`.`entity_id`) AND (`at_tax_class_id_default`.`attribute_id` = '115') AND `at_tax_class_id_default`.`store_id` = 0
       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` = '115') AND (`at_tax_class_id`.`store_id` = 1)
       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` = '70') AND (`at_special_price`.`store_id` = 0)
       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` = '71') 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` = '71') AND (`at_special_from_date`.`store_id` = 1)
       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` = '72') 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` = '72') AND (`at_special_to_date`.`store_id` = 1)
       LEFT JOIN `catalog_product_entity_decimal` AS `at_price` ON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = '69') AND (`at_price`.`store_id` = 0)
       LEFT JOIN `catalog_product_entity_decimal` AS `at_cost` ON (`at_cost`.`entity_id` = `e`.`entity_id`) AND (`at_cost`.`attribute_id` = '73') AND (`at_cost`.`store_id` = 0)
       LEFT JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '95') 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` = '95') AND (`at_visibility`.`store_id` = 1)
       ORDER BY e.entity_id ASC
       LIMIT 20;
      

      Rersults and execution time of the query from MariaDB 10.4.22 on CentOS 7. Wrong indexes used:

      +------+-------------+------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+------------------------------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
      | id   | select_type | table                        | type        | possible_keys                                                                                                                                                                                 | key                                                                         | key_len | ref                          | rows  | filtered | Extra                                                                                                                                         |
      +------+-------------+------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+------------------------------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
      |    1 | SIMPLE      | e                            | ALL         | NULL                                                                                                                                                                                          | NULL                                                                        | NULL    | NULL                         | 11887 |   100.00 | Using temporary; Using filesort                                                                                                               |
      |    1 | SIMPLE      | at_tax_class_id_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       | aaa2.e.entity_id,const,const | 1     |     5.41 |                                                                                                                                               |
      |    1 | SIMPLE      | at_tax_class_id              | 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       | aaa2.e.entity_id,const,const | 1     |     0.02 |                                                                                                                                               |
      |    1 | SIMPLE      | at_special_price             | ref         | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                            | CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                 | 2       | const                        | 9779  |    50.00 | Using where                                                                                                                                   |
      |    1 | SIMPLE      | at_special_from_date_default | eq_ref      | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID             | 8       | aaa2.e.entity_id,const,const | 1     |   100.00 |                                                                                                                                               |
      |    1 | SIMPLE      | at_special_from_date         | ref         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                    | 2       | const                        | 1     |    16.15 | Using where                                                                                                                                   |
      |    1 | SIMPLE      | at_special_to_date_default   | eq_ref      | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID             | 8       | aaa2.e.entity_id,const,const | 1     |   100.00 |                                                                                                                                               |
      |    1 | SIMPLE      | at_special_to_date           | ref         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                    | 2       | const                        | 1     |    28.18 | Using where                                                                                                                                   |
      |    1 | SIMPLE      | at_price                     | eq_ref      | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                            | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID              | 8       | aaa2.e.entity_id,const,const | 1     |   100.00 |                                                                                                                                               |
      |    1 | SIMPLE      | at_cost                      | eq_ref      | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                            | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID              | 8       | aaa2.e.entity_id,const,const | 1     |   100.00 |                                                                                                                                               |
      |    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       | aaa2.e.entity_id,const,const | 1     |     4.88 |                                                                                                                                               |
      |    1 | SIMPLE      | at_visibility                | index_merge | 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_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID | 2,2     | NULL                         | 27    |   100.00 | Using intersect(CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID); Using where; Using join buffer (flat, BNL join) |
      +------+-------------+------------------------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+------------------------------+-------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------+
      

      Execution time when the query runs:

      20 rows in set (1 min 52.189 sec)
      

      Compared to rersults and execution time of the same query using the same data from 10.2.36-MariaDB SUSE package on openSUSE Leap 15.1 where correct indexes are used:

      +------+-------------+------------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------------+-------+----------+-------+
      | id   | select_type | table                        | type   | possible_keys                                                                                                                                                                                 | key                                                             | key_len | ref                          | rows  | filtered | Extra |
      +------+-------------+------------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------------+-------+----------+-------+
      |    1 | SIMPLE      | e                            | ALL    | NULL                                                                                                                                                                                          | NULL                                                            | NULL    | NULL                         | 12109 |   100.00 |       |
      |    1 | SIMPLE      | at_tax_class_id_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       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_tax_class_id              | 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       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_special_price             | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                            | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID  | 8       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_special_from_date_default | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_special_from_date         | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_special_to_date_default   | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_special_to_date           | eq_ref | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_DATETIME_STORE_ID                                         | CATALOG_PRODUCT_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_price                     | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                            | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID  | 8       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_cost                      | eq_ref | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_STORE_ID,CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID                                            | CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID  | 8       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    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       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      |    1 | SIMPLE      | at_visibility                | 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       | aaa2.e.entity_id,const,const |     1 |   100.00 |       |
      +------+-------------+------------------------------+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------+---------+------------------------------+-------+----------+-------+
      

      Execution time when the query runs:

      20 rows in set (0.00 sec)
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            stann4355 Stan Stan
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.