MySQL supports invisible indexes; that is, indexes that are not used by the optimizer. The feature applies to indexes other than primary keys (either explicit or implicit).
Indexes are visible by default. To control visibility explicitly
for a new index, use a
INVISIBLE keyword as part of the index
CREATE INDEX, or
CREATE TABLE t1 ( i INT, j INT, k INT, INDEX i_idx (i) INVISIBLE ) ENGINE = InnoDB; CREATE INDEX j_idx ON t1 (j) INVISIBLE; ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
To alter the visibility of an existing index, use a
keyword with the
ALTER TABLE ... ALTER INDEX
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
Information about whether an index is visible or invisible is
available from the Information Schema
STATISTICS table or
SHOW INDEX output. For example:
mysql> SELECT INDEX_NAME, IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1'; +------------+------------+ | INDEX_NAME | IS_VISIBLE | +------------+------------+ | i_idx | YES | | j_idx | NO | | k_idx | NO | +------------+------------+
Invisible indexes make it possible to test the effect of removing an index on query performance, without making a destructive change that must be undone should the index turn out to be required. Dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible are fast, in-place operations.
If an index made invisible actually is needed or used by the optimizer, there are several ways to notice the effect of its absence on queries for the table:
Errors occur for queries that include index hints that refer to the invisible index.
Performance Schema data shows an increase in workload for affected queries.
Queries have different
Queries appear in the slow query log that did not appear there previously.
variable controls whether the optimizer uses invisible indexes
for query execution plan construction. If the flag is
off (the default), the optimizer ignores
invisible indexes (the same behavior as prior to the
introduction of this flag). If the flag is
on, invisible indexes remain invisible but
the optimizer takes them into account for execution plan
hint to update the value of
you can enable invisible indexes for the duration of a single
query only, like this:
mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */ > i, j FROM t1 WHERE j >= 50\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: j_idx key: j_idx key_len: 5 ref: NULL rows: 2 filtered: 100.00 Extra: Using index condition mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 filtered: 33.33 Extra: Using where
Index visibility does not affect index maintenance. For example, an index continues to be updated per changes to table rows, and a unique index prevents insertion of duplicates into a column, regardless of whether the index is visible or invisible.
A table with no explicit primary key may still have an effective
implicit primary key if it has any
NOT NULL columns. In this case,
the first such index places the same constraint on table rows as
an explicit primary key and that index cannot be made invisible.
Consider the following table definition:
CREATE TABLE t2 ( i INT NOT NULL, j INT NOT NULL, UNIQUE j_idx (j) ) ENGINE = InnoDB;
The definition includes no explicit primary key, but the index
NOT NULL column
places the same constraint on rows as a primary key and cannot
be made invisible:
mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE; ERROR 3522 (HY000): A primary key index cannot be invisible.
Now suppose that an explicit primary key is added to the table:
ALTER TABLE t2 ADD PRIMARY KEY (i);
The explicit primary key cannot be made invisible. In addition,
the unique index on
j no longer acts as an
implicit primary key and as a result can be made invisible:
mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE; Query OK, 0 rows affected (0.03 sec)