List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:June 21 2002 3:37pm
Subject:Re: auto_increment and clustering sequence
View as plain text  
In the last episode (Jun 21), Bill Ferrett said:
> I would like to have a MySql table where each row has a unique id
> (using auto-increment) but is clustered, i.e. physically stored, in a
> different sequence, e.g. name.  This is so that an sql query to order
> by namew will be quicker.
> 
> I don't want the auto-increment id to be duplicated - I believe this
> happens when the auto_increment column is not the first column - so a
> multi-column index of name,id seems to be out.

Your choice of primary key has no bearing at all on where mysql decides
to store a row in MyISAM tables.  It has a slight bearing on BDB and
InnoDB tables, since they are really index-organized tables.  You'll
get clustering but not sequential order through the entire table.

You can guarantee uniqueness of the auto_increment column by creating
another unique index (not primary, just unique) on the id column.

So basically, do this:

CREATE TABLE mytable ( 
	id INTEGER AUTO_INCREMENT, 
	name VARCHAR(20),
	{other fields}
	PRIMARY KEY (name, id), 
	UNIQUE INDEX (id) 
) TYPE={InnoDB/BDB};

and you're set.

-- 
	Dan Nelson
	dnelson@stripped
Thread
auto_increment and clustering sequenceBill Ferrett21 Jun
  • Re: auto_increment and clustering sequenceDan Nelson21 Jun
  • Re: auto_increment and clustering sequencePaul DuBois22 Jun