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

Feature request: make an index invisible to the optimizer

        Details

        • Type: Task
        • Status: Open
        • 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

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                mrperl James Briggs added a comment - - edited

                Here's the syntax I've implemented in the parser:

                \W
use test
drop table if exists t1;
create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1;
create index idx_col1 on t1 (col1) visible;
show create table t1;
insert into t1 (id, col1) values (1,'abc'), (2, 'def');
alter table t1 change index idx_col1 invisible;
alter table t1 change index idx_col1 visible;
alter table t1 change index idx_id invisible, change index idx_col1 invisible;
select id, col1 from t1 order by id;
drop table if exists t1;
show warnings;

                mysql> \W
                Show warnings enabled.
                mysql> drop table if exists t1;
                Query OK, 0 rows affected, 2 warnings (0.00 sec)

                Note (Code 1051): Unknown table 'test.t1'
                mysql> create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1;
                Query OK, 0 rows affected (0.20 sec)

                mysql> create index idx_col1 on t1 (col1) visible;
                Query OK, 0 rows affected (0.07 sec)
                Records: 0 Duplicates: 0 Warnings: 0

                mysql> show create table t1;

                Table Create Table
                t1 CREATE TABLE `t1` (
                `id` int(11) DEFAULT NULL,
                `col1` char(3) DEFAULT NULL,
                KEY `idx_id` (`id`),
                KEY `idx_col1` (`col1`)
                ) ENGINE=InnoDB DEFAULT CHARSET=latin1

                1 row in set (0.00 sec)

                mysql> insert into t1 (id, col1) values (1,'abc'), (2, 'def');
                Query OK, 2 rows affected (0.01 sec)
                Records: 2 Duplicates: 0 Warnings: 0

                mysql> alter table t1 change index idx_col1 invisible;
                Query OK, 0 rows affected (0.00 sec)
                Records: 0 Duplicates: 0 Warnings: 0

                mysql> alter table t1 change index idx_col1 visible;
                Query OK, 0 rows affected (0.00 sec)
                Records: 0 Duplicates: 0 Warnings: 0

                mysql> alter table t1 change index idx_id invisible, change index idx_col1 invisible;
                Query OK, 0 rows affected (0.00 sec)
                Records: 0 Duplicates: 0 Warnings: 0

                mysql> select id, col1 from t1 order by id;
                ----------+

                id col1

                ----------+

                1 abc
                2 def

                ----------+
                2 rows in set (0.00 sec)

                mysql> drop table if exists t1;
                Query OK, 0 rows affected, 1 warning (0.03 sec)

                Show
                mrperl James Briggs added a comment - - edited Here's the syntax I've implemented in the parser: \W use test drop table if exists t1; create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1; create index idx_col1 on t1 (col1) visible; show create table t1; insert into t1 (id, col1) values (1,'abc'), (2, 'def'); alter table t1 change index idx_col1 invisible; alter table t1 change index idx_col1 visible; alter table t1 change index idx_id invisible, change index idx_col1 invisible; select id, col1 from t1 order by id; drop table if exists t1; show warnings; mysql> \W Show warnings enabled. mysql> drop table if exists t1; Query OK, 0 rows affected, 2 warnings (0.00 sec) Note (Code 1051): Unknown table 'test.t1' mysql> create table t1 (id int, col1 char(3), key idx_id (id) visible) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.20 sec) mysql> create index idx_col1 on t1 (col1) visible; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `col1` char(3) DEFAULT NULL, KEY `idx_id` (`id`), KEY `idx_col1` (`col1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> insert into t1 (id, col1) values (1,'abc'), (2, 'def'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> alter table t1 change index idx_col1 invisible; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 change index idx_col1 visible; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 change index idx_id invisible, change index idx_col1 invisible; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select id, col1 from t1 order by id; ----- -----+ id col1 ----- -----+ 1 abc 2 def ----- -----+ 2 rows in set (0.00 sec) mysql> drop table if exists t1; Query OK, 0 rows affected, 1 warning (0.03 sec)
                Hide
                Permalink
                mrperl James Briggs added a comment - - edited

                The Oracle 11g documentation is fuzzy on whether invisible indexes are session or global or both. If anybody has any thoughts on that, please add a comment.

                In MySQL, various index properties are scoped like this:

                set foreign_key_checks (session or global)
                alter table disable keys (MyISAM-only, global)
                create temporary table (session)

                Show
                mrperl James Briggs added a comment - - edited The Oracle 11g documentation is fuzzy on whether invisible indexes are session or global or both. If anybody has any thoughts on that, please add a comment. In MySQL, various index properties are scoped like this: set foreign_key_checks (session or global) alter table disable keys (MyISAM-only, global) create temporary table (session)

                  People

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

                    Dates

                    • Created:
                      Updated: