Make an index ignorable to the optimizer

SYNTAX

An index can be ignored in the following way:

1) Adding IGNORED to the key specification in CREATE TABLE
Example

CREATE TABLE t1(a INT, key key1(a) IGNORE);

2) Adding IGNORED to the key specification in CREATE INDEX
Example

CREATE INDEX key1 on t1 (a) IGNORE;

3) Adding IGNORED to the key in the ALTER statement

New syntax: ALTER TABLE table_name ALTER INDEX key_name ignorability

ignorability:

Example:

 ALTER TABLE t1 ADD INDEX key1 IGNORE;

Specifications

• 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.
• An ALTER INDEX operation should use the INPLACE algorithm by default.