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

On 03/03/2010, at 10:13 AM, Zardosht Kasheff wrote:

> I see. I understand that the insert buffer is used for unique
> secondary keys when unique_checks is set to 0.
> 
> Now what about the primary key? Is the insert buffer used for the
> primary key when unique_checks is set to 0?
> 

Primary keys (a.k.a cluster indexes) never use the insert buffer, not even
when unique_checks is set to 0.

> Thanks
> -Zardosht
> 
> On Tue, Mar 2, 2010 at 6:07 PM, Sunny Bains <Sunny.Bains@stripped> wrote:
>> Zardosht,
>> 
>> On 03/03/2010, at 9:47 AM, Zardosht Kasheff wrote:
>> 
>>> Then what does one gain by setting unique_checks to 0 and using InnoDB?
>>> 
>> 
>> 
>> Sorry, I misunderstood the question. You wanted to know if  unique_checks == 0
>> for the transaction then would InnoDB still use the insert buffer for unique
> secondary
>> indexes. I answered the case for the default setting. If you want to ignore the
> unique
>> check then yes it will use the insert buffer. The whole point of the insert
> buffer is
>> to reduce IO and be able to translate from random IO to sequential IO. In the
> default
>> setting we would have to bring the page into the buffer pool to check (if it's
> not already
>> there) for dup keys defeating the purpose of the insert buffer. The insert buffer
> is
>> only used for pages that are not already in the buffer pool.
>> 
>> 
>>> 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
>>>> 
>>> 
>> 
>> Regards,
>> -sunny

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