From: Rick James Date: March 4 2010 6:57pm Subject: Re: unique_checks and InnoDB primary key List-Archive: http://lists.mysql.com/internals/37779 Message-Id: <4B90028E.2050608@yahoo-inc.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 >