Removing duplicate and redundant indexes

You can define several indexes on a column. By mistake, you might have defined the same index again (same column, same order of columns, or same order of keys), which is called a duplicate index. If only partial indexes (leftmost columns) are repetitive, they are called redundant indexes. A duplicate index has no advantages. Redundant indexes might be useful in some cases (a use case is mentioned in the note at the end of this section), but both slow down the inserts. So, it is important to identify and remove them.

There are three tools that can help with finding out duplicate indexes:

• pt-duplicate-key-checker, which is part of the Percona Toolkit. Installing the Percona Toolkit is covered in Chapter 10, Table Maintenance, Installing Percona Toolkit section.
• mysqlindexcheck, which is part of MySQL utilities. Installing MySQL utilities is covered in Chapter 1, MySQL 8.0 – Installing and Upgrading.
• Using the sys schema, which will be covered in the next...