MySQL Bugs: #57358: Add option to not cluster the table around NOT NULL UNIQUE indexes automatically

Bug #57358	Add option to not cluster the table around NOT NULL UNIQUE indexes automatically
Submitted:	10 Oct 2010 22:26	Modified:	13 Oct 2010 16:28
Reporter:	Justin Swanhart	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S4 (Feature request)
Version:	Any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	clustered index, innodb, null, primary key, unique key

Description:
According to the manual:
"If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE  index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. "

There are many times when a long UNIQUE constraint is desirable while allowing InnoDB to organize around an internal short key.  According to the above, unless one or more columns in the UNIQUE index allow NULL values, MySQL clusters the data around the index.

Adding an AUTO_INCREMENT PRIMARY KEY can resolve this, but there are situations where the auto_increment is not desirable.  Such a case is when SBR is used, because the lock mode must be 0 which decreases insert concurrency due to the mutex being held.

In those cases, an extra char(1) NULL column can be added, but this too is very ugly.

Sometimes I'd like to have a long UNIQUE KEY full of NOT NULL values and just let InnoDB create a unique index instead of clustering around the UNIQUE index.

How to repeat:
Create a table such as the following and use the InnoDB table monitor:
CREATE TABLE `j2` (
  `c1` int(11) NOT NULL,
  `c2` int(11) DEFAULT NULL,
  UNIQUE KEY `c1` (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB

-- InnoDB table monitor --

TABLE: name test/j2, id 0 23, flags 1, columns 5, indexes 2, appr.rows 0
  COLUMNS: c1: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; c2: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
  INDEX: name c1, id 0 33, fields 1/4, uniq 1, type 3
   root page 3, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  c1 DB_TRX_ID DB_ROLL_PTR c2
  INDEX: name c2, id 0 34, fields 1/2, uniq 2, type 0
   root page 4, appr.key vals 0, leaf pages 1, size pages 1
   FIELDS:  c2 c1

Notice that the INDEX named c1 is the clustered index:
   FIELDS:  c1 DB_TRX_ID DB_ROLL_PTR c2

And the other index is not clustered (it holds ref to c1, the clustered col):
   FIELDS:  c2 c1

Suggested fix:
Add an option to either 
a) CREATE TABLE syntax 
or
B) A SQL_MODE which turns this behavior off.

The former is more preferable as it is easier to incorporate into mysqldump with conditional comments.

Hello Justin,

3NF says when you have a relationship between two columns then the referenced column needs to be defined unique.

Which means that referenced column needs to have UNIQUE and NOT NULL (for defined) constrained.

PRIMARY KEY constraint just is the combination of UNIQUE constraint + NOT NULL constraint.

MySQL implemented PRIMARY KEY and UNIQUE KEY constraint as unique index and for PK constraints additonally with NOT NULL constraint.

InnoDB just is SQL standard conform here and our constraints and index implementation is done similar way as you will find it in other DBMS too.

The rule says that every table should have an identifier. The identifier should be in best way a natural identifier. If natural identifier don't exist then an artificial unique identifier should be created (e.g. personal number for employees). This artificial identifier should be as short as possible.

I doubt that we will change the behaviour because it is totally sql standard and conform.

should read http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html

I think reporter is saying that sometimes it is physically more efficient to store
the table with the generated rowid 6-byte clustered index, than automatically cluster on the potentially lengthy user data..

What does any of that have to do with the clustered index?

BNF does not dictate the structure of tables or indexes, only there relationships.

InnoDB can enforce identifying constraints without organizing the table around them.  

On an InnoDB table, when there is a long clustered key, all of the secondary keys have to include the long clustered key.  While it may be faster for unique key checks to use a clustered index, this is not necessary for functionality.

Please don't mark this as won't fix as this is a valid feature request.

Chiming in here, I agree with Justin that there is nothing in SQL specification which dictates that the physical structure of a table must be clustered by the PRIMARY KEY. The fact that InnoDB does this is engine-specific and can be very beneficial in some situations, but there are also cases where a long UNIQUE KEY makes for a terrible PRIMARY KEY. Here are two such reasons:
* it makes secondary indexes much larger than necessary, which is less than optimal
* it may cluster the records very poorly, for instance a random distribution, which can lead to frequent page splits and low page fill factor.

It would be helpful to make this option at the table level, not globally.

InnoDB always organizes data (on disk) around the PRIMARY KEY, and failing that, around the first NOT NULL UNIQUE index on the table.

There are times when this organization is undesirable, particularly when there is the need for an identifying constraint (PRIMARY KEY or UNIQUE NOT NULL) but this constraint is long (in terms of bytes). This long clustered key results in long secondary keys.

In these cases I would like to be able to force an internal 6 byte key.

I suggest:
CREATE TABLE t1 (
 c1 char(64) not null,
 c2 char(64) not null,
 c3 bigint not null,
 primary key (c1,c2),
 unique(c3)
) CLUSTERED INTERNALLY;

and

CREATE TABLE t1 (
 c1 char(64) not null,
 c2 char(64) not null,
 c3 bigint not null,
 primary key (c1,c2),
 unique(c3)
) CLUSTERED USING c3;

The default should be to cluster using the primary key, but the cluster index should be able to be chosen manually, as long as it meets the requirements for a clustered key.

Hi,

I've been developing an application that uses GUID.Comb (semi-sequential GUIDs) as the primary key, inserting into InnoDB tables.

I can definitely see a valid use case for being able to tell InnoDB to please cluster internally, and not on the GUID PK.

Insert performance into a multi-million row drops dramatically (see http://kccoder.com/mysql/uuid-vs-int-insert-performance/ - note this is not my research, I conducted my own to confirm myself and it agrees with these figures).

The workarounds that I have available are:

1) Don't use GUIDs as PKs. I'd like to, because it lets prod/development data always be separate etc.
2) Drop the PRIMARY KEY, and instead use a UNIQUE NOT NULL index on my 'id' column. The minor downside is you can now insert nullable values into this field.
3) Add a special 'rowid' column that is simply a BIGINT AUTO_INCREMENT and mark this as the PK. This has a side effect of preventing data merging if it should ever be needed (since separate databases, such as prod/development, while having separate 'id' GUID rows, would have clashing 'rowid' values).

Using option #2, I can see insert performance appears to be far better (similar to the 'uuid no key, indexed' value on the above page).

There are also various articles such as on StackOverflow saying you shouldn't use GUIDs as PKs for InnoDB tables because of the clustering.

Would you perhaps reconsider adding this feature request?

Thanks,
Andrew