Is there a general rule for where in the order of a primary-key index to place the partition key?

Question

Assume that I properly query the partition key in every query. Is there any sensible reason to place the partition key anywhere but first in line?

I feel like there's something I'm not understanding about how the index works. Assume MySQL and InnoDB.

I think I get that, ordinarily, you place the most selective keys first and the less selective ones later. And the partition key would ordinarily be one of the less selective ones. But if the partition key is included in every query, what difference does it make to include the partition key first? Wouldn't this help in other ways, too? E.g., I won't have to include the partition key in every index if it's up front in the primary-key index: queries using other indexes can borrow the primary key from the primary-key index consistent with the leftmost-key constraint.

And I don't know if an index itself is ever partitioned but it seems like it could be if it's a covering index. (Am I right?) If so, the partition key would have to be first, no, for the partitions to work?

E.g.:

CREATE TABLE  `fee` (
    `fi` INT ,
    `fo` INT ,
    PRIMARY KEY ( `fi` , `fo` ) ,
) ENGINE = INNODB
PARTITION BY RANGE ( `fi` ) (
   . . .
);

Or . . .

CREATE TABLE  `fee` (
    `fi` INT ,
    `fo` INT ,
    PRIMARY KEY ( `fo` , `fi` ) ,
) ENGINE = INNODB
PARTITION BY RANGE ( `fi` ) (
   . . .
);

Which, if either, is inherently better, and why or why not?

Thank you for your time.

Answer 1

"you place the most selective keys first and the less selective ones later" -- No. That is an old wives tale.

Put keys that are tested with '=' first is a simple and more important rule.

Think of a composite InnoDB BTree index as working this way. Concatenate all the columns together, then picture the BTree as having a single string as the key.

Putting the "partition key" first in an index is the least useful place! You are already pruning on that; having it in the index is actually redundant. However, it is necessary for any Unique key (that includes the `PRIMARY KEY').

Yes, you correctly observed that the PK columns are implicitly included in every secondary key, hence the partition key is included.

Note that if the partition key is not really part of a desired UNIQUE key, then the uniqueness constraint is not possible (in MySQL). However, the tacked-on PK is not part of the uniqueness constraint. Since MySQL is only willing to check uniqueness for one partition, you must include the partition key to also provide the semantics that states "Unique" across the entire table. (Yeah, it is a bit convoluted; live with it.)

In your example, if you do SELECT .. WHERE fi BETWEEN 1 and 2 AND fo=3, any index (the PK is an index) starting with fi would work harder than if fo were first in the index.

So, a Rule of Thumb is to move the partition key to the end of any index that includes it. (I have seen only one rare exception; I forget the details.)