Make Indexes Invisible Before Deleting Them

Deleting an unused index in the schema is always a nerve-stretching task. When it was still needed, the application will be slow until the index is recreated which may take a very long time depending on the table's size. But instead of deleting it, you can make it invisible so it's not used anymore. You can then make it visible again within a second or safely delete it some days later when the performance does not get worse.

Usage

MySQL

ALTER TABLE blog_visits ALTER INDEX idx_referer INVISIBLE;

ALTER TABLE blog_visits ALTER INDEX idx_referer VISIBLE;

PostgreSQL

-- Not Supported :(

Detailed Explanation

Identifying and validating obsolete indexes is complicated for huge applications. The past weeks' monitoring information indicates a specific index was never used. And one of your colleagues vaguely remembers that it was added months ago to fix a performance problem. But you still can't be sure whether the index is really not needed anymore: The monitoring could be sketchy or a specific query for a quarterly billing analysis has not been executed yet. Because of these reasons, an index is very seldom deleted to not impact the production application.

However, MySQL provides an alternative solution that massively limits the impact on production. You don't have to delete an index and recreate it when the performance drops, which can take a very long the bigger your table is. When an index is made invisible, it will still be updated but no longer be used by any query. In case the performance drops, you can now reactivate that index within seconds by making it visible anymore. You may still incorrectly guess an index's importance and impact on a production application, but the time to fix that problem is reduced massively.

That's all great, but how do you check that a query is now slow because it used an invisible index? You could alter the visibility status of all invisible indexes for running an EXPLAIN statement to check whether any are used. The more invisible indexes you have, the more tedious this will be. And you should not forget to make them invisible again at the end. But even if doing this correctly, this experimentation will skew the monitoring because MySQL will use every index again. Luckily, the MySQL developers anticipated that problem. You can force MySQL to use invisible indexes for only the current session by changing an optimizer flag: set optimizer_switch = 'use_invisible_indexes=on'

Additional Resources

• MySQL documentation: Invisible Indexes.

Tobias Petry

SQL for Devs Founder