Redundant Indexes

Issue Type

Design

Problem

The index indicated can be dropped as there is an alternative that covers it.

In the below example, the idx_name index is redundant as any query that uses the name field can also use the idx_name_age index. Thus idx_name can be safely dropped to save on disk space and process overhead to maintaining the index.

CREATE TABLE tab1 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(200),
    age TINYINT,
    INDEX idx_name (name),
    INDEX idx_name_age (name, age),
    PRIMARY KEY (id)
);

You can manually check for redundant indexes by running:

SELECT * FROM sys.schema_redundant_indexes WHERE table_schema="<schema>";

Remediation

Drop the offending index.

ALTER TABLE tab1 DROP INDEX idx_name;

Considerations

• Altering a large table can result in it locking up for an extended period of time. If you're unable to take the table offline to perform this operation, consider using a tool like Online Schema Change to avoid downtime.

External Resources

• Online Schema Change