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
