An index can be ignored in the following way:
1) Adding IGNORED to the key specification in CREATE TABLE
2) Adding IGNORED to the key specification in CREATE INDEX
3) Adding IGNORED to the key in the ALTER statement
New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability
- Store the information about the ignorability of the index in the FRM.
- A primary index cannot be made IGNORABLE.
- A non-nullable unique key that is promoted to a primary key cannot be made IGNORABLE.
- CHANGES in the output of SHOW CREATE TABLE and SHOW INDEXES|KEYS to show if the index is IGNORED or not.
- Introduce an optimizer switch that would allow the optimizer to not consider the ignorable indexes
ignore_indexes: on/off [name for the switch can be reconisdered]
- An ALTER INDEX operation should use the INPLACE algorithm by default.