List:General Discussion« Previous MessageNext Message »
From:Aveek Misra Date:April 22 2010 10:09am
Subject:Re: Auto Increment in InnoDB
View as plain text  
I am not sure I understand. If I make the autoincrement column as part 
of the primary key as (rev + cluster + file), how do I ensure that a 
reset of the revision number is done as soon as (cluster + file) 
combination changes? It looks like I need to do the following to mimic 
the same behavior as that of an autoincrement column in MyISAM

SELECT @id := IFNULL(MAX(rev), 0) FROM table WHERE cluster='clusterA' 
AND file='fileA' ;
SET @id := @id + 1;
INSERT INTO table (cluster, file, rev) VALUES ('clusterA', 'fileA', @id);

Additionally I guess the above needs to be encapsulated in a transaction 
to ensure atomic updates to the 'rev' number for a given cluster and 
file combination. Any thoughts?

Thanks
Aveek

Johan De Meersman wrote:
> You can't, iirc - if you add an autoincrement to InnoDB it MUST be the 
> primary key.
>
> You *can*, however, add that, set it as PK and stick a unique index on 
> (cluster, file) instead. Behaviour will be identical, but be aware 
> that there will be some performance implications - you will now have 
> to do an extra primary key lookup every time you select based on the 
> (cluster,file) key.
>
>
> On Thu, Apr 22, 2010 at 7:03 AM, Aveek Misra <aveekm@stripped 
> <mailto:aveekm@stripped>> wrote:
>
>     I have a InnoDB table which contains columns named 'cluster' and
>     'file' ('cluster' + 'file' is a primary key). I want to add a new
>     column that tracks the revision number of a file for a given
>     cluster and a file. The situation is tailor made for a MyIsam
>     table where I can add a new Auto Increment column as a secondary
>     column in a multiple column index. How can I get the same behavior
>     in an InnoDB table? Given below is a view of how the records will
>     look like
>
>     --------------------------------------------------------
>     | Cluster             |    File                |   Rev       |
>     --------------------------------------------------------
>     | clusterA           |   fileA               |   1            |
>     --------------------------------------------------------
>     | clusterA           |   fileA               |   2            |
>     ---------------------------------------------------------
>     | clusterB           |   fileA               |   1            |
>     --------------------------------------------------------
>     | clusterB           |   fileB               |   1            |
>     --------------------------------------------------------
>
>
>
>     Thanks
>     Aveek
>
>     -- 
>     MySQL General Mailing List
>     For list archives: http://lists.mysql.com/mysql
>     To unsubscribe:  
>      http://lists.mysql.com/mysql?unsub=1
>
>
>
>
> -- 
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel

Thread
Auto Increment in InnoDBAveek Misra22 Apr
  • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
    • Re: Auto Increment in InnoDBAveek Misra22 Apr
      • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
        • Re: Auto Increment in InnoDBAveek Misra22 Apr
          • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
            • Re: Auto Increment in InnoDBAveek Misra22 Apr
              • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
            • Re: Auto Increment in InnoDBCarsten Pedersen22 Apr
              • Re: Auto Increment in InnoDBJohan De Meersman22 Apr
                • Re: Auto Increment in InnoDBChris W22 Apr
                  • Re: Auto Increment in InnoDBJohan De Meersman23 Apr
  • Re: Auto Increment in InnoDBmos22 Apr