sunnyhirai
asked on
How do I add an auto-increment field to a multi-column key
Is it possible to add an auto-increment field to a table with a multi-column key? For example, I would like a table something like this:
group_id: key int
sort_order: key int
id: auto-increment int
...other non key fields go here...
The reason I want the key on the group_id and sort_order (as an example, not my actual application) is so that I can take advantage of the clustered index on INNODB tables. I use this a lot on MS SQL Server to speed up queries.
For example, if I want to list all the records of group_id = 3 and output them in their sort_order, the clustered index means that I can retrieve all the records in one place and in the proper order.
Yes, I've tried putting a key on the "id" field after group_id and sort_order but I keep getting this error.
MySQL Error Number 1075
Incorrect table definition; there can be only one auto column and it must be defined as a key
As I mentioned, I did try defining it as a key and it is the only auto column on the table. Is this just a limitation of INNODB tables?
Any help would be greatly appreciated. Thanks.
Sunny
group_id: key int
sort_order: key int
id: auto-increment int
...other non key fields go here...
The reason I want the key on the group_id and sort_order (as an example, not my actual application) is so that I can take advantage of the clustered index on INNODB tables. I use this a lot on MS SQL Server to speed up queries.
For example, if I want to list all the records of group_id = 3 and output them in their sort_order, the clustered index means that I can retrieve all the records in one place and in the proper order.
Yes, I've tried putting a key on the "id" field after group_id and sort_order but I keep getting this error.
MySQL Error Number 1075
Incorrect table definition; there can be only one auto column and it must be defined as a key
As I mentioned, I did try defining it as a key and it is the only auto column on the table. Is this just a limitation of INNODB tables?
Any help would be greatly appreciated. Thanks.
Sunny
ASKER
Thanks for the response.
However, what I'm looking for is a CLUSTERED index on the group_id, sort_order fields. In MS SQL Server, you can define an auto-increment field separate from the tables clustered index. In MySQL, it appears that they are somewhat related because they both needs to be based on the KEY, but this is not very well documented.
A clustered index is different than a regular index in that the records in the table are *physically ordered on the hard drive* in the order of the clustered index. So, for example, if I want to retrieve all the records with group_id = 22 (for example), the read heads on the hard drive read their data sequentially because all the records in group_id=22 are right next to each other on the hard drive. That is, the read heads don't have to jump around the hard drive collecting this data. Instead, it reads it in one continuous chunk. This is much faster than reading it from many positions on the hard drive.
At the same time, I still want a unique auto-incremented id fields. I realize I can probably fake the auto-increment, but this seems unclean and a little dangerous as it may be hard to tell what, if any, side effects there are until it is put under load. For example, I don't want to grab a MAX(id) and add 1 to it because the time between grabbing the max(id) and then inserting a new record, another process might have grabbed the same max(id) and then there would be two identical IDs. Then I could put it into a transaction block but this affects performance again.
Since we are potentially hostings hundreds of thousands of sites off a shared database, performance is a real issue.
However, what I'm looking for is a CLUSTERED index on the group_id, sort_order fields. In MS SQL Server, you can define an auto-increment field separate from the tables clustered index. In MySQL, it appears that they are somewhat related because they both needs to be based on the KEY, but this is not very well documented.
A clustered index is different than a regular index in that the records in the table are *physically ordered on the hard drive* in the order of the clustered index. So, for example, if I want to retrieve all the records with group_id = 22 (for example), the read heads on the hard drive read their data sequentially because all the records in group_id=22 are right next to each other on the hard drive. That is, the read heads don't have to jump around the hard drive collecting this data. Instead, it reads it in one continuous chunk. This is much faster than reading it from many positions on the hard drive.
At the same time, I still want a unique auto-incremented id fields. I realize I can probably fake the auto-increment, but this seems unclean and a little dangerous as it may be hard to tell what, if any, side effects there are until it is put under load. For example, I don't want to grab a MAX(id) and add 1 to it because the time between grabbing the max(id) and then inserting a new record, another process might have grabbed the same max(id) and then there would be two identical IDs. Then I could put it into a transaction block but this affects performance again.
Since we are potentially hostings hundreds of thousands of sites off a shared database, performance is a real issue.
Something strange here. "physically ordered on the harddisk" would imply that the entire table was rewritten to a new set of files on each insert, you need a pretty hefty select/insert ratio before that improves performance.
I would look to other issues, like "Is it really nessesarry for those 100.000 sites to share the same database?", "Could ve decrease load by generating a set of derived tables say every 24 hours?", etc.
I would look to other issues, like "Is it really nessesarry for those 100.000 sites to share the same database?", "Could ve decrease load by generating a set of derived tables say every 24 hours?", etc.
ASKER
INNODB tables ALWAYS have a clustered index so the overhead of ordering them is moot. The table is always physically ordered, in the case of MySQL, on the "key" of the table. The key denotes the clustered index. If there is no key, MySQL makes up a hidden one so there is no way around physically ordered tables even if you wanted to.
Usually the key is an auto-increment field in mysql but doesn't have to be so the table is typically physicalled ordered by the "id" field of a table (since the "id" field is usually the auto-increment field).
You don't have to rewrite the entire table. Any database with a clustered index is smart enough to leave spaces in the database so that you can insert new records in between old records. Sometimes you run out of space and data has to be shifted but you don't rewrite the entire table.
My question is that I need a way to have a clustered multi-field index AND an auto-increment field at the same time.
MS SQL Server separates the clustered index and the identity fields completely. MySQL has them sort of tied together in not altogether clear ways. In fact, they appear to behave differently with MyISAM and InnoDB tables as well.
Anybody out there with an answer?
Usually the key is an auto-increment field in mysql but doesn't have to be so the table is typically physicalled ordered by the "id" field of a table (since the "id" field is usually the auto-increment field).
You don't have to rewrite the entire table. Any database with a clustered index is smart enough to leave spaces in the database so that you can insert new records in between old records. Sometimes you run out of space and data has to be shifted but you don't rewrite the entire table.
My question is that I need a way to have a clustered multi-field index AND an auto-increment field at the same time.
MS SQL Server separates the clustered index and the identity fields completely. MySQL has them sort of tied together in not altogether clear ways. In fact, they appear to behave differently with MyISAM and InnoDB tables as well.
Anybody out there with an answer?
Ok can you post a CREATE TABLE here so I can get a better picture of what you want?
ASKER
Hi all,
I ended up figuring this out myself. I was having the create table query automatically written by Navicat and MySQL Administrator (tried it in both). I think the automatically generated code ordered the "keys" in a different order than what was showing in the table designer.
The final solution was to specify the PRIMARY KEYs (three of them) manually with the "id" field specified last. Then I could put the auto-increment just on the "id" field.
Thanks everyone who tried to help and thanks Squeebee for asking the question that forced me to write the create table statement manually. This led to me experimenting some more and finally figuring it out.
For anyone else out there following this thread, this is a good performance tuning tip and also a warning that both MySQL Administrator and NaviCat do not allow you to create a multi-column clustered index with an auto-increment field. You need to write the create table statement manually.
Sunny
I ended up figuring this out myself. I was having the create table query automatically written by Navicat and MySQL Administrator (tried it in both). I think the automatically generated code ordered the "keys" in a different order than what was showing in the table designer.
The final solution was to specify the PRIMARY KEYs (three of them) manually with the "id" field specified last. Then I could put the auto-increment just on the "id" field.
Thanks everyone who tried to help and thanks Squeebee for asking the question that forced me to write the create table statement manually. This led to me experimenting some more and finally figuring it out.
For anyone else out there following this thread, this is a good performance tuning tip and also a warning that both MySQL Administrator and NaviCat do not allow you to create a multi-column clustered index with an auto-increment field. You need to write the create table statement manually.
Sunny
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So long as the auto_increment column comes first in the list of Index Columns, MySQL Administrator works fine.
The Create Table statement looks like this (note the column order in the PRIMAY KEY clause):
CREATE TABLE `test` (
`a` INTEGER UNSIGNED NOT NULL,
`b` INTEGER UNSIGNED NOT NULL,
`c` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c`, `b`, `a`)
)
ENGINE = InnoDB;
The Create Table statement looks like this (note the column order in the PRIMAY KEY clause):
CREATE TABLE `test` (
`a` INTEGER UNSIGNED NOT NULL,
`b` INTEGER UNSIGNED NOT NULL,
`c` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`c`, `b`, `a`)
)
ENGINE = InnoDB;
Creat index testindex on table (group_id, sort_order, id);
But why do you want that column in your index, as for auto_increment columns there is already an unique index on it. So just define that additional index and the index will be used, as long as the optimizer thinks this will speed up things.
Maybe if you post your create-table statement we can give you further advice.