What is meant by a hidden index?

A hidden index is one that is not used in query plans but is still maintained and updated as data changes. This allows database administrators to keep the index available for potential use while preventing it from participating in query execution. The primary use case for hidden indexes is to facilitate testing and optimization; administrators can evaluate the performance impact of adding that index back to query execution without the overhead of removing and recreating it.

Keeping such an index allows developers to test whether excluding the index from query execution improves performance. However, the index itself remains in the database and is updated alongside the data, which means it won’t appear in the regular query optimization but is still there if needed later.

This definition clarifies the role of the hidden index in managing potential optimization without affecting ongoing query performance, thus highlighting its purpose within a database to optimize data retrieval processes and make indexing decisions more flexible.