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

Range checked for each record used with key

    XMLWordPrintable

Details

    • 10.0.20

    Description

      Using the attached mysqldump, the following query is very slow in MariaDB compared to MySQL 5.5 or MySQL 5.6:

      SELECT 1 FROM `catalog_category_flat_store_1` AS `main_table`
                    LEFT JOIN `core_url_rewrite` AS `url_rewrite`
                       ON url_rewrite.category_id=main_table.entity_id
                       AND url_rewrite.is_system=1
                       AND url_rewrite.store_id = 1
                       AND url_rewrite.id_path LIKE 'category/%'
                WHERE (main_table.include_in_menu = '1')
                AND (main_table.is_active = '1')
                AND (main_table.path like '1/2/%')
                ORDER BY `main_table`.`position` ASC

      The schema and query are part of Magento, but the attached dump has had FK, some unique indexes, and most of the columns dropped.

      The query plan in MySQL, which executes quickly:

      |  1 | SIMPLE      | main_table  | ALL   | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH                                                                                                                       | NULL                                            | NULL    | NULL |  124 | Using where; Using temporary; Using filesort |
      |  1 | SIMPLE      | url_rewrite | range | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID | 773     | NULL | 1138 | Using where                                  |

      5.6 has BNL and using Join buffer for the second table, and runs at a similar speed.

      In MariaDB 5.5 and MariaDB 10, the following slow plan is used:

      |    1 | SIMPLE      | main_table  | ALL   | IDX_CATALOG_CATEGORY_FLAT_STORE_1_PATH                                                                                                                       | NULL                                            | NULL    | NULL |  149 | Using where; Using filesort                     |
      |    1 | SIMPLE      | url_rewrite | range | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID,IDX_CORE_URL_REWRITE_ID_PATH,IDX_CORE_URL_REWRITE_STORE_ID,FK_CORE_URL_REWRITE_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID | UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID | 773     | NULL | 1138 | Range checked for each record (index map: 0x74) |

      This can be made fast in MariaDB with ADD INDEX ix_foo (category_id, store_id) or IGNORE INDEX.

      There also seems to be no optimizer_switch to turn off this strategy.

      Attachments

        1. bugtest.sql.gz
          8.09 MB
          MG
        2. mdev-6735_x2_x1_testcase.sql.gz
          156 kB
          Daniel Black
        3. MDEV-6735.patch
          0.6 kB
          Daniel Black
        4. mdev-6735.sql
          5 kB
          Daniel Black

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              mg MG
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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