Create a mysql primary key without a clustered index?

Question

I'm a SQL Server guy experimenting with MySQL for a large upcoming project (due to licensing) and I'm not finding much information in the way of creating a primary key without a clustered index. All the documentation I've read says on 5.1 says that a primary key is automatically given a clustered index. Since I'm using a binary(16) for the primary key column (GUID), I'd rather not have a clustered index on it. So...

1. Is it possible to create a primary key without a clustered index? I could always put the clustered index on the date_created column instead, but how do I prevent mysql from creating the clustered index on the primary key automatically?
2. If not possible, will I be OK performance wise with a unique index on the GUID column and no primary key on the table? I'm planning to use nhibernate here, so I'm not sure if having no primary key is allowed (haven't got that far yet).

Answer 1

It depends on which storage engine you are using. MyISAM tables do not support clustered indices, so primary keys on MyISAM tables are not clustered. The primary key on an InnoDB table, however, is clustered.

You should consult the MySQL Manual for further details about the pros and cons of each storage engine.

You need to have a primary key; if you don't create one yourself, MySQL will create a hidden one for you. You could always just create an AUTO_INCREMENT field for the primary key (this is preferable to having MySQL have hidden fields in your table, I think).

Answer 2

Considering what is said on 13.6.10.1. Clustered and Secondary Indexes, it seems you cannot really define on which column the clustered index is set :

• it's either on the PK column
• or on the first column with a UNIQUE index that only has non-null values
• or on some internal column -- not that usefull in your case ^^

About the second question in your post : no PK on the table, and a UNIQUE index on the GUID ; it might be possible, but it will not change anything about the clustered index : it will still probably be on the GUID column.

Some kind of a hack might be to :

• not define a primary key
• place a UNIQUE index on your date_created field (if you don't create too many rows in short periods of time, it could be viable... )

Not sure you can place a second UNIQUE index on the GUID... Maybe ^^
And not sure that would change much about the clustered index ; but might be worth a try...