Auto Increment in InnoDB

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=gcdmg-mysql-2@m.gmane.org

Re: Auto Increment in InnoDB

--001636c92f21ddc9740484cfb058
Content-Type: text/plain; charset=ISO-8859-1

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 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=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636c92f21ddc9740484cfb058--

Re: Auto Increment in InnoDB

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 > > 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=vegivamp@tuxera.be
>
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto Increment in InnoDB

--001485e7e61a97fe770484d0b2e3
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra wrote:

> 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?
>


You want the autoincrement to go up every time you change the values in your
primary key ?

Aside from it not being a recommended practice, to put it mildly, that you
update primary key values (possible referential inconsistency), I'm not
aware of this behaviour in MyISAM, either.

An autoincrement is assigned if, and only if you assign NULL or (zero) to an
autoincrement column during an insert.

If your application behaves differently, it is probably already done either
in the application, or possibly through the use of triggers.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001485e7e61a97fe770484d0b2e3--

Re: Auto Increment in InnoDB

MyISAM has this really cool feature where you can specify autoincrement
on a secondary column in a multiple column index. In such a case the
generated value for the autoincrement column is calculated as
MAX(autoincrement column) + 1 WHERE prefix='given-prefix'. For more
refer to
http://dev.mysql.com/doc/refman/5.0/en/example-auto-incremen t.html. This
is exactly what I want, however I have an InnoDB table so this will not
work (in an InnoDB table, you cannot specify autoincrement on a
secondary column). So what I wanted to know was if there is some easy
way to mimic that behavior. In my last mail that I sent, in order to
mimic that functionality on InnoDB, I had to write several statements
that possibly need to be a part of a transaction. Of course that also
meant that I cannot specify that column as an autoincrement but instead
specify it something as INT NOT NULL.

Thanks
Aveek

Johan De Meersman wrote:
> On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra wrote:
>
>
>> 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?
>>
>>
>
>
> You want the autoincrement to go up every time you change the values in your
> primary key ?
>
> Aside from it not being a recommended practice, to put it mildly, that you
> update primary key values (possible referential inconsistency), I'm not
> aware of this behaviour in MyISAM, either.
>
> An autoincrement is assigned if, and only if you assign NULL or (zero) to an
> autoincrement column during an insert.
>
> If your application behaves differently, it is probably already done either
> in the application, or possibly through the use of triggers.
>
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto Increment in InnoDB

--0016e644ded6268ddc0484d162d7
Content-Type: text/plain; charset=ISO-8859-1

Kudos for managing to drag up such an obscure piece of functionality :-) I
can see where it would be useful, though.

As to your question, though: given that that page indicates that it will
reuse deleted sequence numbers, I think your best bet would be select @id :=
count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should
be slightly faster than a max(), I think. That in a trigger on your table
should emulate the behaviour pretty closely.

Am I mistaken, or does your code try to start from sequence 0 ?

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016e644ded6268ddc0484d162d7--

Re: Auto Increment in InnoDB

How can count(*) in an InnoDB table be faster than MAX() considering
that the former needs to do a table scan and the latter can use an index
if correctly used? My code starts the sequence from 1.

Thanks
Aveek

Johan De Meersman wrote:
> Kudos for managing to drag up such an obscure piece of functionality
> :-) I can see where it would be useful, though.
>
> As to your question, though: given that that page indicates that it
> will reuse deleted sequence numbers, I think your best bet would be
> select @id := count(*)+1 from table where cluster='clusterA' AND
> file='fileA' ; - should be slightly faster than a max(), I think. That
> in a trigger on your table should emulate the behaviour pretty closely.
>
> Am I mistaken, or does your code try to start from sequence 0 ?
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto Increment in InnoDB

--001636ef0ceabd23690484d18d5a
Content-Type: text/plain; charset=ISO-8859-1

The count happens after the where on an index - it should just count the
appropriate index rows without looking at the values. Worth benchmarking on
your dataset, though.


On Thu, Apr 22, 2010 at 1:22 PM, Aveek Misra wrote:

