Re: auto_increment, UNIQUE, and performance

[Paul DuBois]

At 11:22 AM -0500 12/4/99, Andy wrote:
>Hi!
>
>I read in th MySQl manual section 10.7 "Other Optimization Tips - Unsorted
>tips for faster systems: " the following:
>
>"Use AUTO_INCREMENT columns to make unique values."
>
>How does that make things faster? Could someone explain please?


Unique indexes are fastest when you're performing queries that involve
the indexed column.

>
>Also I have a few more questions:
>
>1) How does mysql implement auto_increment? Is the last_inserted_value
>cached in memory? Or does doing an auto_increment involve going to disk to
>read the last_inserted_value and thus slowing things down by requiring an
>extra disk read?


Depends.  If you use the mysql_insert_id() API function, the value is
maintained on the client side.  If you get the value by issuing a
SELECE LAST_INSERT_ID() query, then you're going to have a round trip
to the server.

>
>2) How does mysql check for duplications on UNIQUE keys when doing inserts?
>Is there a performance hit for doing the duplication check?

Dunno.

>
>3) Does mysql require that every table has at least one UNIQUE KEY? Can I
>have a table with no UNIQUE KEY and no PRIMARY KEY at all?

You're not required to have any indexes at all.  You can have only one
PRIMARY KEY, but you can have multiple UNIQUE indexes.  (The reason you
can have only one PRIMARY KEY is that such an index is named PRIMARY, and
you can't have more than one index with a given name.)

-- 
Paul DuBois, paul@stripped