MySQL 8 invisible indexes

MySQL 8 supports invisible indexes, This allows you to on-demand enable/disable indexes from being used by MySQL optimizer. Now please don’t get confused with “disabled indexes“, “invisible indexes are not disabled indexes, MYISAM supports disabled indexes, ” , The disabled indexes halt maintenance of an index. Invisible indexes are a new feature in MySQL 8.0 , which mark an index unavailable for use by the optimizer. That means, Index will still be maintained and keep up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses a FORCE INDEX hint) .

Why we really love invisible indexes in MySQL 8.0?

• You want to make only one query to use that index, In this case “invisible index” is a great option
• On-demand indexing, You will have index (up-to-date with data) but you can make it visible or invisible. Even when optimizer force index, invisible indexes will not be invoked.
• Testing which index is efficient for many queries, You can test them by enabling invisible index and do not forget to disable them before testing with another invisible index, You can test index efficiency in production system (instant gratification indeed !)

How can you create MySQL invisible indexes?

There are two ways you can create invisible indexes:

Step1 is quite direct.

CREATE TABLE tab1 (
col1 int(10) DEFAULT NULL,
col2 int(10) DEFAULT NULL,
col3 int(10) DEFAULT NULL,
KEY i_idx (col1),
KEY idx_1 (col1,col2,col3) INVISIBLE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Step 2 is with “alter table script”

mysql> alter table tab1 alter index idx_1 invisible;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Using invisible index 

mysql> show create table tab1 \G;
*************************** 1. row ***************************
       Table: tab1
Create Table: CREATE TABLE `tab1` (
  `col1` int(10) DEFAULT NULL,
  `col2` int(10) DEFAULT NULL,
  `col3` int(10) DEFAULT NULL,
  KEY `i_idx` (`col1`),
  KEY `idx_1` (`col1`,`col2`,`col3`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Now, Let see we can forcefully make optimizer use the invisible index (here it is – idx_1)

mysql> explain select * from tab1 force index (idx_1) where col1=20 and col2=30;
ERROR 1176 (42000): Key 'idx_1' doesn't exist in table 'tab1'

Let’s try, How we can forcibly make optimizer use the index i_idx (this works because index is not invisible)

mysql> explain select * from tab1 force index (i_idx) where col1=20 and col2=30;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tab1  | NULL       | ref  | i_idx         | i_idx | 5       | const |    1 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

We are converting the index “idx_1” visible to make optimizer forcibly use

mysql> alter table tab1 alter index idx_1 visible;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from tab1 force index (idx_1) where col1=20 and col2=30;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | tab1  | NULL       | ref  | idx_1         | idx_1 | 10      | const,const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

We cannot alter Primary Key to invisible index:

mysql> create table tab3
    -> (col11   int(10)  not null, 
    -> col22    int(10)  not null, 
    -> col33    int(10) not null,
    -> unique  uidx(col11));
Query OK, 0 rows affected (0.10 sec)

mysql> alter table tab3 alter index uidx invisible; 
ERROR 3522 (HY000): A primary key index cannot be invisible
mysql> 

Conclusion

MySQL 8.0 invisible index is an very interesting feature, This enables Database Architects / DBAs / Database Engineers to choose optimal indexing for  MySQL performance and scalability. We are very excited about this feature, This makes several of our customer applications more optimal..

MySQL 8 invisible indexes (MySQL official documentation) – https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html