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.
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 good,
>> you’ll have to ram them down their throats!
>> Howard Aiken
- michael dykman
Don’t worry about people stealing your ideas. If they’re any good,
you’ll have to ram them down their throats!