From: Dan Nelson
Date: June 21 2002 3:37pm
Subject: Re: auto_increment and clustering sequence
List-Archive: http://lists.mysql.com/mysql/112708
Message-Id: <20020621153751.GA95221@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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