EmergencyEMERGENCY? Get 24/7 Help Now!

Thoughts on MySQL 8.0 Invisible Indexes

 | October 27, 2016 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

Invisible IndexesMySQL 8.0 has a new feature called “invisible indexes,” which allow you to quickly enable/disable indexes from being used by the MySQL Optimizer.

I wanted to share some of my first experiences and thoughts about this new feature.

Why is it good for us?

There are a couple of use cases. One of them is if you want to drop an index, but want to know the effect beforehand. You can make it invisible to the optimizer. It is a quick metadata change to make an index invisible. Once you are sure there is no performance degradation, you can then drop the index.

The main point is that the invisible index is unavailable for use by the optimizer, but it is still present and kept up-to-date by write operations. The optimizer won’t use it, even if we try to “FORCE INDEX”. I think we should be able to force it somehow, though. There might be scenarios where this could be useful:

  • We can create a new invisible index, but if we want to test it we have to make it visible. That means all the queries are going to be able to use it, which could have an immediate effect on the application. I don’t think this is the best approach if we just want to test it. You should always test on staging, but not everybody has the same data size or real life data on their staging servers. Forcing invisible indexes could be useful.
  • You have many indexes, but are not sure which one is not in use. You can change one index to invisible to see if there is any performance degradation. If yes, you can change it back immediately.
  • You could have a special case where only one query can use that index. In that case, an invisible index could be a great solution.

How can you create invisible indexes?

There are two options. We can create a table with an invisible index like this:

Or we can use alter table and change the index to be invisible:

Using invisible indexes

If we want to remove an index now, we can change it to invisible. But what about queries that use “FORCE/USE INDEX”? Are they are going to throw an error? If you force an index that does not exist, you get an error. You don’t get an error with invisible indexes. The optimizer doesn’t use it, but it knows that it exists.

As you can see, if we use “FORCE INDEX” with an invisible index MySQL performs a full table scan because (from mysql.com):

The FORCE INDEX hint acts like USE INDEX (index_list), with the addition that a table scan is assumed to be very expensive. In other words, a table scan is used only if there is no way to use one of the named indexes to find rows in the table.

MySQL won’t throw any errors because the index exists, but it is not visible. Even if there is another usable index, it is going to perform a full table scan. On a large table, that could cause serious performance issues. Even if MySQL doesn’t throw any errors during the query execution, it should log a warning in the error log.

Conclusion

I think invisible indexes are a great new feature that could be useful for many customers. We should to be able to use an invisible index if necessary, and be able to log queries that are trying to use invisible indexes.

You can read more about invisible indexes in this blog post, and in the MySQL Documentation.

PREVIOUS POST
NEXT POST
Tibor Korocz

Tibi joined Percona in 2015 as a Consultant. Before joining Percona, among many other things, he worked at the world’s largest car hire booking service as a Senior Database Engineer. He enjoys trying and working with the latest technologies and applications which can help or work with MySQL together. In his spare time he likes to spend time with his friends, travel around the world and play ultimate frisbee.

9 Comments

  • Hi Tibor, Good write up! Interesting to know you can FORCE index and get no error, I would actually prefer to get the error in this case as getting index invisible is often step before removing it completely, while allowing to go back easily.

    FORCE index not giving you an error can go below the radar if performance regression is not severe will cause problems once index is finally removed; while re-creating index can take time.

    What is use case of forcing invisible index ? I think if you can afford create index in production for testing you just should create it as normal index and make invisible if it does not work as expected.

  • Nicely written Tibor, and it will be useful to know when the code is trying to force an index that has been set to invisible.

    Can you confirm what is the current behaviour? Does enforcing it actually not use the index but remains silent about the index not being there? Or, does it actually use the index ignoring the fact that it has been made invisible?

    • It does not use any index and being silent at the moment. But it looks like this is going to be changed in 8.0.1, I am going to test that also.

  • This was reported as http://bugs.mysql.com/bug.php?id=82960. Index hints will now always cause an error for missing and invisible indexes alike. The bug was fixed in version 8.0.1.

  • Invisible index is similar to disable index in other RDBMS, like Firebird. I can create simple index (not used for foreign keys, just for incresing speed) and without drop it I can disable it. I can test it’s behavior. Or I can “disable” it in query:
    WHERE a = 12 vs WHERE a+0 = 12

    • Right. Disabling index to test its behavior is what it is designed for. You could use IGNORE INDEX clause in MySQL for ages which I think is better than using expression as this requires assumption what database is unable to optimize expressions well. I think there is work done in MariaDB to be able to optimize the example you show here.

Leave a Reply to Abbas Ahmed Cancel reply