•  

Announcement

Announcement Module
Collapse
No announcement yet.

More on autoincrement vs guid vs natural key

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • More on autoincrement vs guid vs natural key

    #1
    I've read several articles about autoincrements and I'm still in doubt. I understand that long GUIDs results in large index files and possibly slow access. Also I tested 32-byte GUIDs versus autoincrenent keys and found a very little difference. Also I've been told by our admins that autoincrement columns are bad in scaling. They asked us, programmers, to change application logic in orde to use natural keys or UUIDs. Is it possible to make a little summary on this subject ? Advantages and disadvantages of autoincrements, uuids and natural keys could depend on:

    * table size
    * key size
    * whether the key is composite or not
    * number of connections
    * what else ?

    Currently I have two cases. A table that requires many inserts from many connections. This table has several millions of rows and acts as a kind of a log. Another table holds session information. There are several thousand rows, but there is a very intensive process of selection-insertion-deletion of rows.
    Currently the first table uses autoincrement and the second one uses GUID. Is this correct ?
    Tags: None

  • #2
    Hi,

    The auto_increment is not a special type, it is a "default" value for Primary Key Int-Type columns for Inserting new values on.

    If you do use a autoincrement id, or a UUID depends on your application structure.

    In general, I would advice to use a autoincrement, specially if you use InnoDB tables.
    InnoDB rows are stored in order of the PK on disk. Using a UUID, which is random like, results in storing the rows in random order. New rows, will have to be inserted between existing rows, resulting in moving existing rows out of the way, to make room for the new row...
    Autoincremented rows will be stored at the end of the table..

    The data is stored in a B-Tree structure. Inserting data in random order will result in many nodes, which are only partly filled. Using up more space on disk and in memory.
    Inserting in order (autoincrement), will result in very dense nodes...


    In your case: For the Log Table, where no rows will be deleted, the autoincrement is a good choice.
    For your session table, where many inserts and deletes occur, the table will be "fragmented" anyway. (MyISAM Fragmentation, InnoDB partly filled nodes). If you do access the table, using your GUID, there is no need for adding another "autoincrement" PK column. Using an autoincrement would not result in a more dense data structure (because of the random deletes) and it may make the ID predictable, which might be a Security-Concern...

    Comment


    • #3
      AutoIncrement is bad in scaling because it makes distributed insertion complex. If you have several servers, how do you coordinate the autoincrement? That's a major disadvantage.

      Comment


      • #4
        i have field on table. it is for example it has 1 to 20 digits as 20 records. when i delete 15 to 20 records from end of it then insert new record to it with: INSERT INTO dynamic VALUES(Ɔ')

        , it give "21" digit instead of "16".

        how to reset the auto increment field ? without using truncate option ,as truncate can not rollback .

        Comment

        Working...
        X