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

Make an index ignorable to the optimizer

    XMLWordPrintable

Details

    Description

      SYNTAX

      An index can be ignored in the following way:

      1) Adding IGNORED to the key specification in CREATE TABLE
      Example

      CREATE TABLE t1(a INT, key key1(a) IGNORE);
      

      2) Adding IGNORED to the key specification in CREATE INDEX
      Example

      CREATE INDEX key1 on t1 (a) IGNORE;
      

      3) Adding IGNORED to the key in the ALTER statement

      New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

      ignorability:

      IGNORE_SYM
      NOT IGNORE_SYM

      Example:

       ALTER TABLE t1 ADD INDEX key1 IGNORE;
      

      Specifications

      • Store the information about the ignorability of the index in the FRM.
      • A primary index cannot be made IGNORABLE.
      • A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
      • CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
      • An ALTER INDEX operation should use the INPLACE algorithm by default.

      Attachments

        Issue Links

          Activity

            People

              varun Varun Gupta (Inactive)
              mrperl James Briggs
              Votes:
              11 Vote for this issue
              Watchers:
              13 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.