MySQL Bugs: #44447: Incorrect clustered index in InnoDB table?

Bug #44447	Incorrect clustered index in InnoDB table?
Submitted:	23 Apr 2009 21:25	Modified:	24 Apr 2009 6:05
Reporter:	Mariusz G	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb clustered index

Description:
When I create table with command:

CREATE TABLE test (
  id int(11) not null auto_increment,
  name varchar(50) not null,
  PRIMARY KEY (id)
) Engine=InnoDB;

table test is clustered on primary key field.

But, when I add unique index on name field:

CREATE TABLE test (
  id int(11) not null auto_increment,
  name varchar(50) not null,
  PRIMARY KEY (id),
  UNIQUE KEY name (name)
) Engine=InnoDB;

table is clustered on unique key field (name), not primary key (id). InnoDB table should be clustered on primary key (if exists) or unique key (not null field), when primary key doesn't exist. So, I think there is a bug.

And finally, when table test looks like this:

CREATE TABLE test (
  id int(11) not null auto_increment,
  name varchar(50) not null,
  extra_field varchar(50) not null,
  PRIMARY KEY (id),
  UNIQUE KEY name (name)
) Engine=InnoDB;

everything works fine and table is clustered on primary key field.

How to repeat:
Follow the instructions from description and insert some rows with select without ORDER BY section (data should be retrived in order with clustered index).

Thank you for the problem report. I think your testing approach is wrong. Order of rows returned will depend on query execution plan, not the way they are stored. 

Please, send the result of EXPLAIN for the query you use for testing, for all 3 cases. If you do SELECT *, with 2 columns only UNIQUE key is used to read rows, I assume.

Here are the results:

1. First case:

mysql> insert into test (name) values ('one');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test (name) values ('two');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (name) values ('three');
Query OK, 1 row affected (0.02 sec)

mysql> insert into test (name) values ('four');
Query OK, 1 row affected (0.13 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  1 | one   | 
|  2 | two   | 
|  3 | three | 
|  4 | four  | 
+----+-------+
4 rows in set (0.00 sec)

mysql> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4 |       | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

2. Second case:

mysql> insert into test (name) values ('one');Query OK, 1 row affected (0.01 sec)

mysql> insert into test (name) values ('two');
Query OK, 1 row affected (0.03 sec)

mysql> insert into test (name) values ('three');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (name) values ('four');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+
| id | name  |
+----+-------+
|  4 | four  | 
|  1 | one   | 
|  3 | three | 
|  2 | two   | 
+----+-------+
4 rows in set (0.00 sec)

mysql> explain select * from test;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | index | NULL          | name | 152     | NULL |    4 | Using index | 
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+

3. Third case:

mysql> insert into test (name, extra_field) values ('one', 'one');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test (name, extra_field) values ('two', 'two');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (name, extra_field) values ('three', 'three');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test (name, extra_field) values ('four', 'four');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test;
+----+-------+-------------+
| id | name  | extra_field |
+----+-------+-------------+
|  1 | one   | one         | 
|  2 | two   | two         | 
|  3 | three | three       | 
|  4 | four  | four        | 
+----+-------+-------------+
4 rows in set (0.00 sec)

mysql> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    4 |       | 
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

You are right, that in second case unique index is used to read rows. I always thought, that when table has clustered index, rows are returning with ascending order.

So, this is not a bug.