The Hidden Costs of Duplicate and Redundant Indexes in MySQL

As database engineers, we strive to optimize query performance and ensure our systems are as efficient as possible. However, even experienced developers can fall into the trap of creating duplicate or redundant indexes in MySQL, leading to significant performance degradation. This blog will delve into the impacts of these indexes, share insights from a recent analysis at Nuclei, and provide practical examples to illustrate these issues.

Understanding Duplicate and Redundant Indexes

Duplicate Indexes

Duplicate indexes are multiple indexes of the same type created on the same set of columns in the same order. MySQL allows you to create these without warning, and it maintains each one separately. This not only consumes additional disk space but also forces the query optimizer to consider each duplicate index, which can slow down query execution.

Example:

CREATE TABLE test (
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    B INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
) ENGINE=InnoDB;

In the above example, an inexperienced user might think they are correctly setting up a primary key, a unique constraint, and an index. However, this creates three indexes on the same column (ID), leading to unnecessary overhead.

Redundant Indexes

Redundant indexes, on the other hand, are indexes where one is a subset of another. For instance, if you have an index on (A, B), another index on (A) would be redundant because the first index can also serve queries on (A).

Example:

CREATE TABLE test (
    A INT NOT NULL,
    B INT NOT NULL,
    INDEX (A),
    INDEX (A, B)
) ENGINE=InnoDB;

In this example, the index on (A) is redundant because the index on (A, B) can cover queries involving just (A).

Performance Impacts of Duplicate and Redundant Indexes

Increased Maintenance Costs

Every index requires maintenance. When you insert, update, or delete rows, MySQL must update each index, leading to increased disk I/O and CPU usage. This maintenance can significantly impact performance, particularly for write-heavy workloads.

Example:

Consider a table with a large number of rows. The following benchmarks show how the presence of multiple indexes affects insert performance:

-- Single index
ALTER TABLE userinfo ADD INDEX (state_id);

-- Redundant index
ALTER TABLE userinfo ADD INDEX (state_id, city, address);

With a single index on state_id, inserting a million rows might take 80 seconds. However, adding a redundant index (state_id, city, address) could increase the time to 136 seconds.

Query Optimization Overhead

The MySQL query optimizer considers all available indexes when planning query execution. Duplicate indexes add unnecessary options, potentially leading to suboptimal execution plans. This can increase query latency and overall system load.

Disk Space Usage

Indexes consume disk space. Duplicate and redundant indexes waste valuable storage, which could be used more effectively. In environments with large datasets, this can lead to significant storage costs.

Case Study: Insights from Nuclei

At Nuclei, we observed an intriguing phenomenon during master-to-worker replication. We noticed that disk I/O was initiated on the worker nodes even when no reporting queries were running. This unexpected I/O was traced back to redundant indexes being maintained during replication.

Analysis

• Initial Observations: High IOPS (Input/Output Operations Per Second) on worker nodes without active queries.
• Investigation: Identified redundant indexes as one of the causes of increased disk I/O during replication.

Practical Examples and Solutions

Identifying Duplicate and Redundant Indexes

To manage indexes efficiently, it’s crucial to identify and eliminate duplicates and redundancies. Tools like Percona Toolkit’s pt-duplicate-key-checker can be invaluable:

pt-duplicate-key-checker --host localhost --user root --password rootpassword

This tool analyzes table structures and suggests indexes that are duplicate or redundant, simplifying the optimization process.

Removing Duplicate Indexes

Once identified, removing duplicate indexes is straightforward:

ALTER TABLE test DROP INDEX index_name;

Extending Existing Indexes

Instead of creating new indexes, consider extending existing ones:

Original index

CREATE INDEX idx_state ON userinfo(state_id);

Extended index:

ALTER TABLE userinfo DROP INDEX idx_state, ADD INDEX idx_state_city (state_id, city);

Conclusion

Duplicate and redundant indexes are hidden performance killers in MySQL databases. They increase maintenance costs, consume unnecessary disk space, and can degrade query performance. By carefully managing indexes and leveraging tools to identify and remove inefficiencies, you can ensure your database operates at peak efficiency.

References: High Performance MySQL