X

Learn MySQL with the Curriculum Team

How to use Invisible Indexes in MySQL 8.0

Mark Lewin
MySQL Curriculum Developer

Have you ever tried to improve the performance of a query by adding a new index to a table with a large number of rows, waited for ages while the index is built, only to discover that it didn't really help as much as you'd like?

Or perhaps you have had an index that you have a hunch isn't doing much to help your query performance and decided to drop it, only to have a bunch of users complain that their queries are stalling?

If so, then you will appreciate a nice new feature in the forthcoming MySQL 8.0 release called "invisible indexes".

Invisible indexes enable you to test the effect of removing an index on query performance without making a destructive change that must be undone should the index turn out to be required. By marking an index as invisible, you are effectively "hiding" it from the optimizer while the index itself remains intact and can be restored at any time. This feature makes it much easier to test the removal or addition of indexes and to perform a staged rollout of the changes.

Making an index invisible is not the same as disabling it. The index is still kept up to date and you can continue to maintain it with DML statements.

Every index you create is visible by default. If you want to know if a particular index is visible, you can query the Information Schema:

mysql> SELECT INDEX_NAME, COLUMN_NAME, IS_VISIBLE 
    -> FROM INFORMATION_SCHEMA.STATISTICS
    -> WHERE TABLE_SCHEMA = 'world' AND TABLE_NAME = 'Country';
+------------+-------------+------------+
| INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+------------+-------------+------------+
| PRIMARY    | Code        | YES        |
+------------+-------------+------------+
1 row in set (#.## sec)

Let's say that you want to add a new index on the Continent column in the Country table of the world database, but you're not quite ready to make it generally available. You can add the index with the usual CREATE TABLE, ALTER TABLE, or CREATE INDEX statement, but use the INVISIBLE keyword to hide it from queries:

mysql> ALTER TABLE Country ADD INDEX cont_idx (Continent) INVISIBLE;
Query OK, 0 rows affected (#.## sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT INDEX_NAME, COLUMN_NAME, IS_VISIBLE 
    -> FROM INFORMATION_SCHEMA.STATISTICS
    -> WHERE TABLE_SCHEMA = 'world' AND TABLE_NAME = 'Country';
+------------+-------------+------------+
| INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+------------+-------------+------------+
| cont_idx   | Continent   | NO         |
| PRIMARY    | Code        | YES        |
+------------+-------------+------------+
2 rows in set (#.## sec)

Verify that a query cannot use the index:

mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Country
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 239
     filtered: 14.29
        Extra: Using where
1 row in set, 1 warning (#.## sec)

After some testing you're happy with the resulting increase in performance and so you mark the index as VISIBLE:

mysql> ALTER TABLE Country ALTER INDEX cont_idx VISIBLE;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT INDEX_NAME, COLUMN_NAME, IS_VISIBLE 
    -> FROM INFORMATION_SCHEMA.STATISTICS
    -> WHERE TABLE_SCHEMA = 'world' AND TABLE_NAME = 'Country';
+------------+-------------+------------+
| INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+------------+-------------+------------+
| cont_idx   | Continent   | YES        |
| PRIMARY    | Code        | YES        |
+------------+-------------+------------+
2 rows in set (#.## sec)

And now your queries can use the index:

mysql> EXPLAIN SELECT * FROM Country WHERE Continent = 'Asia'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Country
   partitions: NULL
         type: ref
possible_keys: cont_idx
          key: cont_idx
      key_len: 1
          ref: const
         rows: 51
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (#.## sec)

Dropping and re-adding an index can be expensive for a large table, whereas making it invisible and visible are fast, in-place operations. This makes invisible indexes a great tool for any DBA to have in their toolbox.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.