What is your answer to “if you could have any superpower, what would it be?” If your answer is invisibility, this post is for you.
MySQL 8.0 added a feature called invisible indexes that can improve your query tuning experience. Invisible indexes are indexes that have been created but do not get considered by the query optimizer when retrieving data.
Let’s look at an example of how a query is affected with the same index when it’s visible and invisible.
You Can’t See Us
We’ll start with a few rows of data in a person table:
SELECT id, first_name, last_name
FROM person;
Let’s search for records based on the last_name. If we run for a specific value:
SELECT last_name
FROM person
WHERE last_name = 'Nelson';
We can check the execution plan and find a full table scan. What if we create an index on that last_name column:
CREATE INDEX last_name on person (last_name);
We can run the query again, check the execution plan, and see that our new index is used.
Hocus Pocus
Perhaps that index isn’t getting us the results we need and we want to see how the query perform without the index. The good news is that we don’t have to drop it to get our answer. Instead, we can make it invisible by running:
ALTER TABLE person
ALTER INDEX last_name INVISIBLE;
Let’s run our query for Nelson again:
Now our plan looks like it did before the index had been created.
What if we decide to use our last_name index again? Rather than recreate and rebuild the index, all that’s needed is to set it back to visible:
ALTER TABLE person
ALTER INDEX last_name VISIBLE;
Behind the Curtain
The query below can be executed if you’re curious about what indexes are visible and invisible:
SELECT index_name, is_visible
FROM information_schema.statistics
WHERE table_schema = 'schema_name';
It’s nice to flip a switch making an index invisible without the worry of rebuilding the index if it’s needed again in the future.
Thanks for reading!
Chad Callihan 