We're counting on batch inserts of constant 2 leftmost columns of the
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.
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:
>> 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.
>> 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.
>>>> 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
>>> 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