Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10153

MariaDB allows creation of duplicate indexes with identical definitions

    Details

      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.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              nickivanov nick ivanov
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: