An invisible index is an index that is maintained by the database but ignored by the planner unless explicitly specified.

Why Use an Invisible Index?

Identifying an index as INVISIBLE allows the user to evaluate the impact of:

Dropping an existing index before actually doing so.
Evaluating a new index without impacting existing queries.

Dropping and re-adding an index can take substantial time for large tables, whereas marking an index as VISIBLE or INVISIBLE is a fast, in-place operation that can be easily undone if you do not achieve the expected results.

Invisible Index Characteristics

INVISIBLE indexes are ignored by Sierra, ClustrixDB’s query planner, but continue to be maintained such that they are useable when explicitly named or made VISIBLE.
Unique INVISIBLE indexes still prevent duplicate keys.
INVISIBLE indexes are allowed on persistent tables, partitioned tables, In-Memory tables, and temporary tables.
Any index, with the exception primary keys, may be made INVISIBLE.
If FORCE INDEX or USE INDEX is used with an INVISIBLE index, ClustrixDB will run the query using the index hint. MySQL issues an error.

DDL for VISIBLE or INVISIBLE Indexes

To control the visibility for an index, set the VISIBLE or INVISIBLE keyword when defining an index. Indexes are VISIBLE by default.

CREATE TABLE table_name ...  INDEX index_name (column_name [,column_name...])  [INVISIBLE | VISIBLE]

CREATE INDEX index_name ON  table_name  (column_name [,column_name...]) [INVISIBLE | VISIBLE]

ALTER TABLE table_name … ALTER INDEX index_name [INVISIBLE | VISIBLE]

Sample Scenarios

Steps for evaluating an INVISIBLE index vary depending on whether or not the index already exists.

Make an Existing Index INVISIBLE

ALTER the index to be INVISIBLE then evaluate the impact:

Do queries appear in the query.log as SLOW that did not before? If so, the existing index may be beneficial and not a candidate for removal. See Query Logging for more information.
Does the EXPLAIN for queries that previously used the INVISIBLE index now include any full table scans? This may indicate that the INVISIBLE index was relevant and should not be removed. See Understanding the ClustrixDB Explain Output.
Is the INVISIBLE index specifically named by any USE INDEX or FORCE INDEX hints? These will generate an error if the index is permanently removed.

DROP the index or ALTER it to be VISIBLE, depending on the results. Continue to monitor the query.log.

Creating a New INVISIBLE Index

CREATE a new index as INVISIBLE.

Introduce the new index into queries by specifying USE INDEX or FORCE INDEX.

Compare EXPLAIN output with and without the index to determine if the new index will improve performance. See Understanding the ClustrixDB Explain Output.
Test performance of queries with and without the index by specifying USE INDEX or FORCE INDEX.