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

XtraDB no longer using UNIQUE as clustered index when PK missing

    XMLWordPrintable

    Details

      Description

      InnoDB / XtraDB tables that have no explicit PRIMARY KEY are supposed to use the first UNIQUE NOT NULL index as the clustered index. In MariaDB 10.0.13, this is apparently no longer occurring and the internal 6-byte row id is used for new and altered tables.

      https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/optimization-and-indexes/getting-started-with-indexes/#primary-key

      http://dev.mysql.com/doc/refman/5.6/en/innodb-index-types.html

      Furthermore, the clustered key has been useful since MySQL 5.0 for queries that filter by a secondary index and order by the primary key field(s). The above change breaks this and transforms fast queries on MariaDB 5.5 into slower filesorts on MariaDB 10.0.

      http://bugs.mysql.com/bug.php?id=22950

      On MariaDB 10.0.13:

      test> create table t1 (c1 int not null, c2 int, primary key(c1), index (c2)) engine=innodb;
      Query OK, 0 rows affected (0.34 sec)
       
      test> create table t2 (c1 int not null, c2 int, unique index(c1), index (c2)) engine=innodb;
      Query OK, 0 rows affected (0.27 sec)
       
      test> show create table t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `c1` int(11) NOT NULL,
        `c2` int(11) DEFAULT NULL,
        PRIMARY KEY (`c1`),
        KEY `c2` (`c2`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.25 sec)
       
      test> show create table t2\G
      *************************** 1. row ***************************
             Table: t2
      Create Table: CREATE TABLE `t2` (
        `c1` int(11) NOT NULL,
        `c2` int(11) DEFAULT NULL,
        UNIQUE KEY `c1` (`c1`),
        KEY `c2` (`c2`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0.25 sec)
       
      test> insert into t1 (c1, c2) select seq, round(rand()*100) from seq_1_to_1000;
      Query OK, 1000 rows affected (0.26 sec)
      Records: 1000  Duplicates: 0  Warnings: 0
       
      test> insert into t2 (c1, c2) select c1, c2 from t1;
      Query OK, 1000 rows affected (0.26 sec)
      Records: 1000  Duplicates: 0  Warnings: 0
       
      test> explain select * from t1 where c2 = 1 order by c1\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t1
               type: ref
      possible_keys: c2
                key: c2
            key_len: 5
                ref: const
               rows: 9
              Extra: Using where; Using index
      1 row in set (0.25 sec)
       
      test> explain select * from t2 where c2 = 1 order by c1\G
      *************************** 1. row ***************************
                 id: 1
        select_type: SIMPLE
              table: t2
               type: ref
      possible_keys: c2
                key: c2
            key_len: 5
                ref: const
               rows: 9
              Extra: Using where; Using index; Using filesort
      1 row in set (0.25 sec)

      On MariaDB 5.5 the above example does not filesort on either table. My assumption is that the second table causes a filesort because the UNIQUE index is not used as the clustered index, and not due to some other bug.

      The behavior has never been explicit but it is widely expected. If this is a known change, we should document it clearly in the KB.

      Note that this is filed as a MariaDB Server bug because AFAIK it is the server code that makes the decision to use the first UNIQUE key as PK, and the storage engine code simply uses what it is given.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            sean Sean Pringle
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration