List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 4 1999 5:07pm
Subject:Re: auto_increment, UNIQUE, and performance
View as plain text  
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
Thread
auto_increment, UNIQUE, and performanceAndy4 Dec
  • Re: auto_increment, UNIQUE, and performancePaul DuBois4 Dec
    • Re: auto_increment, UNIQUE, and performanceMichael Widenius26 Jan
  • Re: auto_increment, UNIQUE, and performancesinisa5 Dec
  • Re: auto_increment, UNIQUE, and performanceSasha Pachev5 Dec
  • Re: auto_increment, UNIQUE, and performanceSasha Pachev5 Dec
    • Re: auto_increment, UNIQUE, and performancesinisa5 Dec
  • Re: auto_increment, UNIQUE, and performanceRobert Seward5 Dec
    • Re: auto_increment, UNIQUE, and performancesinisa6 Dec
  • Re: auto_increment, UNIQUE, and performanceRick Moore5 Dec
    • Re: auto_increment, UNIQUE, and performancesinisa6 Dec
  • Re: auto_increment, UNIQUE, and performanceSasha Pachev6 Dec
    • Re: auto_increment, UNIQUE, and performancesinisa6 Dec
  • Re: auto_increment, UNIQUE, and performanceSasha Pachev6 Dec