From: Sunny Bains Date: March 2 2010 10:35pm Subject: Re: unique_checks and InnoDB primary key List-Archive: http://lists.mysql.com/internals/37763 Message-Id: MIME-Version: 1.0 (Apple Message framework v1077) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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