MySQL 8 Invisible Indexes

Invisible Indexes

Invisible indexes is new feature added in MySQL 8.0. This feature can help us to mark an index as   unavailable for use by optimizer. This means that index will still be maintained and kept  up to date  in metadata dictionary as data is modified. These marked indexes are not permitted to be used by optimizer even by INDEX hint.

Indexes are visible by default. To control index visibility for a new index or existing one  VISIBLE OR INVISIBLE keywords are used.

for example: If we have following table with index j_idx

CREATE TABLE t1 ( 

i INT, 

j INT, 

k INT, 

INDEX i_idx (i) INVISIBLE 

                                 ) ENGINE = InnoDB; 

CREATE INDEX j_idx ON t1 (j) INVISIBLE; 

ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

To change the visibility of existing  index we can use following statements.

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; 

ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

Information about index, if it is visible or invisible can be extracted from INFORMATION_SCHEMA.STATISTICS as shown below.

mysql> SELECT INDEX_NAME, IS_VISIBLE 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1'; 

+----------------+---------------+ 
| INDEX_NAME     | IS_VISIBLE | 
+----------------+---------------+ 
| i_idx          | YES           | 
| j_idx          | NO            | 
| k_idx          | NO            | 
+----------------+---------------+

Invisible Indexes can help us possible to test the effect of removing an index for query performance, without dropping or recreating index which are expensive operations.

Primary Key Index cannot be made invisible , if you try to do that you will get error as shown below

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE; 

ERROR 3522 (HY000): A primary key index cannot be invisible.

I hope this will help you all, Don’t forget to like, share and comment

Thanks and Regards

Raja M Naveed

 

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *