List:Internals« Previous MessageNext Message »
From:Sunny Bains Date:March 2 2010 10:35pm
Subject:Re: unique_checks and InnoDB primary key
View as plain text  
Zardosht,

On 03/03/2010, at 1:54 AM, Zardosht Kasheff wrote:

> Hello,
> 
> I have a question about the variable unique_checks and InnoDB. I see
> that if unique_checks is 0, the storage engine is allowed to ignore
> duplicates of secondary unique keys. InnoDB uses this setting to allow
> the insert buffer to increase insertion speed.
> 
> My question is this: if unique_checks is 0, is InnoDB allowed to
> ignore duplicates of the primary key? Looking at code, the answer
> seems to be no. And as a result, the insert buffer is not used for the
> primary key, correct?
> 
> Assuming my understanding above is correct, here is a follow up
> question. Suppose I have a table schema:
> create table foo (a int, b int, primary key (a,b))engine=InnoDB;
> Because the insert buffer cannot be used for the primary key, can I
> get better performance by changing the schema to the following?
> create table foo (a int, b int, unique key (a,b))engine=InnoDB;
> 
> Or will this not work because InnoDB selects the unique key as the
> clustered key?
> 
> Thanks
> -Zardosht

Unique secondary keys cannot use the insert buffer.  Also, he insert buffer
is not used for primary keys at all.  The insert buffer is only used for
non-unique secondary indexes.

Regards,
-sunny
Thread
unique_checks and InnoDB primary keyZardosht Kasheff2 Mar
  • Re: unique_checks and InnoDB primary keySunny Bains2 Mar
    • Re: unique_checks and InnoDB primary keyZardosht Kasheff2 Mar
      • Re: unique_checks and InnoDB primary keySunny Bains3 Mar
        • Re: unique_checks and InnoDB primary keyZardosht Kasheff3 Mar
          • Re: unique_checks and InnoDB primary keySunny Bains3 Mar
            • Re: unique_checks and InnoDB primary keyZardosht Kasheff3 Mar
        • Re: unique_checks and InnoDB primary keyRick James4 Mar
          • Re: unique_checks and InnoDB primary keyVenu Kalyan4 Mar
            • Re: unique_checks and InnoDB primary keyRick James26 Jul