Many users/customers (Dropbox being the biggest one) have requested the ability to make a column/index invisible in order to determine if it can be dropped. This way they can see if any apps or DB users are actually using it (are any errors generated/reported) before making the final decision to drop it.

Examples of user comments are: "I would like the ability to temporary disable indexes for query execution so I can check it is safe to drop them (Oracle calls these INVISIBLE indexes)"

"Global IGNORE INDEX -- It would tell the optimizer to never use a specific index. The index would continue to be maintained. This would be the first step in trying to see if the INDEX can be dropped. (Morgan: this is called INVISIBLE indexes in Oracle (invisible to optimizer but otherwise works.) Very useful feature request.)"

Here's a reference to the INVISIBLE clause that can be used in ALTER TABLE DDL in Oracle DB: http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#SQLRF01001

See also: http://bugs.mysql.com/bug.php?id=70299

Please note: Invisible columns are not in scope for this work log.

• F-1 An index can be turned visible or invisible either when created or through the new ALTER TABLE operation ALTER INDEX.
• F-2 The tables information_schema.statistics and mysql.indexes get a new column is_visible, added as the last column. This also changes the result of SHOW KEYS|INDEXES.

• NF-1 An invisible index shall be maintained as any other index.
• NF-2 An invisible index shall not be considered by the optimizer.
• NF-3 A primary key index can never be made invisible.
• NF-4 An ALTER INDEX operation uses the INPLACE algorithm by default.
• NF-5 The feature is guaranteed to work on InnoDB.
• NF-6 An index that is promoted to primary key - i.e. the index over the first NOT NULL UNIQUE column - cannot be made invisible.

Survey

• Microsoft SQL Server has the possibility to disable indexes. Disabled indexes are not maintained, and the PRIMARY KEY or UNIQUE constraint and all FOREIGN KEY constraints that reference the indexed columns from other tables are also disabled.

• Oracle has since version 12c the possibility to make indexes invisible via the ALTER INDEX command. This behavior seems a close match to what our customers are requesting.

• Postgres does not seem to have a command for making indexes invisible, but there appears to be a workaround involving the system catalogue, mention in this blog post from 2014: https://thecuriousfrequency.wordpress.com/2014/11/16/disable-an-index-in-postgres/.

Invisible Indexes in Oracle 12c

Invisible indexes in the Oracle database have the following properties:

• It is maintained as usually.
• The optimizer ignores invisible indexes.
• One can make the optimizer see the invisible indexes by setting the OPTIMIZER_USE_INVISIBLE_INDEXES parameter, either on global or session level.
• An invisible index will be used if mentioned in a hint.

An index is made invisible by issuing the command ALTER INDEX <index> INVISIBLE. All index names shares the same namespace, so index names are always unique, hence no mention of table names are needed.

Syntax Changes in MySQL to support Invisible Indexes

• The parser will get two new keywords, INVISIBLE and VISIBLE.

• There will be a new index option [VISIBLE|INVISIBLE] whenever an index is declared. VISIBLE is the default.

• There will be a new statement type ALTER INDEX. The form is ALTER INDEX ident [VISIBLE|INVISIBLE].

The grammar for the new ALTER TABLE is:

 ALTER [IGNORE] TABLE tbl_name
     [alter_specification [, alter_specification] ...]
     [partition_options]

This work will add a new alter_specification:

 alter_specification:
     ...
   | ALTER INDEX index_name {VISIBLE|INVISIBLE}

The grammar for CREATE TABLE is (in one instance):

 CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
     (create_definition,...)
     [table_options]
     [partition_options]

 create_definition:
     col_name column_definition
     ...
   | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
       [index_option] ...
     ...

This work will add a new index_option:

 index_option:
     ...
   | {VISIBLE|INVISIBLE}

Meta-data changes

This work will add one column to the data dictionary table 'indexes': is_visible. The datatype of the column is BOOL NOT NULL. The column is added as the 7:th column, before is_generated.

The new column is visible as result of SHOW KEYS/INDEXES, SELECT * FROM mysql.columns or SELECT * FROM mysql.indexes.

Invisible indexes will not be usable.

After a long discussion, it has been decided that a method for making invisible indexes usable by the optimizer, e.g. optimizer switch, session variable, index hint, etc will not be part of this work.

Behavior of invisible indexes

An invisible index cannot be used by the optimizer at all.

Primary Key Indexes cannot be Invisible

The implementation of primary key constraints relies heavily on the presence of a primary key index. Furthermore, primary key indexes are rarely subjects for removal. For this reason primary key indexes cannot be invisible.

Parser Changes

• The parser gets two new tokens, INVISIBLE and VISIBLE. They are not reserved words.

• There is a new rule with a semantic type of bool:

   visibility:
             VISIBLE_SYM { $$= true; }
           | INVISIBLE_SYM { $$= false; }
           ;

• The <alter> rule gets a new production ALTER INDEX ident visibility

• The <normal_key_opt> rule also gets a new production <visibility>.

AST Changes

• KEY_CREATE_INFO gets a new member is_visible.

Internal Index Representation Changes

• KEY gets a new member m_is_invisible.

Information Schema Changes

The table information_schema.statistics gets a new column IS_VISIBLE, added as the last column. This also changes the result of SHOW KEYS|INDEXES.

Implementation of ALTER TABLE ALTER INDEX

The runtime asks that we do not use inheritance or virtual functions inside the implementation of ALTER. The preferred method for code reuse is copy-paste. The below design follows from this request.

A new list of Alter_index_invisibility objects is added to Alter_info. The new operation is similar to RENAME KEY functionality and is arguably a special case thereof. Therefore all chunks of code pertaining to RENAME KEY have been copy-pasted and tailored to the simpler case of altering visibility.

Data Dictionary Changes

The data dictionary table INDEXES gets a new column is_visible BOOL NOT NULL.

Result of SHOW CREATE TABLE

The INVISIBLE/VISIBLE keywords will use the normal backward-compatible notation used for mysqldump/mysqlpump, e.g. the result of SHOW CREATE TABLE for a table with an invisible index "c" will be:

 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) DEFAULT NULL,
   `b` int(11) DEFAULT NULL,
   `c` int(11) DEFAULT NULL,
   KEY `a` (`a`),
   KEY `b` (`b`) /*!50800 INVISIBLE */,
   KEY `c` (`c`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1