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?
On Tue, Mar 2, 2010 at 6:07 PM, Sunny Bains <Sunny.Bains@stripped> wrote:
> 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
> indexes. I answered the case for the default setting. If you want to ignore the
> check then yes it will use the insert buffer. The whole point of the insert buffer
> to reduce IO and be able to translate from random IO to sequential IO. In the
> setting we would have to bring the page into the buffer pool to check (if it's not
> 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>
>>> On 03/03/2010, at 1:54 AM, Zardosht Kasheff wrote:
>>>> 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?
>>> Unique secondary keys cannot use the insert buffer. Also, he insert
>>> is not used for primary keys at all. The insert buffer is only used
>>> non-unique secondary indexes.