MySQL 8 Invisible Indexes

MySQL 8 features invisible indexes. But what good is an index if you can see it?

Is It Really Invisible

You probably know by now that indexes can really speed up data searches. And many of you have mastered the use of EXPLAIN to see if the optimizer is using an index for your query. Which means you should know that sometimes for some queries an index does not help.

mysql> CREATE TABLE t2 (
    ->   i INT NOT NULL,
    ->   j INT NOT NULL,
    ->   UNIQUE j_idx (j)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values (1,2),(3,4),(5,6),(7,8);
Query OK, 4 rows affected (0.00 sec)

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | j_idx         | j_idx | 4       | NULL |    3 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

So a simple select of j with values over 2 uses j_idx.

Hocus pocus - Your Index is now Invisible

But what if we are not sure if that index is really helping? Deleting an index for testing (and then rebuilding) can be a time consuming task. With MySQL 8 you simple make the index invisible.

mysql> alter table t2 alter index j_idx invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select j from t2 where j>2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

In this overly simple example it is easy to see that the query is not using a key, does not know of any possible keys, and had to read more rows to answer the query. So the j_idx query was helping for this case. You can also know if this is true by looking for errors occuring for queries that include index hints that refer to the invisible index, Performance Schema data shows an increase in workload for affected queries, or suddenly the query starts appearing in the slow query log.

Not for Primary keys

Any key can be made invisible except implicit or explicit primary keys. And it is storage engine neutral from MySQL 8.0.1 onward but 8.0.0 can only works with InnoDB. Please read the manual for more detail.

Presto-Change-o

But how do you reverse the invisibility?

mysql> alter table t2 alter index j_idx visible;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0