12

What are Visible INDEX and Invisible INDEX in MySQL?

Which is better?

I have a database tables with about 1M rows. The previous development team doesn't add Index to the table columns. Now we need to add Index to some columns to speed up the fetch query.

Improve this question
2
  • Invisible index is only to get it ignored by optimizer. If you want your index to be used for performance gain, it should be visible (default). Refer more here for invisible index - dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html. Invisible feature is generally used when in operation if you feel an index is not giving benefit then rather then dropping it you can make it invisible and see performance difference. Indexes marked invisible can later be removed during scheduled maintenance given it did not led to any performance degradation. Commented Jun 17, 2022 at 18:31
  • Show us your main queries; we will help you design the best INDEXes. Meanwhile, pretend that "INVISIBLE".does not exist, it is only distracting you. Commented Jun 20, 2022 at 15:14

2 Answers 2

Reset to default
23

A question comes up while developing a database: "Do we really need all these indexes?"

Indexes cost some overhead to keep in sync with data. If the index is needed to optimize some queries, it's generally worth keeping the index. But if the index is not used by any query, it might be time to drop that index. But how can you be sure it's not needed?

Why not test by dropping the index and recreating it if it turns out the index is needed? Because tables get really, really large, and adding the index back could take a long time. In one case, one of the developers I supported dropped an index he thought was not needed. It turned out it was important. But adding the index back took four weeks because the table was so huge, and the server was running hard just keeping up with the other queries. In the meantime, the query that needed that index was running extremely poorly.

It would have been preferable to somehow make MySQL temporarily pretend the index does not exist, and then flip a switch and make the index visible again when they decided it was important.

https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html says:

MySQL supports invisible indexes; that is, indexes that are not used by the optimizer.

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.

An invisible index is an index that exists, and is still kept in sync with data as you run INSERT/UPDATE/DELETE statements. But the optimizer treats it as if the index is not there.

An index hint in a query can suppress use of any index, and that's the way we had to test during MySQL 5.x. But that requires finding all your SQL queries that would use the index, and changing application code. That's especially inconvenient if your queries are generated by layers of ORM code.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Image
So if you come across a table that's had an invisible index for, say, a year in a system that's three years old, that's likely a code smell, yes? Or, rather, is there an obvious use case for a long-term invisible index?
Image
Yes, I would expect an invisible index to be used only for testing in a short time frame. Once you know if the index is needed or not, then either make it visible (if it is needed by some queries), or else drop it (if it's not needed by any queries). I can't think of a reason to keep an invisible index for a year.
7

In simple words

Visible INDEX are working or Active Index.

Invisible INDEX are removed Indexes, which were marked as Removed.

Improve this answer

1 Comment

Image
Invisible indexes are not removed. They continue to be updated when you do any INSERT/UPDATE/DELETE. But they are invisible to the optimizer, so they won't be used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.