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
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
