Use Case

The use case for invisible indexes that we focused on when implementing the feature was a preliminary check to see if an index could be dropped. I.e. before dropping the index the user could do:

 ALTER TABLE t ALTER INDEX indx INVISIBLE;

Then if performance went down they could immediately make it visible again.

However, there is also the use case where a user wants to roll out an index. In this case they may want to create the index as invisible first, and in selected sessions activate the index and measure the effect. In this case they will want an optimizer_switch to make the index visible, for instance:

 SET @@optimizer_switch='use_invisible_indexes=on';

Future work

Users might also want a list of sessions where the index is visible, if any. Likely, they can use performance_schema for this, perhaps with a helpful procedure.

• F-1 The new optimizer_switch flag is off by default.
• F-2 If the optimizer_switch is active for a session, that session shall ignore column invisibility completely.
• F-3 Just like all optimizer_switch flags, the value can be set globally and will become the default value for new sessions. See https://dev.mysql.com/doc/refman/5.6/en/switchable-optimizations.html

• NF-1 No session should be affected in any way by another session having the optimizer_switch flag active.

This work adds the optimizer_switch_ use_invisible_indexes, which lets the session's queries leverage invisible indexes.

The function TABLE_SHARE::usable_indexes() is reimplemented to take a THD*, from which it reads the new optimizer_switch's value, removing invisible keys from usable_indexes unless set to 'on'.