Invisible index

If you want to drop an unused index, then instead of dropping immediately, you can mark it as invisible, monitor the application behavior, and later drop it. Later, if you need that index, you can mark it as visible, which is very fast compared to dropping and re-adding indexes.

To explain the invisible index, you need to add normal index if not already there. Example:

mysql> ALTER TABLE employees ADD INDEX (last_name);
Query OK, 0 rows affected (1.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to do it...

If you wish to drop the index on last_name, rather than directly dropping, you can mark it as invisible using the ALTER TABLE command:

mysql> EXPLAIN SELECT * FROM employees WHERE last_name='Aamodt'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: last_name
          key: last_name
      key_len: 66
          ref: const
         rows...