Re: auto_increment and clustering sequence
At 15:41 +0100 6/21/02, Bill Ferrett wrote: 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. Can I do this and if so how? No. You have no say in how the server stores rows in tables. You can control the order in which it retrieves by, by using an ORDER BY clause. For fast lookups on the name column, index it. Many thanks -- Bill Ferrett Accroserve Systems Ltd. Office: 01702 342148 Mobile: 07855 868565 - 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
auto_increment and clustering sequence
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. Can I do this and if so how? Many thanks -- Bill Ferrett Accroserve Systems Ltd. Office: 01702 342148 Mobile: 07855 868565 - 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
Re: auto_increment and clustering sequence
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
