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<kykim...@gmail.com> 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<mdyk...@gmail.com> 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<kykim...@gmail.com> 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=mdyk...@gmail.com >>> >>> >> >> >> >> -- >> - michael dykman >> - mdyk...@gmail.com >> >> 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 - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
