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 4rfv...@cox.net wrote:
Johan De Meersman wrote:
On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dk
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
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
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
On Thu, Apr 22, 2010 at 12:09 PM, Aveek Misra ave...@yahoo-inc.com 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
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
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
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
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 ave...@yahoo-inc.com wrote:
How can count(*) in an InnoDB table be faster than
On Thu, 22 Apr 2010 13:12:16 +0200, Johan De Meersman vegiv...@tuxera.be
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
On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dkwrote:
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
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
Johan De Meersman wrote:
On Thu, Apr 22, 2010 at 1:34 PM, Carsten Pedersen cars...@bitbybit.dkwrote:
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
13 matches
Mail list logo
