List:General Discussion« Previous MessageNext Message »
From:Kyong Kim Date:July 31 2009 11:07pm
Subject:Re: Does InnoDB ever not cluster data by primary key?
View as plain text  
Michael,
We're counting on batch inserts of constant 2 leftmost columns of the
primary key.
We would be selecting within constant values for the leftmost columns as well.

For example, our primary key is

country_id, city_id, auto_inc, ...

We'll always be looking for data from within the same country and city.
Kyong


On Thu, Jul 30, 2009 at 9:44 PM, Michael Dykman<mdykman@stripped> wrote:
> 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