Invisible Indexes 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: The  use_invisible_indexes  flag of the  optimizer_switch  system 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 construction. Using the  SET_VAR  optimizer hint to update the value of  optimizer_switch  temporarily, you can enable invisible indexes for the duration of a single query only, like this: 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  UNIQUE indexes on  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: 1728 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 •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  EXPLAIN  execution plans. •Queries appear in the slow query log that did not appear there previously.