List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:July 31 2009 4:44am
Subject:Re: Does InnoDB ever not cluster data by primary key?
View as plain text  
With your auto-increment in the right-most position, it seems to me
that it would tend to make your inserts non-sequential (assuming the
fields to the left are not sequential) causing inserts to occur all
over the tree.  With the auto-increment as the first field in the key,
the inserts would be going to the same place in the tree allowing it
to build out nicely.

I have definitely found that sequential inserts perform much better
than random ones.

 - md

On Fri, Jul 31, 2009 at 12:25 AM, Kyong Kim<kykimdba@stripped> wrote:
> Michael,
> Yeah. We're trying to maximize the benefits of clustering and had to
> sacrifice on the length of the primary key.
> And we got fairly good results from query profiling using maatkit.
> One thing that shocked me was the overhead of random inserts primary
> key updates.
> It's definitely a tradeoff.
> We're reasonably certain that we'll see a lot of ordered bulk inserts.
>
> It ran counter to the results that we were seeing so I had to verify
> that InnoDB always clusters by primary key regardless of the position
> of the auto increment column in the primary key.
> Kyong
>
> On Thu, Jul 30, 2009 at 7:08 PM, Michael Dykman<mdykman@stripped> wrote:
>> InnoDb storage format is (always) a b-tree based on the primary key,
>> so the simple answer is: no, InnoDB never clusters by anything other
>> than a primary key.  The size of that key can have significant impact
>> on performance though, so be careful with the multi-icolumn primary
>> key.  Assuming your primary key remains constant over the lifetime of
>> the record. I don't think it matters much where you put the
>> auto-increment key.
>>
>>  - michael
>>
>>
>> On Thu, Jul 30, 2009 at 10:00 PM, Kyong Kim<kykimdba@stripped> wrote:
>>> We have a multi-column primary key with an auto-increment column as
>>> the 3rd column in the primary key in InnoDB.
>>> Is there a requirement to have the auto-increment column as the
>>> leftmost column in the primary key in order for InnoDB to cluster by
>>> the multi-column primary key?
>>> I don't believe this to be the case but there has been some discussion
>>> on this topic.
>>> I haven't been able to find any definitive answers.
>>> Judging by the query profiling results and explain output,  we are
>>> seeing the benefits of clustering by primary key.
>>> If you have any insight on this matter, it would be much appreciated.
>>> Kyong
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>
>>>
>>
>>
>>
>> --
>>  - michael dykman
>>  - mdykman@stripped
>>
>> Don’t worry about people stealing your ideas. If they’re any good,
>> you’ll have to ram them down their throats!
>>
>>   Howard Aiken
>>
>



-- 
 - michael dykman
 - mdykman@stripped

Don’t worry about people stealing your ideas. If they’re any good,
you’ll have to ram them down their throats!

   Howard Aiken
Thread
Does InnoDB ever not cluster data by primary key?Kyong Kim31 Jul
Re: Does InnoDB ever not cluster data by primary key?Kyong Kim31 Jul
  • Re: Does InnoDB ever not cluster data by primary key?Michael Dykman31 Jul
    • Re: Does InnoDB ever not cluster data by primary key?Kyong Kim1 Aug