Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Description
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.