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.
      • Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
        ignore_indexes: on/off [name for the switch can be reconisdered]
      • An ALTER INDEX operation should use the INPLACE algorithm by default.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              mrperl James Briggs
              Votes:
              10 Vote for this issue
              Watchers:
              11 Start watching this issue

                Dates

                Created:
                Updated: