Free SQL Help for Devs, DBAs, and the Curious

In the realm of performance optimization, indexes play a pivotal role. However, there comes a point when the implementation of indexes might lead to a performance dip rather than a gain. MySQL 8.0 introduced a brilliant feature to help combat such issues, known as Invisible Indexes. An Invisible Index is an index that the optimizer does not consider while preparing a query execution plan. This blog post will delve into the intricacies of MySQL Invisible Indexes and their role in query tuning.

The concept of Invisible Indexes is similar to the Disabled Indexes in SQL Server 2019. It provides an option to make an index invisible at will and observe the impact on query performance. This is particularly useful when evaluating the effectiveness of an index. The syntax to create an Invisible Index in MySQL is quite straightforward. You simply modify an existing index and set it to invisible.

ALTER TABLE table_name ALTER INDEX index_name INVISIBLE;

To make an index visible again, you simply need to run the ALTER INDEX command with the VISIBLE keyword.

ALTER TABLE table_name ALTER INDEX index_name VISIBLE;

Invisible Indexes also come in handy while performing query tuning in Azure SQL and Azure Synapse. Invisible Indexes are a great method to test the performance impact of removing an index without actually dropping it. If you observe that an index is not improving the performance, you can make it invisible and gauge the impact on the workload.

The concept of Invisible Indexes is not limited to MySQL and SQL Server. DB2 11.5 also supports this concept, although it refers to them as 'Hidden Indexes'. The implementation is almost identical. You can alter an existing index to make it hidden and vice versa using the ALTER INDEX statement.

-- To hide an index in DB2
ALTER INDEX schema.index_name VISIBILITY HIDDEN;
-- To unhide an index in DB2
ALTER INDEX schema.index_name VISIBILITY UNHIDDEN;

To conclude, Invisible Indexes provide a powerful tool for query optimization across multiple SQL platforms. They allow for fine-tuning of database performance by enabling or disabling indexes without changing the physical structure of the database. As a result, DBAs and developers can easily identify and eliminate under-performing indexes thereby improving overall database performance.