List:Internals« Previous MessageNext Message »
From:Sunny Bains Date:March 2 2010 11:07pm
Subject:Re: unique_checks and InnoDB primary key
View as plain text  
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