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

Posted on 2004-11-28
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

    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.
    LVL 1

    Author Comment

    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

    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.
    LVL 1

    Author Comment

    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

    Ok can you post a CREATE TABLE here so I can get a better picture of what you want?
    LVL 1

    Author Comment

    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

    Glad I could lead you down the right path.

    Expert Comment

    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` (
      PRIMARY KEY (`c`, `b`, `a`)
    ENGINE = InnoDB;

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now