> How can count(*) in an InnoDB table be faster than MAX() considering that
> the former needs to do a table scan and the latter can use an index if
> correctly used? My code starts the sequence from 1.
>
>
> Thanks
> Aveek
>
> Johan De Meersman wrote:
>
>> Kudos for managing to drag up such an obscure piece of functionality :-) I
>> can see where it would be useful, though.
>>
>> As to your question, though: given that that page indicates that it will
>> reuse deleted sequence numbers, I think your best bet would be select @id :=
>> count(*)+1 from table where cluster='clusterA' AND file='fileA' ; - should
>> be slightly faster than a max(), I think. That in a trigger on your table
>> should emulate the behaviour pretty closely.
>>
>> Am I mistaken, or does your code try to start from sequence 0 ?
>>
>> --
>> Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636ef0ceabd23690484d18d5a--

Re: Auto Increment in InnoDB

On Thu, 22 Apr 2010 13:12:16 +0200, Johan De Meersman

wrote:

> Kudos for managing to drag up such an obscure piece of functionality :-)

I

> can see where it would be useful, though.

>

> As to your question, though: given that that page indicates that it will

> reuse deleted sequence numbers, I think your best bet would be select

@id

> :=

> count(*)+1 from table where cluster='clusterA' AND file='fileA' ; -

should

> be slightly faster than a max(), I think. That in a trigger on your

table

> should emulate the behaviour pretty closely.



Wouldn't that strategy cause problems if one or more rows have been

deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row

2 has been deleted - new sequence number would be 4).



/ Carsten



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto Increment in InnoDB

--000e0cd489b8d666da0484d1bd30
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen wrote:

> Wouldn't that strategy cause problems if one or more rows have been
> deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row
> 2 has been deleted - new sequence number would be 4).
>

Yeps. I'm none too sharp today, apparently. Max() it is.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000e0cd489b8d666da0484d1bd30--

Re: Auto Increment in InnoDB

At 12:03 AM 4/22/2010, Aveek Misra 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 |
>--------------------------------------------------------

Aveek,
You should be able to add an autoincrement field to a compound key if
the autoinc field is the first field of the compound key. This probably
doesn't help you though.

Mike




>Thanks
>Aveek
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto Increment in InnoDB

Johan De Meersman wrote:
> On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen wrote:
>
>
>> Wouldn't that strategy cause problems if one or more rows have been
>> deleted in the meantime? (i.e. sequence numbers 1-4 have been created, row
>> 2 has been deleted - new sequence number would be 4).
>>
>>
>
> Yeps. I'm none too sharp today, apparently. Max() it is.
>
>

That may not be an issue in this case. Since it sounds like he is
keeping a revision history, I wouldn't be surprised if he plans on not
allowing the deleting of records, unless of course all of the revision
history for a given file Cluster/File are deleted. If that is the case
the count would work fine. If that is not the case, max may not work
either since if the last revision record has been deleted then using max
will give faulty data as well. Seems the only way for something like
this to work is if you keep the full revision history. Although I
suppose that if you were to keep say the most recent X revisions then
the last revision would always be in the table and max could work where
count would not always.

Chris W

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Auto Increment in InnoDB

--001636ed793f205c7d0484e34c47
Content-Type: text/plain; charset=ISO-8859-1

It might also be done by keeping a last-revision table. Then you'd only
select 1 record from that, and up the number.


On Thu, Apr 22, 2010 at 5:34 PM, Chris W <4rfvgy7@cox.net> wrote:

> Johan De Meersman wrote:
>
>> On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen >> >wrote:
>>
>>
>>
>>> Wouldn't that strategy cause problems if one or more rows have been
>>> deleted in the meantime? (i.e. sequence numbers 1-4 have been created,
>>> row
>>> 2 has been deleted - new sequence number would be 4).
>>>
>>>
>>>
>>
>> Yeps. I'm none too sharp today, apparently. Max() it is.
>>
>>
>>
>
> That may not be an issue in this case. Since it sounds like he is keeping
> a revision history, I wouldn't be surprised if he plans on not allowing the
> deleting of records, unless of course all of the revision history for a
> given file Cluster/File are deleted. If that is the case the count would
> work fine. If that is not the case, max may not work either since if the
> last revision record has been deleted then using max will give faulty data
> as well. Seems the only way for something like this to work is if you keep
> the full revision history. Although I suppose that if you were to keep say
> the most recent X revisions then the last revision would always be in the
> table and max could work where count would not always.
>
> Chris W
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--001636ed793f205c7d0484e34c47--