Visible index or not?

Question

About using the parameter Visible of index of a table, I would like to know:

• What is the effect of this parameter on a table field?

• This parameter is specific to Mysql or found in other BD?

• Will optimize my index?

• Other considerations?

Answer 1

This setting makes the index "invisible" to the query optimizer, in other words, when running a query, this index will be ignored by the optimizer when trying to mount the best plan to run the query.

More about the functionality here: https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html

What is the influence of this parameter?

It totally influences the execution of the querires. Once the index is being ignored, it can change performance sensitivity, and can generate errors if any query has a hint to use the index. In the maintenance view of the index nothing changes, it will not be used but continues to be updated.

This parameter is specific in Mysql or found in other BD?

This question is too wide, I would have to know each bank, but I can say about:

SQL-SERVER does not have the same configuration. No SQL-SERVER set an index as disabled has the same effect on the query parser, it will get ignored, but the effect is more comprehensive: the index is ignored by the bank, and is not updated. One can, for the purpose of testing use a hint to ignore the index, force another, etc, but this by query.

ORACLE I am not deep knowledgeable, but there is the option to set an index as disabled, but I do not know the consequences well.

Will optimize my index?

Not the index, it will continue to be updated (MYSQL), will influence the performance of sweethearts

To close, I will quote what the documentation says about its use:

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.

In free translation:

Invisible contents make it possible to test the effect of removing a content in the performance of the consultation, without making a destructive change which must be undone if the index is necessary. Delete and adding again an index can be costly for a large table, while making it invisible/visible are fast and onsite operations.