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

Optimize DROP INDEX, ADD INDEX into RENAME INDEX

Details

    Description

      MDEV-7318 is requesting SQL syntax for ALTER TABLE…RENAME INDEX, similar to MySQL 5.7.

      One could use DROP INDEX, ADD INDEX to simulate RENAME INDEX, but that will currently cause InnoDB to rebuild the index. The index rebuild should be optimized away, and only the index should be renamed. The InnoDB source code for renaming indexes exists starting with MariaDB 10.2, but it is disabled.

      InnoDB should allow ALGORITHM=INSTANT operation for DROP INDEX, ADD INDEX when there is no actual data change. If an index is marked as corrupted, it would be nice to refuse ALGORITHM=INSTANT and actually rebuild the index.

      Attachments

        Issue Links

          Activity

            ivolucien Ivo Havener added a comment -

            This looks like a duplicate of MDEV-7318 - https://jira.mariadb.org/browse/MDEV-7318

            And +1

            ivolucien Ivo Havener added a comment - This looks like a duplicate of MDEV-7318 - https://jira.mariadb.org/browse/MDEV-7318 And +1

            I repurposed this ticket for a something similar that does not involve syntax changes.

            marko Marko Mäkelä added a comment - I repurposed this ticket for a something similar that does not involve syntax changes.

            I attached the patch which does rename for add index & drop index which contains same column and same prefix.
            Patch which does it inside innodb. (Patch also contains indexed varchar column changes). I hope it could be useful.
            rename_index+varchar_length.patch

            It is based on 10.2

            thiru Thirunarayanan Balathandayuthapani added a comment - I attached the patch which does rename for add index & drop index which contains same column and same prefix. Patch which does it inside innodb. (Patch also contains indexed varchar column changes). I hope it could be useful. rename_index+varchar_length.patch It is based on 10.2

            I would like to move the rename detection to the SQL layer, so that index renaming can be done instantly on a wider selection of storage engines.

            marko Marko Mäkelä added a comment - I would like to move the rename detection to the SQL layer, so that index renaming can be done instantly on a wider selection of storage engines.

            Patch changed. Seems to work.

            kevg Eugene Kosov (Inactive) added a comment - Patch changed. Seems to work.

            serg, can you please review the changes outside InnoDB?

            marko Marko Mäkelä added a comment - serg , can you please review the changes outside InnoDB?

            Note for myself. After the fix for MDEV-17376 lands it'll be worth look at it and check how much impact it will have on this issue.

            kevg Eugene Kosov (Inactive) added a comment - Note for myself. After the fix for MDEV-17376 lands it'll be worth look at it and check how much impact it will have on this issue.

            PR rebased to 10.4

            kevg Eugene Kosov (Inactive) added a comment - PR rebased to 10.4

            People

              kevg Eugene Kosov (Inactive)
              novergaard Nicklas Laine Overgaard
              Votes:
              4 Vote for this issue
              Watchers:
              11 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.