Re: Adaptation of Auto Increment to a Compound Key

From: Peter Gulutzan <pgulutzan_at_ocelot.ca>
Date: 26 Nov 2002 08:02:50 -0800
Message-ID: <36c478c6.0211260802.6525169e@posting.google.com>

Tim Cross <tcross_at_pobox.une.edu.au> wrote in message news:<87lm3iegkv.fsf_at_blind-bat.une.edu.au>...
>
> Something else to note is that a sequence in oracle is NOT guaranteed
> to have no gaps - from vague memories, I think auto increment in MySQL
> gives you consecutive numbering with no gaps (unless you delete
> records).
>

Paul DuBois (author of a popular thick MySQL book) wrote an article on informit.com titled "Using Auto_Increment to Create Sequences [in MySQL]".
Registration is required but it's free. Interpreting a little from that, it does seem clear that MySQL 3.23 and below found the current high number from an index. However, MySQL/InnoDB allows for transaction logging, so it should be able to imitate Oracle's sequence methods internally. (Without transaction logging it's difficult to support sequences without locking a database object.)

I have an online article comparing auto-numbering internals in other DBMSs (DB2 and Oracle and SQL Server) in the current issue of dbazine.com: "Sequences and Identity Columns" http://dbazine.com/gulutzan4.html.

Peter Gulutzan
Co-Author of SQL Performance Tuning (http://www.ocelot.ca/tuning.htm) Received on Tue Nov 26 2002 - 10:02:50 CST