How do I add an auto-increment field to a multi-column key

Posted on 2004-11-28
Medium Priority
Last Modified: 2008-11-24
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.

Question by:sunnyhirai
LVL 48

Expert Comment

ID: 12691503
Just define an additional index
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.

Author Comment

ID: 12691643
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.
LVL 15

Expert Comment

ID: 12692267
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.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

ID: 12692413
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?
LVL 17

Expert Comment

ID: 12693181
Ok can you post a CREATE TABLE here so I can get a better picture of what you want?

Author Comment

ID: 12694017
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.

LVL 17

Accepted Solution

Squeebee earned 1000 total points
ID: 12696644
Glad I could lead you down the right path.

Expert Comment

ID: 23027426
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):

  PRIMARY KEY (`c`, `b`, `a`)

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question