List:Internals« Previous MessageNext Message »
From:Rick James Date:March 4 2010 6:57pm
Subject:Re: unique_checks and InnoDB primary key
View as plain text  
Why should unique checks ever be turned off?  Wouldn't that violate the 
UNIQUE constraint?  Or at least delay it until too late to do a proper 
ROLLBACK?

On 3/2/10 3:07 PM, Sunny Bains 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
>    

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