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<mdyk...@gmail.com> 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<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
