Tuesday, March 2, 2021

Invisible MySQL?

 Is MySQL going invisible?  Invisible Indexes were included in MySQL 8.0 and now with version 8.0.23 we have Invisible Columns.

Indexes You Can Not See!

The value of the invisible index is that it allows you to make an index disappear from the view of the optimizer.  In the distant days before 8.0, you would often delete an index you were pretty much definitively positive nobody or no query was using.  And then you would find out that yes, not only was that index you just deleted necessary to everyone in the galaxy (but maybe you)  but it was going to take some serious clock time to rebuild that index. 

But with Invisible Indexes, you issue a command like ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; and it was removed from use.  Now you can run EXPLAIN on your queries and compare results.  And if you want that index back among the visible, ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;  returns you to full functionality.

I do recommend making an index invisible as part of a process of decommissioning an index, similar to a soft delete of a column to avoid hurried recreation.  And the Sys Schema will show you indexes that have not been used, just make sure you have long enough of a time period to let those queries that only run once a week/month/quarter or longer show themselves.  

Columns You Can Not See

MySQL 8.0.23 now allows you to have columns you can sort of not see.  There are not really invisible or obfuscated but those columns are harder to see.  If we create a table with an invisible column we have to explicitly call out that column to see the values as a * wildcard will not return the value.

SQL > create table stuff (id serial, c1 int, c2 int invisible);
Query OK, 0 rows affected (0.0393 sec)
SQL > insert into stuff (c1, c2) values row(1,2), row(3,4), row(5,6);
Query OK, 3 rows affected (0.0073 sec)
Records: 3  Duplicates: 0  Warnings: 0
SQL > select * from stuff;  <- the wildcard, no invisible column
| id | c1 |
|  1 |  1 |
|  2 |  3 |
|  3 |  5 |
3 rows in set (0.0005 sec)
SQL > select id,c1,c2 from stuff; <- have to call c2 to see c2
| id | c1 | c2 |
|  1 |  1 |  2 |
|  2 |  3 |  4 |
|  3 |  5 |  6 |
3 rows in set (0.0005 sec)

So you could somewhat hide a column by using this method but please do not call this secure.  This will allow you to add columns, say a primary key to a table lacking one, without having to worry about needing to modify existing queries.

And if you are creating a table from a table with an invisible column you need to explicitly reference it or you will not get that data.


The first limitation is that all columns can not invisible so at least one column needs to be visible.  Columns can be defined as NOT NULL and have DEFAULT values.  

What Else Is Going To Be Inviable?!

Well, in my job as a MySQL Community Manager, I do on rare occasion have access to software pre launch and I can tell you there are some really cool things in the product pipeline but I can not let you see them yet. (sorry, very bad joke)