MySQL Exact Duplicate Index Finder

Purpose

Identifies indexes on the same table that have identical key column lists in identical positional order. Duplicate indexes in MySQL impose extra overhead on every INSERT, UPDATE, and DELETE against the indexed table and occupy additional storage in the InnoDB tablespace. MySQL does not prevent you from creating duplicate indexes — they must be identified and cleaned up manually.

All index columns are full B-tree key entries, so the comparison is purely on the ordered key column list. Note that MySQL does require the PRIMARY KEY to be the first index entry in an InnoDB table (clustered index) — its column list is always the leftmost prefix of any full-table key scan.

The script queries information_schema.STATISTICS, which is available across all MySQL 8.x versions. The GROUP_CONCAT with ORDER BY is used to build the ordered column signature.

Supported Platforms and Versions

MySQL 8.0 and 8.4 (on-premises and Amazon RDS, Azure Database for MySQL, Google Cloud SQL). Also compatible with MariaDB 10.5 and later, which provides the same information_schema.STATISTICS view.

Required Permissions

SELECT on information_schema.STATISTICS — available to any user with table-level SELECT privileges or the global PROCESS privilege. SELECT on information_schema.TABLE_CONSTRAINTS is also used.

Preconditions

Set the target schema in the WHERE clause. The script defaults to DATABASE() (the currently selected schema). To analyse another schema, replace DATABASE() with the schema name as a string literal.

Risk Level

Low. Read-only. No indexes are dropped. Identifies candidates only.

Script

-- ============================================================
-- MySQL Exact Duplicate Index Finder
-- Permissions : SELECT on information_schema.STATISTICS
-- Risk        : Read-only — identifies candidates, drops nothing
-- Platform    : MySQL 8.0+ / MariaDB 10.5+
-- ============================================================

WITH index_cols AS
(
    SELECT
        table_schema,
        table_name,
        index_name,
        GROUP_CONCAT(
            column_name ORDER BY seq_in_index SEPARATOR ','
        )                                               AS key_cols,
        MAX(non_unique)                                 AS non_unique,
        -- index_type reflects BTREE, FULLTEXT, SPATIAL, HASH
        MAX(index_type)                                 AS index_type
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()     -- change to 'your_schema_name' to target a specific schema
    GROUP BY table_schema, table_name, index_name
),
index_constraints AS
(
    -- Identify which indexes back PRIMARY KEY or UNIQUE constraints
    SELECT
        table_schema,
        table_name,
        constraint_name,
        constraint_type
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND constraint_type IN ('PRIMARY KEY', 'UNIQUE')
)
SELECT
    ic1.table_schema                                    AS "schema",
    ic1.table_name                                      AS "table",
    ic1.index_name,
    ic1.index_type,
    CASE WHEN ic1.non_unique = 0 THEN 'YES' ELSE 'NO' END
                                                        AS is_unique,
    COALESCE(cn1.constraint_type, 'NONE')               AS constraint_type,
    ic1.key_cols,
    ic2.index_name                                      AS duplicate_index,
    ic2.index_type                                      AS duplicate_index_type,
    CASE WHEN ic2.non_unique = 0 THEN 'YES' ELSE 'NO' END
                                                        AS duplicate_is_unique,
    COALESCE(cn2.constraint_type, 'NONE')               AS duplicate_constraint_type
FROM index_cols AS ic1
JOIN index_cols AS ic2
    ON  ic1.table_schema  = ic2.table_schema
    AND ic1.table_name    = ic2.table_name
    AND ic1.index_name    < ic2.index_name
    AND ic1.key_cols      = ic2.key_cols
    AND ic1.index_type    = ic2.index_type   -- only compare same index type (BTREE vs FULLTEXT)
LEFT JOIN index_constraints AS cn1
    ON  cn1.table_schema    = ic1.table_schema
    AND cn1.table_name      = ic1.table_name
    AND cn1.constraint_name = ic1.index_name
