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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
