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
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
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.
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