LEFT JOIN index_constraints AS cn2
    ON  cn2.table_schema    = ic2.table_schema
    AND cn2.table_name      = ic2.table_name
    AND cn2.constraint_name = ic2.index_name
ORDER BY ic1.table_schema, ic1.table_name, ic1.key_cols;

Safe Execution Guidance

1. Select the target schema before running: USE your_schema_name; or replace DATABASE() with 'your_schema_name' in the WHERE clauses.
2. Each row represents one duplicate pair. Both indexes are functionally equivalent for query coverage — one can be dropped.
3. Check index usage before dropping. MySQL’s sys schema provides a convenient view:

   -- Requires the sys schema (available by default in MySQL 8.x)
   SELECT table_schema, table_name, index_name,
          rows_selected, rows_inserted, rows_updated, rows_deleted
   FROM sys.schema_index_statistics
   WHERE table_schema = DATABASE()
     AND index_name IN ('index_a', 'duplicate_index')
   ORDER BY rows_selected DESC;

4. Confirm whether either index backs a PRIMARY KEY or UNIQUE constraint — these cannot be dropped with a plain DROP INDEX statement:
   • PRIMARY KEY constraint: ALTER TABLE t DROP PRIMARY KEY; (only if you intend to remove the PK entirely)
   • UNIQUE constraint: ALTER TABLE t DROP INDEX constraint_name;
5. Generate candidate DROP INDEX statements for non-constraint indexes:

   SELECT
       CONCAT('DROP INDEX ', ic2.index_name, ' ON ',
              ic2.table_schema, '.', ic2.table_name, ';') AS drop_statement,
       cn2.constraint_type AS is_constraint
   FROM index_cols AS ic1
   JOIN index_cols AS ic2
       ON ic1.table_schema = ic2.table_schema AND ic1.table_name = ic2.table_name
       AND ic1.index_name < ic2.index_name AND ic1.key_cols = ic2.key_cols
   LEFT JOIN index_constraints AS cn2
       ON cn2.table_schema = ic2.table_schema AND cn2.table_name = ic2.table_name
       AND cn2.constraint_name = ic2.index_name
   WHERE cn2.constraint_type IS NULL;
   -- Review before executing. Constraint-backed indexes are excluded from this output.

Expected Output

One row per duplicate pair. An empty result set means no exact duplicates exist in the target schema.

Interpreting Results

Both indexes non-unique, no constraint: Either can be dropped. Check sys.schema_index_statistics to identify which has lower usage.

constraint_type = PRIMARY KEY: The InnoDB clustered index. It cannot be dropped without restructuring the table. The non-PK duplicate is the candidate for removal.

constraint_type = UNIQUE: A unique constraint index. If uniqueness is required, retain this index and drop the non-unique duplicate. If uniqueness is not required, use ALTER TABLE t DROP INDEX constraint_name rather than DROP INDEX.

index_type mismatch between the two indexes in a pair: The script only surfaces pairs with the same index_type. A FULLTEXT and a BTREE index on the same columns are not duplicates — they serve different query patterns. You will only see pairs where both access methods match.

InnoDB secondary index key includes the PK: InnoDB internally appends the primary key columns to every secondary index entry. A secondary index on (a, b) with a PK of (id) is effectively (a, b, id) at the storage level. A secondary index explicitly on (a, b, id) would therefore be a true storage duplicate. This script compares the declared columns only — the implicit PK suffix is not visible in information_schema.STATISTICS.

Supplement: Index Sizes

SELECT
    table_schema,
    table_name,
    index_name,
    ROUND(SUM(stat_value) * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
  AND table_schema = DATABASE()
GROUP BY table_schema, table_name, index_name
ORDER BY size_mb DESC;

Rollback Steps

Not applicable — read-only script.

Official References

• information_schema.STATISTICS
• sys.schema_index_statistics
• DROP INDEX Statement
• ALTER TABLE Statement
• innodb_index_stats