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

MariaDB allows creation of duplicate indexes with identical definitions

    XMLWordPrintable

    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 Unassigned
            Reporter:
            nickivanov nick ivanov
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration