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
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...