I got confused with Visible INDEX and Invisible INDEX in Mysql. Which is best?
I have a database tables with rows 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. While Indexing the column I got confused with Visible and Invisible columns.
Kindly anyone explain me which is best choice. I tried to learn from online tutorials but I can't get it clear.
CodePudding user response：
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.
CodePudding user response：
I got confused with Visible INDEX and Invisible INDEX in Mysql. Which is best?
There is no Which is the best ?
Invisible indexes are a new feature in MySQL 8.0 that provide the ability to mark an index as unavailable for use by the Optimizer.
In simple words if you don't want to use the index anymore you don't have to drop the index (which for large tables might take time) , just make it Invisible and the optimizer wont use it.
From the docs 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.