[MDEV-6794] XtraDB no longer using UNIQUE as clustered index when PK missing Created: 2014-09-26  Updated: 2014-11-21  Resolved: 2014-11-21

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.0.13
Fix Version/s: 10.0.15

Type: Bug Priority: Critical
Reporter: Sean Pringle Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: xtradb
Environment:

Ubuntu 12.04 LTS ,Linux db1011 3.2.0-60-generic #91-Ubuntu SMP


 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.

 Comments   
Comment by Sergei Golubchik [ 2014-11-16 ]

in 10.0.14 XtraDB is using the first unique not null index as a primary key:

MariaDB [test]> create table t1 (c1 int not null, c2 int, index(c1)) engine=innodb;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> select t.name, i.name from information_schema.INNODB_SYS_TABLES t join
 
 information_schema.INNODB_SYS_INDEXES i USING(TABLE_ID) where t.name like '%t1';
 
+---------+-----------------+
 
| name    | name            |
 
+---------+-----------------+
 
| test/t1 | GEN_CLUST_INDEX |
 
| test/t1 | c1              |
 
+---------+-----------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> create table t2 (c1 int not null, c2 int, unique index(c1)) engine=innodb;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> select t.name, i.name from information_schema.INNODB_SYS_TABLES t join
 
 information_schema.INNODB_SYS_INDEXES i USING(TABLE_ID) where t.name like '%t2';
 
+---------+------+
 
| name    | name |
 
+---------+------+
 
| test/t2 | c1   |
 
+---------+------+
 
1 row in set (0.00 sec)

See how the first table is created with non-unique index and XtraDB generates a clustered index. The second table is created with unique index, and there is no generated clustered index.

Now looking at filesort...

Generated at Thu Feb 08 07:14:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.