use Perl

• Yes it could be better... but a bug? (Score:1)

  by mpeters (5802) on 2008.02.28 9:07 (#61387) Homepage Journal

  Apparently, when using InnoDB (we have MySQL version 5.0.45), AUTO_INCREMENT keys lock the entire table

  This is only a problem when you are doing lots of inserts in one SQL statement. As they say later on, it's a performance reason (to avoid calling ::get_auto_increment() for each row) not something they think is logically required.

  From an application standpoint, auto-inc keys should be non-identifying

  Why? A unique number is a pretty good way to identify something and auto-increment keys should always be unique. It doesn't mean it's the best key. Somethings have pretty good natural keys, but even those are usually text and won't have the speed of an integer.

  frankly, it shouldn't matter if they're consecutive

  I agree that with-in a multi-insert statement, the various insert parts don't need to be consecutive. But if auto-increment keys weren't consecutive I'd be worried. "Increment" implies that the values are consecutive. I'd hate for $i++ to increment my value to some non-consecutive number :)

  Reply to This

  • Re: (Score:2)

    by Ovid (2709)

    Non-identifying: since you aren't sure what these values are, you shouldn't use them as consistently identifying a record. They get used internally to join tables and externally when identifying a particular result in a result set when you need to refer to it again, but not identifying the exact data. Delete a record (on an auto-inc table) and later re-insert it. You should get a different auto-inc primary key, but it's the same data, right? The auto-inc PK is non-identifying (perhaps we're just using
    • Re: (Score:1)

      by chromatic (983)

      I wonder if it's for write clustering. Each slave gets a step value by which to increment all auto-increment rows. I don't know if the clustering system guarantees an order of operation of writes though.