List:General Discussion« Previous MessageNext Message »
From:Kyong Kim Date:July 31 2009 4:25am
Subject:Re: Does InnoDB ever not cluster data by primary key?
View as plain text  
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
>
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