[#MDEV-10153] MariaDB allows creation of duplicate indexes with identical definitions

One is allowed to create multiple indexes with identical definitions. For example, the following leads to creation of 4 separate indexes on the same column.

create table test (f1 int not null);

create unique index ix1 on test (f1);

alter table test add primary key (f1);

create unique index ix2 on test (f1);

create  index ix3 on test (f1);

select * from information_schema.innodb_sys_indexes where table_id = (select table_id from information_schema.innodb_sys_tables where name = 'onedb/test');

+----------+---------+----------+------+----------+---------+-------+

| INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |

+----------+---------+----------+------+----------+---------+-------+

|     2051 | PRIMARY |      768 |    3 |        1 |       3 |   754 |

|     2052 | ix1     |      768 |    2 |        1 |       4 |   754 |

|     2053 | ix2     |      768 |    2 |        1 |       5 |   754 |

|     2054 | ix3     |      768 |    0 |        1 |       6 |   754 |

+----------+---------+----------+------+----------+---------+-------+

4 rows in set (0.00 sec)

Ideally, you should be allowed to create only one index with a given combination and order of columns, and constraints should reuse matching indexes instead of creating new ones.

[MDEV-10153] MariaDB allows creation of duplicate indexes with identical definitions Created: 2016-05-30 Updated: 2023-04-27
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s:	10.4

[MDEV-10153] MariaDB allows creation of duplicate indexes with identical definitions Created: 2016-05-30 Updated: 2023-04-27