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

Make an index invisible to the optimizer

    Details

    • Type: Task
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      Oracle 11g added invisible indexes, which would also be useful in MariaDB.

      Morgan Tocker filed a MySQL bug to add that feature:
      http://bugs.mysql.com/bug.php?id=70299

      http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5011.htm
      "VISIBLE | INVISIBLE Use this clause to specify whether the index is visible or invisible to the optimizer. An invisible index is maintained by DML operations, but it is not be used by the optimizer during queries unless you explicitly set the parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE at the session or system level.

      To determine whether an existing index is visible or invisible to the optimizer, you can query the VISIBILITY column of the USER_, DBA_, ALL_INDEXES data dictionary views."

      http://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN12317

      http://www.dba-oracle.com/t_11g_new_index_features.htm

      Note that MariaDB does have an older, more general mechanism already for making invisible indexes:

      ALTER TABLE t1 DISABLE KEYS;
      ALTER TABLE t1 ENABLE KEYS;

      Suggested new syntax in MariaDB:

      CREATE TABLE t1 (id int(10), name char(20), KEY idx_name(name) INVISIBLE);
      ALTER TABLE t1 CHANGE INDEX idx_name INVISIBLE;
      ALTER TABLE t1 CHANGE INDEX idx_name VISIBLE;
      CREATE INDEX ...
      index_option:
          KEY_BLOCK_SIZE [=] value
          / index_type
          / WITH PARSER parser_name
          / INVISIBLE | VISIBLE 
          / COMMENT 'string'

      Some of the server areas this code will affect:

      • parser (create table, create index, alter table)
      • optimizer

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              mrperl James Briggs
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: