Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.13
-
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.
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.