Non clustered primary key on MySQL

Hi:
We have an application in our company and we use 32 byte keys,
generated from hibernate, being used as primary keys in our MySQL
database. As these are the primary keys for MySQL, they also become
the clustered index for the table for which they belong to. I don't
have any other field that I would like to use as clustered key, and I
don't want to create an auto increment field just for that.

In resume: is there anyway to DISABLE MySQL from creating a clustered
index and to make it order the records in the same order as they're
inserted in the database? I know SQL Server does that and I was hoping
MySQL would do the same.

Regards, Ravi.

raviaw <raviaw@gmail.com> wrote:

> We have an application in our company and we use 32 byte keys,
> generated from hibernate, being used as primary keys in our MySQL
> database. As these are the primary keys for MySQL, they also become
> the clustered index for the table for which they belong to.

This is only true for InnoDB tables. In the following I assume that
we are talking about InnoDB tables.

> In resume: is there anyway to DISABLE MySQL from creating a clustered
> index and to make it order the records in the same order as they're
> inserted in the database?

You will get something along that if you do not specify a PRIMARY KEY
in the table definition. Your former primary key becomes a UNIQUE KEY.

With no primary key in the table definition, InnoDB will create a
hidden primary key very similar to BIGINT UNSIGNED AUTO_INCREMENT
and use it internally to identify rows. Rows will be clustered
according to this hidden column and secondary indexes will refer
to rows via that hidden column too.


HTH, XL
--
Axel Schwenke, MySQL Support Engineer, Sun Microsystems GmbH

MySQL User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On May 12, 4:28 pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> raviaw <rav...@gmail.com> wrote:
> > We have an application in our company and we use 32 byte keys,
> > generated from hibernate, being used as primary keys in our MySQL
> > database. As these are the primary keys for MySQL, they also become
> > the clustered index for the table for which they belong to.
>
> This is only true for InnoDB tables. In the following I assume that
> we are talking about InnoDB tables.
>
> > In resume: is there anyway to DISABLE MySQL from creating a clustered
> > index and to make it order the records in the same order as they're
> > inserted in the database?
>
> You will get something along that if you do not specify a PRIMARY KEY
> in the table definition. Your former primary key becomes a UNIQUE KEY.
>
> With no primary key in the table definition, InnoDB will create a
> hidden primary key very similar to BIGINT UNSIGNED AUTO_INCREMENT
> and use it internally to identify rows. Rows will be clustered
> according to this hidden column and secondary indexes will refer
> to rows via that hidden column too.
>
> HTH, XL
> --
> Axel Schwenke, MySQL Support Engineer, Sun Microsystems GmbH
>
> MySQL User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums:http://forums.mysql.com/

Great, that is what I was looking for. Thanks!