SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-13-2008, 06:13 PM
| ||||
| ||||
 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.      |
|
05-13-2008, 06:14 PM
| |||
| |||
| Re: Non clustered primary key on MySQL 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/ |
|
05-16-2008, 01:40 PM
| ||||
| ||||
| Re: Non clustered primary key on MySQL 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! |
Linear Mode
