List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:March 2 2010 10:47pm
Subject:Re: unique_checks and InnoDB primary key
View as plain text  
Then what does one gain by setting unique_checks to 0 and using InnoDB?

On Tue, Mar 2, 2010 at 5:35 PM, Sunny Bains <Sunny.Bains@stripped> wrote:
> 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