From: Zardosht Kasheff
Date: March 2 2010 11:43pm
Subject: Re: unique_checks and InnoDB primary key
List-Archive: http://lists.mysql.com/internals/37768
Message-Id: <2f9663ba1003021543x507979b7h77b340c29ff81e@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Thanks Sunny.

This answers my questions.

-Zardosht

On Tue, Mar 2, 2010 at 6:19 PM, Sunny Bains <Sunny.Bains@stripped> wrote:
> 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 eve=
n
> when unique_checks is set to 0.
>
>> Thanks
>> -Zardosht
>>
>> On Tue, Mar 2, 2010 at 6:07 PM, Sunny Bains <Sunny.Bains@stripped> wro=
te:
>>> 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 =A0unique_ch=
ecks =3D=3D 0
>>> for the transaction then would InnoDB still use the insert buffer for u=
nique secondary
>>> indexes. I answered the case for the default setting. If you want to ig=
nore the unique
>>> check then yes it will use the insert buffer. The whole point of the in=
sert 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 ins=
ert 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> w=
rote:
>>>>> 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 all=
ow
>>>>>> 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 t=
he
>>>>>> 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=3DInnoDB;
>>>>>> 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=3DInnoDB;
>>>>>>
>>>>>> 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. =A0Also, he inser=
t buffer
>>>>> is not used for primary keys at all. =A0The insert buffer is only use=
d for
>>>>> non-unique secondary indexes.
>>>>>
>>>>> Regards,
>>>>> -sunny
>>>>>
>>>>
>>>
>>> Regards,
>>> -sunny
>
> Regards,
> -sunny